IBM DB2 to PostgreSQL Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from IBM DB2 to PostgreSQL:

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts conversion tool

Databases:

  • IBM DB2 for LUW, z/OS and OS/400 11.x, 10.x, 9.x, 8.x and 7.x
  • PostgreSQL 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

Data Types

Data type mapping between DB2 and PostgreSQL:

DB2 PostgreSQL
1 BIGINT 64-bit integer BIGINT
2 BLOB(n) Binary large object, 1 ⇐ n ⇐ 2G BYTEA
3 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 254 CHAR(n), CHARACTER(n)
4 CHAR(n) FOR BIT DATA Fixed-length byte string, 1 ⇐ n ⇐ 254 BYTEA
5 CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR(n)
6 CLOB(n) Character large object, 1 ⇐ n ⇐ 2G TEXT
7 DATE Date (year, month and day) DATE
8 DBCLOB(n) UTF-16 character large object, 1 ⇐ n ⇐ 1G TEXT
9 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DECFLOAT(16 | 34) IEEE floating-point number FLOAT
11 DOUBLE [PRECISION] Double-precision floating-point number DOUBLE PRECISION
12 FLOAT(p) Double-precision floating-point number DOUBLE PRECISION
13 GRAPHIC(n) Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127 CHAR(n)
14 INTEGER, INT 32-bit integer INTEGER, INT
15 NCHAR(n) Fixed-length UTF-16 string, 1 ⇐ n ⇐ 127 CHAR(n)
16 NCHAR VARYING(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 VARCHAR(n)
17 NCLOB(n) UTF-16 character large object, 1 ⇐ n ⇐ 1G TEXT
18 NUMERIC(p,s), NUM(p,s) Fixed-point number NUMERIC(p,s)
19 NVARCHAR(n) Varying-length UTF-16 string, 1 ⇐ n ⇐ 16336 VARCHAR(n)
20 REAL Single-precision floating-point number REAL
21 SMALLINT 16-bit integer SMALLINT
22 TIME Time (hour, minute, and second) TIME(0)
23 TIMESTAMP(p) Date and time with fraction TIMESTAMP(p)
24 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 32672 VARCHAR(n)
25 VARCHAR(n) FOR BIT DATA Variable-length byte string, 1 ⇐ n ⇐ 32672 BYTEA
26 VARGRAPHIC(n) Variable-length UTF-16 string, 1 ⇐ n ⇐ 16336 VARCHAR(n)
27 XML XML data XML

CREATE PROCEDURE Statement

Converting CREATE PROCEDURE statement from IBM DB2 to PostgreSQL:

DB2 PostgreSQL
1 CREATE OR REPLACE PROCEDURE name CREATE OR REPLACE FUNCTION name
2 (IN | OUT | INOUT param datatype(length), …) (IN | OUT | INOUT param datatype(length), …)
3 LANGUAGE SQL Removed
4 BEGIN
procedure_body
END
RETURNS VOID AS $$
BEGIN
procedure_body
END;
$$ LANGUAGE plpgsql;

For more information, see Conversion of Procedural SQL Statements.

Procedural SQL Statements

Converting procedural SQL statements used in stored procedures, functions and triggers from IBM DB2 to PostgreSQL:

Variable declarations:

DB2 PostgreSQL
1 Declarations are inside BEGIN END block Declarations are before BEGIN END block
2 DECLARE var datatype DEFAULT value var datatype DEFAULT value
3 DECLARE var, var2, … datatype var datatype; var2 datatype; …
4 DECLARE cur CURSOR FOR query Cursor declaration cur CURSOR FOR query

Variable Assignments:

DB2 PostgreSQL
1 SET v1 = value v1 := value
2 SET v1 = value, v2 = value2, … v1 := value; v2 := value2; …
3 SET (v1, v2, …) = (value, value2, …) v1 := value; v2 := value2; …
4 SET (v1, v2, …) = (SELECT c1, c2, …) SELECT c1, c2, … INTO v1, v2, …

Cursor operations:

DB2 PostgreSQL
1 DECLARE cur CURSOR FOR query Cursor declaration cur CURSOR FOR query
2 DECLARE cur CURSOR WITH RETURN ... Return result set cur REFCURSOR
3 DECLARE cur WITH RETURN FOR stmt
PREPARE stmt FROM 'query_string'
Dynamic cursors OPEN cur FOR EXECUTE 'query_string'