Load exception tables

A load exception table is a consolidated report of all of the rows that violated unique index rules, range constraints, and security policies during a load operation. You specify a load exception table by using the FOR EXCEPTION clause of the LOAD command.

Restriction: An exception table cannot contain an identity column or any other type of generated column. If an identity column is present in the primary table, the corresponding column in the exception table should only contain the column's type, length, and nullability attributes. In addition, the exception table cannot be a range-partitioned table or a column-organized table or have a unique index. Moreover, you cannot specify an exception table if either of the following conditions is true:
  • The target table uses LBAC security and has at least one XML column
  • The target table is range partitioned and has at least one XML column

The exception table used with the load utility is identical to the exception tables used by the SET INTEGRITY statement. It is a user-created table that reflects the definition of the table being loaded and includes some additional columns.

You can assign a load exception table to the table space where the table being loaded resides or to another table space. In either case, assign the load exception table and the table being loaded to the same database partition group, and ensure that both tables use the same distribution key. Additionally, ensure that the exception table and table being loaded have the same partition map id (SYSIBM.SYSTABLES.PMAP_ID), which can potentially be different during the redistribute operation (add/drop database partition operation).

When to use an exception table

Use an exception table when loading data that has a unique index and could have duplicate records. If you do not specify an exception table and duplicate records are found, the load operation continues, and only a warning message is issued about the deleted duplicate records. The duplicate records are not logged.

After the load operation is completed, you can use information in the exception table to correct data that is in error. You can then insert the corrected data into the table.

Rows are appended to existing information in the exception table. Because there is no checking done to ensure that the table is empty, new information is simply added to the invalid rows from previous load operations. If you want only the invalid rows from the current load operation, you can remove the existing rows before invoking the utility. Alternatively, when you define a load operation, you can specify that the exception table record the time when a violation is discovered and the name of the constraint violated.

Because each deletion event is logged, the log could fill up during the delete phase of the load if there are a large number of records that violate a uniqueness condition.

Any rows rejected because of invalid data before the building of an index are not inserted into the exception table.