Migrating data

Use the automatic data migration feature to perform a complete system migration from a source to a target Db2® instance, including unloading, transferring, and loading of the data on the target hosts.

Prior to version 4.1, you could use Optim™ High Performance Unload to repartition data while unloading it by using repartitioning rules that were specified in the TARGET KEYS or the TARGET TABLE clause. In this method, repartitioned data is unloaded in separate files, one per target database partition. However, the corresponding output files cannot be generated on the hosts where the related target database partitions are located. Therefore, in a migration scenario, after you unload and repartition the data, you still need to transfer the output files on the corresponding machine and load them on the target instance. By using the automatic data migration feature, you can complete all of the steps that are required to migrate data in a single operation.

To use the data migration feature, you need to install Optim High Performance Unload on both the source and the target systems. Optim High Performance Unload on the source (S1, S2 and S3 in the following figure) performs unloading and repartitioning. Then, Optim High Performance Unload sends the output streams across the network to the Optim High Performance Unload daemon on the target machines (T1 and T2 in the following figure). The target daemon creates a single named pipe for each database partition on the target system and begins loading the data. The Optim High Performance Unload daemon consolidates the multiple streams that are coming from each of the source database partitions into a single stream, which Optim High Performance Unload writes to the named pipes. As a result, Optim High Performance Unload performs the unloading and the loading processes in parallel.

A description for this figure is provided in the text above. — Figure 1. Automatic data migration

Automatic data migration syntax keywords

To use the Optim High Performance Unload automatic data migration feature, you need to create a control file and modify it:

Use the MIGRATE keyword instead of the UNLOAD keyword at the beginning of an UNLOAD block.
You can specify the migration of a whole database, a whole table space, or of a list of tables that are specified in the SELECT blocks related to the MIGRATE block.
Use the TARGET ENVIRONMENT and the WORKING IN clauses to define the target system specifications and the location for temporary files.
You can use the UMASK clause to override the system permissions on the target system. The umask option is also available for the command line and in the configuration file db2hpu.cfg.

Output format specification during data migration

When migrating data, the output format specification is not necessary. If there is no FORMAT clause explicitly specified, the default format used to generate the temporary files that are loaded back is the DEL output format. You can also explicitly specify the FORMAT clause to choose one of the following formats:

MIGRATION: The special keyword for migration format. At this time, the MIGRATION format works the same way as the DEL format.
DEL: The default migration format.
DELIMITED: Do not use the DELIMITED format for data migration unless you have a row delimiter (end of line character) among character string data that you want to migrate automatically. In this case, you can load the corresponding delimited file only if the DELPRIORITYCHAR modifier is specified in the corresponding load command. The only way to generate the appropriate load command is by using the DELIMITED format and the corresponding DELPRIORITYCHAR modifier. Since the DELIMITED format offers much more capabilities with the modifiers, it should be avoided as much as possible for system migration purposes.
IXF: Use it to ensure that floating point values are migrated without a loss of precision towards a mono-partitioned database.
ASC: Use it to ensure that floating point values are migrated without a loss of precision towards a DPF database.

Attention: If there are char delimiter characters among your string data, you must use the DOUBLE DELIM ON option to successfully migrate the data. For the DEL format, the char delimiters are double quotation marks ("), and if there are double quotation marks inside the string data, the DOUBLE DELIM ON option is necessary for successful migration.