The exception table is a user-created table that reflects the definition of the table being loaded, and includes some additional columns. It is specified by the FOR EXCEPTION clause on the LOAD command. The table is used to store copies of rows that violate unique index rules; the utility will not check for constraints or foreign key violations other than violations of uniqueness. DATALINK exceptions are also captured in the exception table.
A unique key is a key for which no two values are equal. The mechanism used to enforce this constraint is called a unique index. A primary key is a special case of a unique key. A table cannot have more than one primary key.
| Note: | Any rows rejected because of invalid data before the building of an index are not inserted into the exception table. |
Rows are appended to existing information in the exception table; this can include invalid rows from previous load operations. If you want only the invalid rows from the current load operation, you must remove the existing rows before invoking the utility.
The exception table used with the load utility is identical to the exception tables used by the SET INTEGRITY statement.
An exception table should be used when loading data which has a unique index and the possibility of duplicate records. If an exception table is not specified, and duplicate records are found, the load operation continues, and only a warning message is issued about the deleted duplicate records. The records themselves are not logged.
After the load operation completes, information in the exception table can be used to correct data that is in error. The corrected data can then be inserted into the table.
For more information about exception tables, see the SQL Reference.