Loading tables with special column types by using generated LOAD statements

When you run the UNLOAD utility or the REORG utility with the UNLOAD EXTERNAL or DISCARD option, DB2® generates a LOAD statement for the unloaded data. You can then use this LOAD statement to load the unloaded data into any table that has a compatible format.

About this task

However, because the following types of columns can contain generated values, they need special consideration:

  • ROWID columns
  • Identity columns
  • Row change timestamp columns
  • Start of changeRow-begin and row-end columnsEnd of change
  • Transaction-start-ID columns

For these table column types, the generated LOAD statement contains dummy fields. The following table lists those dummy fields.

Source table column type Dummy fields in the generated LOAD statement
ROWID with GENERATED ALWAYS DSN_ROWID
Identity column with GENERATED ALWAYS DSN_IDENTITY
Row change timestamp column with GENERATED ALWAYS DSN_RCTIMESTAMP
Start of changeRow-begin and row-end columnsEnd of change Start of changeDSN_ROWBEGIN and DSN_ROWEND End of change
Transaction-start-ID columns DSN_TRANSACTIONSTID

Procedure

To load tables with special column types by using generated LOAD statements

  1. Make sure that the target table has a compatible format for the data that you want load.
  2. Decide whether you want to include the data for the columns with special column types when you load the unloaded data.
  3. If you do not want to load data for the columns with special column types, make sure that the column is defined in the target table as GENERATED ALWAYS.

    The IGNOREFIELDS keyword in the generated LOAD statement causes DB2 to skip the dummy fields when it loads the data into a table.

  4. If you want to load data for the columns with special column types, take the following actions:
    Option Description
    For ROWID, identity, or row change timestamp columns:
    • In the target table, define the ROWID, identity, or row change timestamp column as GENERATED BY DEFAULT
    • In the generated LOAD control statement, remove the IGNOREFIELDS keyword and change the dummy field names to the corresponding column names in the target table.
    Start of changeFor row-begin and row-end columns:End of change
    • Start of changeIn the target table, define the columns as GENERATED ALWAYS. End of change
    • Make the following changes to the generated LOAD control statement:
      • Specify the PERIODOVERRIDE option.
      • Remove the IGNOREFIELDS keyword.
      • Change the dummy field names to the corresponding column names in the target table.
    For transaction-start-ID columns:
    • In the target table, define transaction-start-ID columns as GENERATED ALWAYS
    • Make the following changes to the generated LOAD control statement:
      • Specify the TRANSIDOVERRIDE option.
      • Remove the IGNOREFIELDS keyword.
      • Change the dummy field names to the corresponding column names in the target table.
  5. Issue the LOAD utility control statement.