Question
This document describes how to determine if a table has been manually taken out of check pending state.
Answer
To determine if a table has been taken out of check pending (by the SET INTEGRITY ... IMMEDIATE UNCHECKED command), you can use the following SELECT query to see what options were used to perform the SET INTEGRITY command:
select tabschema as Schema, tabname as Name, status as State, substr(const_checked,1,1) as Foreign_Keys, substr(const_checked,2,1) as Check_Constraints, substr(const_checked,5,1) as Summary_Tables from syscat.tables where tabname = '<tablename>' and tabschema = '<schema>'
...where <tablename> is the table and <schema> is the table schema.
For example, if <schema> = db2inst1 and <tablename> = employee, then an example of the result of the query is as follows:
SCHEMA NAME STATE FOREIGN_KEYS CHECK_CONSTRAINTS SUMMARY_TABLES
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------ ----------------- --------------
DB2INST1 EMPLOYEE N U Y Y
1 record(s) selected.
The FOREIGN_KEYS column shows the status of the check constraint for table DB2INST1.EMPLOYEE and is shown to have a value of 'U' -- indicating it was checked by the user using the SET INTEGRITY ... IMMEDIATE UNCHECKED command.