Scenario: Migrating data with Optim High Performance Unload

Edit online

This scenario describes how a DBA can use Optim High Performance Unload to automatically migrate data from one system to another.

About this task

A banking company has purchased new hardware for its database, OLDDB. The existing Db2® system, M1, has seven DPF partitions. The new system, M2, has bigger and faster drives, so it requires only four DPF partitions. Steve, a senior DBA, needs to migrate the data from the old system to the new one. He will use a single Optim High Performance Unload control file to unload the data, transfer it, and load it into the new database. Because Optim High Performance Unload performs the loading and the unloading steps in parallel by using named pipes, the migration process is fast and system resources are used efficiently.

The following instructions describe the steps that Steve will take to migrate data from one system to another:

Procedure

  1. Create an empty database, NEWDB, with the new instance, newinst, on M2.
    NEWDB must be identical to the OLDDB database on M1. All of the tables must have the same names and the same data types and sizes. You can use any tool to create the database (for example, the db2look tool or IBM® Data Studio Administrator).
  2. Install Optim High Performance Unload on both M1 and M2.
  3. Create a Optim High Performance Unload control file and specify the migration options.
    For example:
    GLOBAL CONNECT TO OLDDB 
UMASK "022"
;
MIGRATE DATABASE 
TARGET ENVIRONMENT (INSTANCE "newinst" ON "M2" IN NEWDB) 
WORKING IN ("user_dir_on_new_system") 
TARGET KEYS (CURRENT PARTS(ALL)) 
FORMAT MIGRATION
;
    When you create the control file, consider the following options:
    • Specify the user directory for temporary files on M2 in the WORKING IN clause.
    • Use the UMASK "022" option to have the appropriate umask permissions on the target system.
    • Use the TARGET KEYS (CURRENT PARTS(ALL)) option to specify that the database partition map on M2 should include all of the database partitions on that machine.
    • You do not need to specify the FORMAT option explicitly; the MIGRATION keyword specifies the default format for migration, which is DEL.
    Restriction: If any tables that you want to migrate contain LOB data types, you cannot use named pipes for migration. In this case, Optim High Performance Unload will use output files instead of named pipes, which will prevent it from loading and unloading the data in parallel. You can also explicitly specify the WITH FILES option in the WORKING IN clause to use output files.
  4. Save the control file in a directory on the old system.
    For example, C:\HPU_ControlFiles\scenario2.ctr.
  5. Create credentials of local type for the user concerned on the target environment.
  6. Open the command line and run the Optim High Performance Unload command with the control file: 
    db2hpu -f C:\HPU_ControlFiles\scenario2.ctr

Results

Optim High Performance Unload unloads the entire database on the new system and starts Db2 Load to load the data. Optim High Performance Unload will perform all of the repartitioning work automatically.