blog
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 you know DB2 and PostgreSQL are RDBMS but there are some incompatibilities. In this blog, we can see some of these incompatibilities.
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.
- 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.
- 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.
