The SET INTEGRITY statement is used to do one of the following:
When the statement is used to check integrity for a table after it has been loaded, the system will by default incrementally process the table by checking only the append portion for constraint violations. However, there are some situations in which the system will decide that full processing (by checking the entire table for constraints violations) is necessary to ensure data integrity. There is also a situation in which user needs to explicitly request incremental processing by specifying the INCREMENTAL option. See Notes for details.
The SET INTEGRITY statement is under transaction control.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges required to execute SET INTEGRITY depend on the use of the statement, as outlined below:
The privileges of the authorization ID of the statement must include at least one of the following:
The privileges of the authorization ID of the statement must include at least one of the following:
The authorization ID of the statement must have at least one of the following:
Syntax
(1)
>>-SET--INTEGRITY----------------------------------------------->
.-,-------------.
V |
>-----+-FOR-----table-name---+---+-OFF---------------------------------------+--+>
| +-TO DATALINK RECONCILE PENDING-------------+ |
| '-IMMEDIATE CHECKED--+--------------------+-' |
| '-| check-options |--' |
| .-,------------------------------------. |
| V | |
'-FOR-----table-name--| integrity-options |---+--IMMEDIATE UNCHECKED-- ---'
>--------------------------------------------------------------><
check-options
|--+-------------+--- ----+----------------------+--------------|
'-INCREMENTAL-' '-| exception-clause |-'
exception-clause
.-,----------------------------------.
V |
|---FOR--EXCEPTION-------IN--table-name--USE--table-name---+----|
integrity-options
|---+-ALL----------------------------------+--------------------|
| .-,-------------------------------. |
| V | |
'----+-FOREIGN KEY----------------+--+-'
+-CHECK----------------------+
+-DATALINK RECONCILE PENDING-+
'-SUMMARY--------------------'
Notes:
Description
Note that it is possible that a table may already be in the check pending state with only one type of integrity checking turned off; in such a situation the other type of integrity checking will also be turned off.
If any table in the list is a parent table, the check pending state for foreign key constraints is extended to all dependent and descendent tables.
If any table in the list is an underlying table of a summary table, the check pending state is extended to such summary tables.
Only very limited activity is allowed on a table that is in the check pending state. Notes lists the restrictions.
Dependent and descendent table are not affected when this option is specified.
If it is a summary table, then the data is checked against the query and refreshed as necessary.
DATALINK values are not checked, even when the table is in DRP or DRNP state. The RECONCILE command or API should be used to perform the reconciliation of DATALINK values. The table will be taken out of check pending state but continue to have the DRP or DRNP flag set. This makes the table usable while the reconciliation of DATALINK values can be deferred to another time.
If the table is not in the check pending state, an error (SQLSTATE 55019) is returned.
Note that incremental processing is the default behaviour even if the INCREMENTAL option is not specified. See Notes for situations in which the INCREMENTAL option is needed.
If the FOR EXCEPTION clause is not specified and any constraints are violated, then only the first violation detected is returned to the user (SQLSTATE 23514). In the case of a violation in any table, all the tables are left in the check pending state, as they were before the execution of the statement. This clause cannot be specified if the table-name is a summary table (SQLSTATE 42997).
This is specified for a given table either by specifying ALL, or by specifying CHECK when only check constraints are off for that table, or by specifying FOREIGN KEY when only foreign key constraints are off for that table, or by specifying DATALINK RECONCILE PENDING when only DATALINK integrity constraints are off for that table or by specifying SUMMARY when only summary table query checking is off for that summary table.
This is specified for a given table by specifying only CHECK, FOREIGN KEY, SUMMARY or DATALINK RECONCILE PENDING when any of those types of constraints are off for that table.
The state change is not extended to any tables not explicitly included in the list.
If the parent of a dependent table is in the check pending state, the foreign key constraints of a dependent table cannot be marked to bypass checking (the check constraints checking can be bypassed).
The implications with respect to data integrity should be considered before using this option. See Notes.
For example, a DELETE of a row in a parent table that cascades to a dependent table that is in the check pending state is not allowed.
The removal of violating rows by the SET INTEGRITY statement is not a delete event. Therefore, triggers are never activated by a SET INTEGRITY statement.
The fact that integrity was turned on without doing deferred checking will be recorded in the catalog (the value in the CONST_CHECKED column in the SYSCAT.TABLES view will be set to 'U'). This indicates that the user has assumed responsibility for data integrity with respect to the specific constraints. This value remains until either:
The 'W' state differs from the 'N' state in that it records the fact that the integrity was previously checked by the user and not yet by the system, and if given a choice, the systems rechecks the whole table for data integrity and then changes it to the 'Y' state. If no choice is given (e.g. when IMMEDIATE UNCHECKED or INCREMENTAL is specified) it is changed back to the 'U' state to record that some data is still not verified by the system. In the latter (INCREMENTAL) case, a warning (SQLSTATE 01636) is returned.
Example
Example 1: The following is an example of a query that gives us information about the check pending state of tables. SUBSTR is used to extract the first 2 bytes of the CONST_CHECKED column of SYSCAT.TABLES. The first byte represents foreign key constraints, and the second byte represents check constraints.
SELECT TABNAME,
SUBSTR( CONST_CHECKED, 1, 1 ) AS FK_CHECKED,
SUBSTR( CONST_CHECKED, 2, 1 ) AS CC_CHECKED
FROM SYSCAT.TABLES
WHERE STATUS = 'C';
Example 2: Set tables T1 and T2 in the check pending state:
SET INTEGRITY FOR T1, T2 OFF;
Example 3: Check the integrity for T1 and get the first violation only:
SET INTEGRITY FOR T1 IMMEDIATE CHECKED
Example 4: Check the integrity for T1 and T2 and put the violating rows into exception tables E1 and E2:
SET INTEGRITY FOR T1, T2 IMMEDIATE CHECKED
FOR EXCEPTION IN T1 USE E1,
IN T2 USE E2;
Example 5: Enable FOREIGN KEY constraint checking in T1 and CHECK constraint checking in T2 to be bypassed with the IMMEDIATE CHECKED option:
SET INTEGRITY FOR T1 FOREIGN KEY,
T2 CHECK IMMEDIATE UNCHECKED;
Example 6: Add a check constraint and a foreign key to the EMP_ACT table, using two ALTER TABLE statements. To perform constraint checking in a single pass of the table, integrity checking is turned off before the ALTER statements and checked after execution.
SET INTEGRITY FOR EMP_ACT OFF; ALTER TABLE EMP_ACT ADD CHECK (EMSTDATE <= EMENDATE); ALTER TABLE EMP_ACT ADD FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE; SET INTEGRITY FOR EMP_ACT IMMEDIATE CHECKED;