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.
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
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:
- /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
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
The New York Times is running an interesting article how Siceworks has become a hub for IT professionals. Spiceworks combines
Have you tried it?