Examples of migrating DB2 databases from other systems to LINUX (1)

This article describes how to migrate a DB2 database from an AIX system to a LINUX system. The operations on the AIX system and on LINUX are described separately for your reference.

Suppose we have an IBM DB2 database, which contains hundreds or even more tables) and many indexes, foreign key constraints, and triggers. Some tables with self-incrementing fields contain the fields defined by generated always as identity ). We do not have a script for the accurate creation of these database objects. I often encounter this situation in the customer ).

Now we want to migrate this database to different operating systems, such as from AIX to LINUX.) What should we do? Because the operating system is different, the use of the BACKUP/RESTORE command is obviously not good. So can we use the db2move command? No. First, the db2move command cannot migrate indexes, foreign key constraints, and triggers, and cannot migrate tables with auto-increment fields. Then, do the export/import operations on each table? Oh, my God, this is a disaster. When should we do it for hundreds of tables? What's more, the import command cannot ensure that the data of the auto-increment field is exactly the same as that of the original one! The following is a solution provided by the author, which is quite effective.

Assume that the database name is sample and runs on AIX. Our goal is to migrate the database to LINUX. For simplicity, all database objects are in DB2INST1 mode. The db2inst1 user has all required permissions and the password is also db2inst1.

1) operations on the AIX system:

1) First, run the db2look command to obtain the DDL script of the database object:

Db2look-d SAMPLE-z DB2INST1-e-o sample. ddl-I db2inst1-w db2inst1

2) use the text editor to edit the generated sample. ddl: Creates table and index script statements, foreign key constraint statements, and trigger creation statements separately, and creates three DDL scripts:

Sample_tabs.ddl

Sample_foriegnkeys.ddl

Sample_triggers.ddl