Quick Beginnings for DB2 Extended Enterprise Edition for UNIX

Migrating Databases

To migrate databases owned by an instance, you need to perform the following steps:

Log on with a user ID that has SYSADM authority.
Ensure that the databases you want to migrate are cataloged.
Migrate the database. Refer to the Command Reference for the database migration syntax command.

Specifying Table Spaces when Migrating DB2 Parallel Edition Version 1.x

When you migrate DB2 Parallel Edition Version 1.x to DB2 Extended Enterprise Edition Version 5, the migration command will automatically create a default set of SMS table spaces and migrate your tables to one of these table spaces. This default behavior will work well for many of your tables. You may, however, want to migrate some tables to specific table spaces that you identify ahead of time.

For example, one very important consideration is that with DB2 Extended Enterprise Edition Version 5, when you load a table, its table space is placed in a quiesce state for the duration of the load. This means that other tables in the table space are not accessible during the load. (This is not the case with DB2 Parallel Edition Version 1.x, as it does not support table spaces.) So, if you regularly perform large loads and depend on other tables being available during the loads, you should identify the target tables of these loads, and using the information described below, migrate each to its own dedicated table space.

Other reasons you may want to customize table spaces at migration time include:

DB2 Extended Enterprise Edition Version 5's point-in-time table space roll-forward recovery can be simplified if related tables are in the same table space.
To take advantage of DB2 Extended Enterprise Edition Version 5's ability to restore a specified subset of table spaces from any backup.

By default, when you migrate DB2 Parallel Edition to DB2 Extended Enterprise Edition Version 5, the following will occur:

An SMS table space will be created for every nodegroup (the table space name will be the same as the nodegroup's name).
Every table will be migrated to the table space created for the nodegroup.

For example, if you have:

     nodegroup NG1 consisting of nodes 1, 2 and 3
     nodegroup NG2 consisting of nodes 3 and 4

and

     table TA in NG1
     table TB in NG2
     table TC in NG1
     table TD in NG1

Migration will create two SMS table spaces, named NG1 and NG2 and migrate tables TA, TC and TD to NG1, and table TB to NG2. Table space NG1 will exist in nodegroup NG1 (nodes 1, 2, and 3). Table space NG2 will exist in nodegroup NG2 (nodes 3 and 4). This is the default behavior

You can override the default behavior as follows:

Create a file that lists specific tables and which table space each table should be migrated to. The format of the file is:
```
   line 1: <d>
   line 2: <d><schema><d><table><d><tablespace><d>
   line 3: <d><schema><d><table><d><tablespace><d>
           .
           .
           .
   line n: <d><schema><d><table><d><tablespace><d>
```
where <d> is a single character delimiter. An example is as follows:
```
     path  :  /a/b/c
     ---------------
     !"             !
     !"S1"TC"TS1    !
     !"S1"TD"TS2"   !
     ----------------
```
This file indicates to create SMS table spaces TS1 and TS2, and to migrate table TC (under schema S1) and table TD (under schema S1) to TS2. The delimiter used is this file is the " character.

If you use a delimiter of ' ' (a blank), ensure that the first line actually contains a blank in the first column (a new line character in the first column is not treated as a blank).
Ensure that the DB2 instance owner has permission the read the file.
Run the db2set command as follows:
```
   db2set DB2_MIGRATE_TS_INFO=/a/b/c
```
where a/b/c is the fully qualified path to the above file. (Alternatively, you can specify DB2_MIGRATE_TS_INFO in your db2profile file. Using the db2set command is the recommended method.)
Run the db2stop command.
Run the db2start command.
Perform the migration.
This results in two additional SMS table spaces being created during migration: TS1 and TS2. Table TC will be migrated to TS1 and table TD will be migrated to TS2. Any table not listed in the file will be migrated according to the default behavior. Continuing with the previous example, table TA will be migrated to table space NG and table TB will be migrated to table space NG2.

You should be aware of the following:

All identifiers in the file are case sensitive.
You can specify the same table space more than once in the file. If you do, ensure that all tables to be migrated to the table space exist in the same nodegroup.
You cannot specify the same table more than once.
Any errors that occur when the file is processed are reported in the db2diag.log file. An SQLCODE of -901 will be reported and the migration will be rolled back. If this occurs, correct the problems reported in the db2diag.log file and try the migration again.
This procedure has been tested to create up to 100 additional table spaces. You should be able to create more, but this has not been tested. (Note that the maximum number of table spaces per database is 4096.)

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]