Tools for moving Db2 data

Moving Db2 data can be complicated. Fortunately, several tools exist that can help to simplify the process.

Important: Before copying any Db2 data, resolve any data that is in an inconsistent state. Use the DISPLAY DATABASE command to determine whether any inconsistent state exists, and the RECOVER INDOUBT command or the RECOVER utility to resolve the inconsistency. The copying process generally loses all traces of an inconsistency except the problems that result.

Although Db2 data sets are created using VSAM access method services, they are specially formatted for Db2 and cannot be processed by services that use VSAM record processing. They can be processed by VSAM utilities that use control-interval (CI) processing and, if they are linear data sets (LDSs), also by utilities that recognize the LDS type.

Furthermore, copying the data might not be enough. Some operations require copying Db2 object definitions. And when copying from one subsystem to another, you must consider internal values that appear in the Db2 catalog and the log, for example, the Db2 object identifiers (OBIDs) and log relative byte addresses (RBAs).

The following tools can help to simplify the operations:

  • The REORG and LOAD utilities move data sets from one disk device type to another within the same Db2 subsystem.

    The INCURSOR option of the LOAD utility allows you to specify a cursor to select data from another Db2 table or tables, which can be on a remote Db2 system. Use the EXEC SQL utility control statement to declare the cursor before executing the LOAD utility. This option uses the Db2 UDB family cross-loader function.

  • The COPY and RECOVER utilities allow you to recover an image copy of a Db2 table space or index space onto another disk device within the same subsystem.
  • The UNLOAD or REORG UNLOAD EXTERNAL utility unloads a Db2 table into a sequential file and generates statements to allow the LOAD utility to load it elsewhere.
  • The DSN1COPY utility copies the data set for a table space or index space to another data set. It can also translate the object identifiers and reset the log RBAs in the target data set. When you use the OBIDXLAT option of DSN1COPY to move objects from one system to another, use REPAIR CATALOG to update the version information in the catalog and directory for the target table space or index.

You might also want to use the following tools to move Db2 data:

  • The Db2 DataPropagator is a licensed program that can extract data from Db2 tables, DL/I databases, VSAM files, and sequential files.
  • DFSMS, which contains the following functional components:
    • Data Set Services (DFSMSdss)

      Use DFSMSdss to copy data between disk devices. You can use online panels to control this, through the Interactive Storage Management Facility (ISMF) that is available with DFSMS.

    • Data Facility Product (DFSMSdfp)

      This is a prerequisite for Db2. You can use access method services EXPORT and IMPORT commands with Db2 data sets when control interval processing (CIMODE) is used.

    • Hierarchical Storage Manager (DFSMShsm)

      With the MIGRATE, HMIGRATE, or HRECALL commands, which can specify specific data set names, you can move data sets from one disk device type to another within the same Db2 subsystem. Do not migrate the Db2 directory, Db2 catalog, and the work file database (DSNDB07). Do not migrate any data sets that are in use frequently, such as the bootstrap data set and the active log. With the MIGRATE VOLUME command, you can move an entire disk volume from one device type to another. The program can be controlled using online panels, through the Interactive Storage Management Facility (ISMF).

The following table shows which tools are applicable to specific operations.

Table 1. Tools applicable to data-moving operations
Tool Moving a data set Copying a database Copying an entire subsystem
REORG and LOAD Yes Yes No
UNLOAD Yes No No
COPY and RECOVER Yes No No
DSNTIAUL Yes Yes No
DSN1COPY Yes Yes No
DataRefresher or DXT Yes Yes No
DFSMSdss Yes No Yes
DFSMSdfp Yes No Yes
DFSMShsm Yes No No

Some of the listed tools rebuild the table space and index space data sets, and they therefore generally require longer to execute than the tools that merely copy them. The tools that rebuild are REORG and LOAD, RECOVER and REBUILD, DSNTIAUL, and DataRefresher. The tools that merely copy data sets are DSN1COPY, DFSMSdss, DFSMSdfp EXPORT and IMPORT, and DFSMShsm.

DSN1COPY is fairly efficient in use, but somewhat complex to set up. It requires a separate job step to allocate the target data sets, one job step for each data set to copy the data, and a step to delete or rename the source data sets. DFSMSdss, DFSMSdfp, and DFSMShsm all simplify the job setup significantly.

Although less efficient in execution, RECOVER is easy to set up if image copies and recover jobs already exist. You might only need to redefine the data sets involved and recover the objects as usual.