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 15.x, 14.x, 13.x, 12.x, 11.x, 10.x and 9.x

Migration Reference

Language Elements

Converting SQL language elements:

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

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

Built-in SQL Functions

Converting string functions:

	DB2		PostgreSQL
1	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
2	FOR SBCS \| MIXED DATA	Column data encoding (z/OS)	Removed
3	CCSID ASCII \| UNICODE \| EBCDIC	Character set	Removed
3	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	cur REFCURSOR
3	DECLARE cur WITH RETURN FOR stmt PREPARE stmt FROM 'query_string'	Dynamic cursors	OPEN cur FOR EXECUTE 'query_string'

SQL Statements

Converting SQL statements:

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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference