Partitioned data migration example

This example shows how to migrate partitioned data manually.

Attention: Starting with Optim™ High Performance Unload V5.1, you can perform automatic data migration, which completes the entire process described in this example automatically.

Table TB_EMPLOYEE is stored on the table space TS_DATA1, and TS_DATA1 is on four database partitions in the database DB_PROD: 10, 20, 30, and 40. To migrate table TB_EMPLOYEE to table TB_EMPLOYEE_NEW in the table space TS_DATA2, which is on two database partitions in the database DB_TARGET: 20 and 200, you can use Optim High Performance Unload to calculate the distribution of rows according to the new table.

To repartition the data from database DB_PROD, you can use the following command. Comments are preceded by dashes (- -):

--control file to unload TB_EMPLOYEE table
GLOBAL CONNECT TO DB_PROD ;
UNLOAD TABLESPACE DB2 NO
SELECT * FROM TB_EMPLOYEE ;
TARGET TABLE (TB_EMPLOYEE_NEW IN DB_TARGET) HEADER FIRST
--unload to file employee
OUTFILE("/home/db2inst1/data/employee")
--specify DEL format
FORMAT DEL;

Processing of this command results in the following two files:

/home/db2inst1/data/employee.020 (containing the data for the new partition 20)
/home/db2inst1/data/employee.200 (containing the data for the new partition 200)

Both files contain the map of the database partitions used when the LOAD Db2® command was issued; for example:

LOAD FROM /home/db2inst1/data/employee OF DEL
INSERT INTO TB_EMPLOYEE_NEW
PARTITIONED DB CONFIG MODE LOAD_ONLY ;

When Optim High Performance Unload repartition data (four database partitions to two in this example) into a table with a different repartitioning key, the distribution of rows is recalculated based on the new key. For example, for the table:

TB_EMPLOYEE_NEW
(COL1 CHAR(6), COL2 VARCHAR(12), COL3 SMALLINT, COL4 DATE)

with the key of COL3 and assuming the existing and target tables are in the same database, the control file that allows recalculation of the row distribution would look like this:

GLOBAL CONNECT TO DB_PROD ;
UNLOAD TABLESPACE DB2 NO
SELECT * FROM TB_EMPLOYEE ;
TARGET KEYS ((COL3) PARTS(20,200)) HEADER FIRST
OUTFILE("/home/db2inst1/data/employee")
FORMAT DEL;

The result is two files that you can load into table TB_EMPLOYEE_NEW. It is this form, rather than TARGET TABLE, which would be used if you want to repartition an existing table rather than moving it to a different system. You use the PARTS parameter to specify the database partitions that the table will use; that is, two database partition numbers that are in the node group that you plan to create the table space with prior to dropping and recreating this table.