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
- Flexible open source licencing and easy availability from public cloud providers like AWS, Google cloud, Microsoft Azure.
- 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
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.
|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)|
|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)|
|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|
|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 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.
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.
SELECT * FROM EMP ORDER BY EMPID FETCH FIRST 10 ROWS ONLY;
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.
( INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20) );
( 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 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).
SELECT CEIL(123.89) FROM SYSIBM.SYSDUMMY1; SELECT CEILING(123.89) FROM SYSIBM.SYSDUMMY1;
SELECT CEIL(123.89) ; SELECT CEILING(123.89) ;
It converts the input to date values. You can convert DATE function to TO_DATE function in PostgreSQL.
SELECT DATE ('2018-09-21') FROM SYSIBM.SYSDUMMY1;
SELECT TO_DATE ('21-09-2018',’DD-MM-YYYY’) ;
It returns the day (day of the month) part of a date or equivalent value. The output format is integer.
SELECT DAY (DATE('2016-09-21')) FROM SYSIBM.SYSDUMMY1;
SELECT DATE_PART('day', '2016- 09-21'::date);
It returns the month part of the date value. The output format is integer.
SELECT MONTH (DATE('2016-09-21')) FROM SYSIBM.SYSDUMMY1;
SELECT DATE_PART ('month', '2016-09- 21'::date);
Returns the position of string. The POSSTR function is replaced by POSITION function in PostgreSQL.
Usage : POSSTR(
, ) SELECT POSSTR('PostgreSQL and DB2', 'and') FROM SYSIBM.SYSDUMMY1;
IN ) SELECT POSITION('and' IN'PostgreSQL and DB2');
It returns a pseudorandom floating-point value in the range of zero to one inclusive. You can replace RAND function to RANDOM in PostgreSQL.
SELECT RAND() FROM SYSIBM.SYSDUMMY1;
You can use some tools to migrate DB2 database to PostgreSQL. Please test the tool before use it.
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.
Enterprise tool quickly copies DB2 database to PostgreSQL. The conversion of DB2 to PostgreSQL database using Full Convert tool is very simple.
- 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.
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.
Subscribe to get our best and freshest content