Little addendum for db2move


A few days ago I posted a link to a tiny tutorial on how to use db2move to transfer db2 databases from Linux to Windows. This works very nicely, apart from the fact that I had a problem with some tables that were created but not filled with data. The data is in the .ixf files but will not be imported into the tables. db2move even tells me it was successful.

I stumbled across a few more or less helpful discussion threads that covered some problems, ob f which seemed to be related to mine. It turns out that the .lst file that gets written on export only contains tables that were successfully exported without any errors or warnings. Hmm. There were some warnings on export of tables. There were lots of .msg files. And they all contained warnings that I thought can be ignored without any harm. The warnings I got on export were all of this kind:

SQL3132W The character data in column "INRPAN" will be truncated to size "5".

The “affected” columns were either of type CHAR or VARCHAR, so I thought there’s no deal here. Especially because  the msg files all ended with this line:

SQL3105N The Export utility has finished exporting "4113" rows.

So you might think all is good and fine. But for DB2 it’s not.
It turns out there is an “accept warnings” switch “-aw” that, if appended to the export command, leads to all tables that had warnings will be included in the .lst file and thus will be imported to the target db.

So I guess it is a good idea to export data using this call in most cases:

db2move YOUR_DB_NAME export -aw

And here finally is something I found to be extremely helpful when it comes to changing the schema of tables, sequences and such (a job that sounds easy, but isn’t):

db2 "call ADMIN_COPY_SCHEMA('OldSchema','NewSchma', 'COPY', NULL,NULL,NULL,'TEST','ERR_TAB')"