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:

Databases:

Migration Reference

Language Elements

Converting SQL language elements:

DB2 PostgreSQL
1 string1 CONCAT string 2 CONCAT … String concatenation operator string1 || string 2 || …

Data Types

Converting data types:

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

Built-in SQL Functions

Converting string functions:

DB2 PostgreSQL
1 LOCATE(substring, string) Get position of substring in string POSITION(substring IN string)
2 VARCHAR(exp, length) Convert to string CAST(exp AS VARCHAR(length))
VARCHAR(exp) CAST(exp AS VARCHAR)

Converting date and time functions:

DB2 PostgreSQL
1 DATE(exp) Convert to date (year, month and day) DATE(exp) Not documented
2 DAY(exp) Extract day EXTRACT(DAY FROM exp)
3 MONTH(exp) Extract month EXTRACT(MONTH FROM exp)
4 TIMESTAMP(exp) Convert to timestamp (date and time) CAST(exp AS TIMESTAMP)
5 YEAR(exp) Extract year EXTRACT(YEAR FROM exp)

SELECT Statement

Converting SQL queries:

DB2 PostgreSQL
1 SELECT … MINUS SELECT … Exclude rows returned by 2nd query SELECT … EXCEPT SELECT …

CREATE TABLE Statement

Converting CREATE TABLE statement:

DB2 PostgreSQL
1 FOR COLUMN system_name System column name (OS/400) Removed
2 FOR BIT DATA Binary data encoding Removed
FOR SBCS | MIXED DATA Column data encoding (z/OS) Removed
3 CCSID ASCII | UNICODE | EBCDIC Character set Removed
CCSID num Column character set (OS/400) Removed

Implicit DEFAULT values in DB2:

DB2 PostgreSQL
1 column CHAR(n) WITH DEFAULT column CHAR(n) DEFAULT ''
2 column VARCHAR(n) WITH DEFAULT column VARCHAR(n) DEFAULT ''
3 column INTEGER WITH DEFAULT column INTEGER DEFAULT 0
4 column DECIMAL(p, s) WITH DEFAULT column DECIMAL(p, s) DEFAULT 0
5 column NUMERIC(p, s) WITH DEFAULT column NUMERIC(p, s) DEFAULT 0
6 column DATE WITH DEFAULT column DATE DEFAULT CURRENT_DATE
7 column TIMESTAMP WITH DEFAULT column TIMESTAMP DEFAULT NOW()
8 column CLOB WITH DEFAULT column TEXT DEFAULT ''

CREATE INDEX Statement

Converting CREATE INDEX statement keywords and clauses:

DB2 PostgreSQL
1 CREATE INDEX schema.index_name CREATE INDEX index_name Can contain index name only

CREATE PROCEDURE Statement

Converting CREATE PROCEDURE statement from IBM DB2 to PostgreSQL:

DB2 PostgreSQL
1 CREATE OR REPLACE PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 (IN | OUT | INOUT param datatype(length), …) (IN | OUT | INOUT param datatype(length), …)
3 LANGUAGE SQL Removed
4 BEGIN
procedure_body
END 		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
5 DECLARE GLOBAL TEMPORARY TABLE Create a temporary table CREATE TEMPORARY TABLE

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, …
5 VALUES c1, c2, … INTO v1, v2, … v1 := c1; v2 := c2; …

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 OUT cur REFCURSOR
3 DECLARE cur WITH RETURN FOR stmt
PREPARE stmt FROM 'query_string' 		Dynamic cursors OPEN cur FOR EXECUTE 'query_string'

Executing stored procedures:

DB2 PostgreSQL
1 CALL proc_name Execute a stored procedure CALL proc_name

SQL Statements

Converting SQL statements:

DB2 PostgreSQL
1 VALUES Return one or more rows SELECT ... UNION ALL SELECT ...