Database migration between different systems

From: http://doc.linuxpk.com/15886.html

 

Suppose we have an IBM DB2 database with many tables (hundreds or even more) and many indexes, foreign key constraints, and triggers. The most terrible thing is that there are some tables with self-incrementing fields (tables with fields defined by generated always as identity ). We do not have an accurate script for the creation of these database objects (I often encounter this situation ).

What should we do if we want to migrate this database to different operating systems (such as from Aix to Linux? 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

3) use the following SQL statement to generate a script to export all data: exort. SQL

DB2 "select 'export to '| rtrim (tabname) | '. ixf of ixf select * from '| rtrim (tabname) |'; 'from syscat. tables where tabschema = 'db2inst1' "> export. SQL

4) edit the generated export. SQL, remove the header and tail information, and only retain the necessary export command.

5) use the following SQL statement to generate a script to import all data (we use the load command and must use it)

DB2 "select 'Load from' | rtrim (tabname) | '. ixf of ixf insert into '| rtrim (tabname) |'; 'from syscat. tables where tabschema = 'db2inst1' "> load. SQL

6) edit the generated load. SQL, remove the head and tail information, only keep the necessary load command. search the tables in the sample_tabs.ddl file that contain the self-incrementing fields (the tables that contain the fields defined by generated always as identity), and load. the load command of the table containing the auto-increment field in SQL is added to the modified by identityoverride statement (between of ixf and insert, for example:

Load from mytable. ixf of ixf modified by identityoverride insert into mytable ;)

Note: The modified by identityoverride in the load command ensures that the values of the Self-incrementing fields during data import are consistent with those in the original database.

7) use the DB2-tvf export. SQL command to export data from all tables.

(2) Copy sample_tabs.ddl, sample_foriegnkeys.ddl, sample_triggers.ddl, load. SQL, and all exported. ixf data files to the Linux server.

(3) operations on Linux:

1) Use the create database Command to create a database sample, create necessary tablespace, and configure necessary database parameters.

2) connect to the sample database and use the sample_tabs.dd script file to create a table (DB2-tvf sample_tabs.ddl ).

3) Go to the directory where the. ixf data file is stored and use DB2-tvf load. SQL to import data.

4) use the sample_foriegnkeys.ddl and sample_triggers.ddl script files to create foreign key constraints and triggers.

OK, our database is basically migrated!

Finally, do not forget to run the runstats command on each table. You can generate the runstats script by referring to the above method to generate the export and load command script, and then run it.