Data migration using LOADPLUS (FORMAT BMCLOAD)

This option provides a high-speed Db2 data migration solution. When you specify this option, UNLOAD PLUS performs the following functions:

• Unloads data in an internal format that only LOADPLUS can read
• Creates verification records to ensure that the data format matches the table definition
• Generates LOADPLUS control cards that you can use to reload the data

Table structure considerations for FORMAT BMCLOAD

In general, when you use the FORMAT BMCLOAD option, the structure of the table that you are unloading must be nearly identical to the structure of the table that you plan to load with LOADPLUS. This topic describes considerations regarding the structure of the two tables.

General structure considerations

The following characteristics of the table structure do not have to be the same on the target as they are on the source:

• Type of table space (for example, segmented)
• Indexes
• Index and table space versions

The following specific characteristics must match:

• Coded character set identifier (CCSID)
• Row format (basic or reordered)

Column definitions

The following considerations apply to column definitions in the two tables:

• The number, order, and data type of the columns in the two tables must be the same, but column names may be different.
• For VARCHAR, VARGRAPHIC, and VARBINARY columns, target table columns can have a length that is greater than the source table columns.

• Row ID columns that are defined in the source table must be defined in the target table as GENERATED BY DEFAULT.

  When a table contains a row ID column that is defined as GENERATED ALWAYS, UNLOAD PLUS unloads the data. However, LOADPLUS fails to load the data if the column is not defined in the target table as GENERATED BY DEFAULT.

DB2 user exits

The following considerations apply to user exits that are defined for the two tables:

• If the source has an EDITPROC, the target must have an EDITPROC with the same name.
• If the source has a VALIDPROC or FIELDPROC, the target must have one, but the names can differ. LOADPLUS displays a warning but loads the rows.

Command options that are incompatible with FORMAT BMCLOAD

The following table describes the options that are not valid when you specify FORMAT BMCLOAD, and how UNLOAD PLUS responds if your job contains these options:

Additional restrictions and considerations for FORMAT BMCLOAD

The following additional restrictions and considerations apply when you specify FORMAT BMCLOAD:

• The SELECT statement is limited as follows:
  • The SELECT statement cannot contain any column information. UNLOAD PLUS supports SELECT * only.
  • The SELECT INTO statement cannot contain any field information.
  • The record options block cannot contain RECORDID.
• If you unload tables that contain identity columns and you specify SHRLEVEL CHANGE CONSISTENT NO, the MAXASSIGNEDVAL value supplied in internal verification records to LOADPLUS might be incorrect because the unloaded table is available for updates.
• You cannot use FORMAT BMCLOAD when you are unloading LOB or XML data. UNLOAD PLUS terminates in this case.
• You cannot load output from FORMAT BMCLOAD by using a version of LOADPLUS that is earlier than the version of UNLOAD PLUS that you are running. For example, if you unload your data by using FORMAT BMCLOAD in version 10.2 of UNLOAD PLUS, you cannot load it by using FORMAT BMCUNLOAD in version 10.1 of LOADPLUS.
• You cannot use user exits other than those described in Db2-user-exits-with-UNLOAD-PLUS.
• Message BMC51674I indicates the total number of records that UNLOAD PLUS wrote to the output file, including the control records. An additional message, BMC51679I, indicates the number of data records and the number of control records that UNLOAD PLUS wrote.