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
  • Row-begin and row-end columns
  • Transaction-start-ID columns
  • Generated expression columns

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

Table 1. Dummy fields in the generated LOAD statement
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
Row-begin and row-end columns DSN_ROWBEGIN and DSN_ROWEND
Transaction-start-ID columns DSN_TRANSACTIONSTID
Generated expression columns DSN_column-name (where column-name is the name of the column)

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. Alternatively, for a row change timestamp column, you can define the column as GENERATED ALWAYS in the target table and add the OVERRIDE(ROWCHANGE) option to the LOAD statement.
    • 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.
    For row-begin and row-end columns:
    • In the target table, define the columns as GENERATED ALWAYS.
    • Make the following changes to the generated LOAD control statement:
      • Specify the OVERRIDE(SYSTEMPERIOD) 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 OVERRIDE(TRANSID) option.
      • Remove the IGNOREFIELDS keyword.
      • Change the dummy field names to the corresponding column names in the target table.
    For generated expression columns: Make the following changes to the generated LOAD control statement:
    • Specify the OVERRIDE(NONDETERMINISTIC) 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.