Fung's DBA World

Migrating SMS to DMS in DB2

October 18, 2016

Some DBA would be required to convert the SMS tablespace to DMS tablespace. But seems DB2 do not have such utilities to convert SMS to DMS directly. We cannot use backup/restore for restoring the SMS to DMS, the traditional way to convert SMS to DMS is to use db2look and db2move. Also hope some people can tell me a better option.

To convert the SMS to DMS( in my production environment, there are over 300 tables belong to multiple schemas), following steps would be required(Outage required):

1. Extracting the DDL of the tables

Before migration:

There’re two options which can let you extract the table structure.

Option A: Extracting the whole schema’s table DDl

From the output, there are only two schemas in my SMS table space, if we have fewer related schemas, this option can be considered.

Now the table definition are saved to the output files.

Option B: Extracting the table DDL only in the tablespace

This method would be recommended if we have many schemas reside in the SMS tablespace.

2. Exporting all the tables reside in the tablespace

Export the table data into a directory by using db2move, db move will generate two files for each table, so it’s a good idea to put all the files into a separate directory.

4. Modifying the DDL script and creating the tables from the DDL script

Double check the script to ensure there’s no missing anything. If everything is OK, then creating the table into the new tablespace:

Double check to ensure everything is fine, then we can proceed.

Compare the output of LOAD.out and EXPORT.out, if the committed rows are exactly equal, then everything is okay.

6. Setting the integrity for the tables

First, using following SQL to query the table status:

Generating the set integrity command:

Some errors are expected, because we separated co-dependency tables to set integrity, which they should be together.

Execute the set integrity together for the co-dependency tables:

Finally, we’re here, converted the SMS to DMS succefully.

EOF