How to Convert IBM DB2 Database to PostgreSQL

IBM DB2 is one of the world's oldest relational database management systems. However, its high cost of ownership for large databases and strict licensing policy lead some organization to the idea of migrating from DB2 to another DBMS.

Why PostgreSQL?

When planning a database migration, it is logical to review open-source systems in order to reduce total cost of ownership. PostgreSQL offers valuable benefits such as:

• liberal licensing similar to BSD or MIT
• true open source ecosystem
• availability on major SAAS platforms like Google cloud, AWS, Microsoft Azure

DB2 to PostgreSQL Migration Steps

Here are the steps to migrate IBM DB2 database to PostgreSQL:

• export table definitions and data from the source database (SQLWorkbench tool can be used for this purpose)
• post-process SQL DDL statements to make it compatible with PostgreSQL format
• import the resulting statements and data to PostgreSQL server (pgloader tool can be used for this purpose)
• handle views, triggers, stored procedures and functions

This is how to make DB2 scripts compatible with PostgreSQL format:

• data types must be converted according to this table
• IDENTITY columns must be defined as SERIAL in PostgreSQL tables
• DB2 has "default" default values. For example INTEGER DEFAULT means INTEGER DEFAULT 0. PostgreSQL does not accept it.
• DB2 accepts the NULL in columns having default values and uses the DEFAULT. In PostgreSQL the appropriate trigger is required to fix this.
• DB2 embedded functions must be converted into PostgreSQL equivalents as it is specified here
• DB2 does not allow SELECT without FROM and uses special table "SYSIBM.SYSDUMMY1" for trivial SELECT-queries. PostgreSQL allows SELECT without FROM and does not need this.

DB2 to PostgreSQL Conversion Tools

As you may see, database migration from IBM DB2 to PostgreSQL is a tedious procedure that requires a lot of efforts. Manual conversion may cause data loss or corruption due to human errors. Fortunately, there are some tools to automate migration from DB2 to PostgreSQL.

db2topg is a set of easy to use Perl scripts to convert DB2 schemas and data into PostgreSQL format. The result is number of SQL scripts that must be run in particular order:

1. creation of statements of tables, sequences, tablespaces
2. export.db2 script produces a bunch of del/lob files, containing all tables content
3. creation of indexes and foreign keys
4. creation of views, triggers and check constraints

As you may see, this approach requires a lot of intermediate steps that must be handled manually.

In order to get fully automated database migration with just a few clicks, consider DB2 to PostgreSQL converter.