Db2 – How to use the unload command to export a table to a IXF file

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring a backup from one instance to another, as it automatically grants instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the databases restored. With 9.7 FP5, the setting is dynamic and does not require bouncing of the instance. I realize my original question had nothing to do with security, but it can be encountered and thought that this would be a handy tidbit placed here.

EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the clause WITHOUT ROLLING FORWARD in my RESTORE command. This would have taken the database out of roll forward pending state immediately upon successful restore, which would mean that I would not have had to then issue the ROLLFORWARD DB command.

First you need to catalog the remote instance.

db2 catalog tcpip node <my alias node name> remote <host or host alias name> server <port>

Issue db2 terminate to make sure the catalog entry remains.

Now you can actually catalog the remote database.

db2 catalog db <remote database name> as <local alias> at node <node alias supplied above>

And once again issue db2 terminate

Here is an example of what this could look like:

db2 catalog tcpip node devinst1 remote devservera server 50100;
db2 terminate;
db2 catalog db crmdb as remotedb at node devinst1;
db2 terminate;

Now you can connect to that local database using either the db name (crmdb) or the alias name (remotedb) depending on your needs.

Note: Sometimes you can run into issues where it will say things about a null package id. This would mean that particular packages need to be bound for the connection to work. Easiest way to solve this is to have your instance owner id (of the remote database) make the first connection and then disconnect or to have your id you are using have BINDADD authority. Then the correct packages will get bound. You may not run into this, especially if you are on the same version and fix pack level of DB. If you ever differ between "client" and "server" is when you for sure have to do this.