PostgreSQL to DB2 export conversion

PostgreSQL and DB2 use different styles of conventions to represent NULL. In PostgreSQL, the convention is to have “N” in the data to be loaded with the COPY command. In DB2, the LOAD command can be set to consider and empty field as NULL. Thus, to convert from DB2 to POstgreSQL style data dump, assuming TAB as the delimiter, we would use:

sed -e 's/tt/t\Nt/g' -e 's/t$/t\N/g' -e 's/tt/t\Nt/g' db2_file > pg_file

Conversely, to convert from PostgreSQL to DB2 style, with TAB delimited data:

sed -e 's/t\Nt/tt/g' -e 's/t\N$/t/g' -e 's/t\Nt/tt/g' db2_file > pg_file

The reason we need to go with 3 steps is the following:

  • step 1: convert TAB NULL TAB
  • step2: convert TAB NULL at the end of line
  • step3: convert TAB NULL TAB from sequences where originally there was TAB NULL TAB NULL

All this has been tested on AIX and Linux.


DB2 The maximum number of open files has been reached

After a fresh install of DB2 Express C (free download from IBM) 9.5.2 on Linux AMD 64bit, I got the following error trying to load some tables for testing:

SQL3500W  The utility is beginning the "LOAD" phase at time "01/22/2009
07:40:27.557594".

SQL0958C  The maximum number of open files has been reached.  SQLSTATE=57009

After some investigation, I found that the following 3 places impact the open files:

  • /etc/security/limits.conf
  • /proc/sys/fs/file-max & /etc/sysctl.conf & sysctl -w fs.file-max=xxxx
  • DB2 Config parameter MAXFILOP (max on Linux 64 is 61440)

Once all these are correclty set, things should run smoothly.  Remember to restart the instance. 

In addition, if you are using NFS, either for the core database or for the files involving LOAD, EXPORT, BACKUP, RESTORE, make sure that nfslock is running:

chkconfig nfslock on
service nfslock start

DB2 List of Tables

To retrieve to list of tables in DB2, you need to use the system tables, and then a little bit of sed.  Here is how it goes:

db2 "SELECT tabschema || '.' || tabname FROM syscat.tables" | sed -e '/ //g'

You can redirect this into a file, and you may need to cut off the first few and last few lines.  If you only want to get the tables from a given user space:

SELECT tabschema || '.' || tabname FROM syscat.tables WHERE tbspace = 'USERSPACE1';

If you want the list for a specific schema:

SELECT tabschema || '.' || tabname FROM syscat.tables WHERE tabschema = 'MYSCHEMA';

Reference: How to get useful information from the DB2 UDB system catalog


Manage your IT infrastructure with Spiceworks

The New York Times is running an interesting article how Siceworks has become a hub for IT professionals.  Spiceworks combines

Have you tried it?