Following a load operation, the loaded table may be in check pending state if it has table check constraints or referential integrity constraints defined on it. The STATUS flag of the SYSCAT.TABLES entry corresponding to the loaded table indicates the check pending state of the table. For the loaded table to be usable, the STATUS must have a value of N, indicating a normal state.
To remove the check pending state, use the SET INTEGRITY statement (see the SQL Reference). The SET INTEGRITY statement checks a table for constraints violations, and takes the table out of check pending state. If all the load operations are performed in INSERT mode, the SET INTEGRITY statement will by default incrementally process the constraints (that is, it will check only the appended portion of the table for constraints violations). For example:
db2 load from infile1.ixf of ixf insert into table1 db2 set integrity for table1 immediate checked
Only the appended portion of TABLE1 is checked for constraint violations. Checking only the appended portion for constraints violations is faster than checking the entire table, especially in the case of a large table with small amounts of appended data.
One or more tables can be checked in a single invocation of this statement. If a dependent table is to be checked, the parent table must not be in check pending state. In the case of a referential integrity cycle, all the tables involved in the cycle must be included in a single invocation of the SET INTEGRITY statement. It may be convenient to check the parent table for constraints violations while a dependent table is being loaded. This can only occur if the two tables are not in the same table space.
When issuing the SET INTEGRITY statement, you can specify the INCREMENTAL option to explicitly request incremental processing. In most cases, this option is not needed, because the default behavior is incremental processing. If incremental processing is not possible, full processing is used automatically. When the INCREMENTAL option is specified, but incremental processing is not possible, an error is returned if:
If the T table has one or more W values in the CONST_CHECKED column of the SYSCAT.TABLES catalog (see the SQL Reference, SET INTEGRITY statement, for a description of the W state), the system checks the entire table for constraints violations if the INCREMENTAL option is not specified. If the option is specified, it is allowed, but the CONST_CHECKED column of SYSTABLES will be marked as U to indicate that not all data has been verified by the system.
Use the load exception table option to capture information about rows with constraints violations (see Exception Table).
The SET INTEGRITY statement does not activate any DELETE triggers as a result of deleting rows that violate constraints, but once the table is removed from check pending state, triggers are active. Thus, if we correct data and insert rows from the exception table into the loaded table, any INSERT triggers defined on the table will be activated. The implications of this should be considered. One option is to drop the INSERT trigger, insert rows from the exception table, and then recreate the INSERT trigger.