Migrating from DB2 to PostgreSQL – What You Should Know

Whether migrating a database or an application from DB2 to PostgreSQL with only one type of database knowledge is not sufficient, there are few things to know about the differences between the two database systems.

PostgreSQL is world’s most widely used advanced open source database. PostgreSQL database has rich feature set and PostgreSQL community is very strong and they are continuously improving the existing features and add new features. As per the db-engine.com, PostgreSQL is the DBMS of the year 2017 and 2018.

As you know DB2 and PostgreSQL are RDBMS but there are some incompatibilities. In this blog, we can see some of these incompatibilities.

Why Migrate From DB2 to PostgreSQL

1. Flexible open source licencing and easy availability from public cloud providers like AWS, Google cloud, Microsoft Azure.
2. Benefit from open source add-ons to improve database performance.

You can see in the below image that PostgreSQL popularity is increasing over time as compared to DB2.

Interest Over Time

Migration Assessment

The first step of migration is to analyze the application and database object, find out the incompatibilities between both the databases and estimate the time and cost required for migration.

Data Type Mapping

Some of the data types of IBM DB2 does not match directly with PostgreSQL data types, so you need to change it to corresponding PostgreSQL data type.

Please check the below table.

IBM DB2		PostgreSQL
BIGINT	64-bit integer	BIGINT
BLOB(n)	Binary large object	BYTEA
CLOB(n)	Character large object	TEXT
DBCLOB(n)	UTF-16 character large object	TEXT
NCLOB(n)	UTF-16 character large object	TEXT
CHAR(n), CHARACTER(n)	Fixed-length string	CHAR(n)
CHARACTER VARYING(n)	Variable-length string	VARCHAR(n)
NCHAR(n)	Fixed-length UTF-16 string	CHAR(n)
NCHAR VARYING(n)	Variable-length UTF-16 string	VARCHAR(n)
VARCHAR(n)	Variable-length string	VARCHAR(n)
VARGRAPHIC(n)	Variable-length UTF-16 string	VARCHAR(n)
VARCHAR(n) FOR BIT DATA	Variable-length byte string	BYTEA
NVARCHAR(n)	Varying-length UTF-16 string	VARCHAR(n)
GRAPHIC(n)	Fixed length UTF-16 string	CHAR(n)
INTEGER	32-bit integer	INTEGER
NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
DOUBLE PRECISION	Double precision floating point number	DOUBLE PRECISION
FLOAT(p)	Double precision floating point number	DOUBLE PRECISION
REAL	Single precision floating point number	REAL
SMALLINT	16-bit integer	SMALLINT
DATE	Date(year, month and day)	DATE
TIME	TIME (hour, minute, and second)	TIME(0)
TIMESTAMP(p)	Date and time with fraction	TIMESTAMP(p)
DECFLOAT(16 | 34)	IEEE Floating point number	FLOAT

Incompatibilities in DB2 and PostgreSQL

There are many incompatibilities present in DB2 and PostgreSQL, you can see some of them here. You can automate them by creating extensions so that you can use the DB2 function as it is in PostgreSQL and you can save your time. Please check the behaviour of DB2 function in PostgreSQL

TABLESPACE

TABLESPACE clause defines the name of the tablespace in which the newly created table resides.

DB2 uses IN clause for TABLESPACE so it should be replaced by TABLESPACE clause in PostgreSQL.

Example:

DB2:

IN 

PostgreSQL:

TABLESPACE 

FIRST FETCH n ROWS ONLY

In DB2, you can use FETCH FIRST n ROWS ONLY clause to retrieve no more than n rows. In PostgreSQL, you can use LIMIT n which is equivalent to FETCH FIRST n ROWS ONLY.

Example:

DB2:

SELECT * FROM EMP
 ORDER BY EMPID
 FETCH FIRST 10 ROWS ONLY;

PostgreSQL:

SELECT * FROM EMP
 ORDER BY EMPID
 LIMIT 10;

GENERATED BY DEFAULT AS IDENTITY

The IDENTITY column in DB2 can be replaced by Serial column in PostgreSQL.

DB2:

CREATE TABLE  (
 INTEGER NOT NULL
 GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) 
);

PostgreSQL:

CREATE TABLE  (
  SERIAL NOT NULL
);

Select from SYSIBM.SYSDUMMY1

There is no “SYSIBM.SYSDUMMY1” table in PostgreSQL. PostgreSQL allows a “SELECT” without ”FROM” clause. You can remove this by using script.

Scalar Functions: DB2 vs PostgreSQL

CEIL/CEILING

CEIL or CEILING returns the next smallest integer value that is greater than or equal to the input (e.g. CEIL(122.89) returns 123, also CEIL(122.19) returns 123).

DB2:

SELECT CEIL(123.89) FROM SYSIBM.SYSDUMMY1; 
SELECT CEILING(123.89) FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

SELECT CEIL(123.89) ; 
SELECT CEILING(123.89) ;

DATE

It converts the input to date values. You can convert DATE function to TO_DATE function in PostgreSQL.

DB2:

SELECT DATE ('2018-09-21') FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

SELECT TO_DATE ('21-09-2018',’DD-MM-YYYY’) ;

DAY

It returns the day (day of the month) part of a date or equivalent value. The output format is integer.

DB2:

SELECT DAY (DATE('2016-09-21')) FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

SELECT DATE_PART('day', '2016- 09-21'::date);

MONTH

It returns the month part of the date value. The output format is integer.

DB2:

SELECT MONTH (DATE('2016-09-21')) FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

SELECT DATE_PART ('month', '2016-09- 21'::date);

POSSTR

Returns the position of string. The POSSTR function is replaced by POSITION function in PostgreSQL.

DB2:

Usage : POSSTR(,)
SELECT POSSTR('PostgreSQL and DB2', 'and') FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

Usage: POSITION( IN)
SELECT POSITION('and' IN'PostgreSQL and DB2');

RAND

It returns a pseudorandom floating-point value in the range of zero to one inclusive. You can replace RAND function to RANDOM in PostgreSQL.

DB2:

SELECT RAND() FROM SYSIBM.SYSDUMMY1;

PostgreSQL:

SELECT RANDOM();

Tools

You can use some tools to migrate DB2 database to PostgreSQL. Please test the tool before use it.

1. Db2topg

   It is an automated tool for DB2 to PostgreSQL migration like ora2pg. The scripts in the db2pg tool converts as much as possible of a DB2 UDB database. This tool does not work with DB2 zOS. It is very simple to use, you need a SQL dump of your schema and then use db2pg script to convert it to a PostgreSQL schema.

2. Full convert

   Enterprise tool quickly copies DB2 database to PostgreSQL. The conversion of DB2 to PostgreSQL database using Full Convert tool is very simple.
   Steps:

   • Connect to the source database i.e. DB2
   • Optional: Choose the tables that you want to convert(by default all the tables selected)
   • Start the conversion.

Conclusion

As we could see, migrating from DB2 to PostgreSQL is not rocket science, but we need to keep in mind the thing that we saw earlier to avoid big issues in our system. So, we only need to be careful in the task and go ahead, you can migrate to the most advanced open source database and take advantage of its benefits.