SQL Reference

SET INTEGRITY

The SET INTEGRITY ¹⁰⁵ statement is used to do one of the following:

Turn off integrity checking for one or more tables. This includes check constraint and referential constraint checking, datalink integrity checking, and generation of values for generated columns. If the table is a summary table with REFRESH IMMEDIATE, the immediate refreshing of the data is turned off. Note that this places the table(s) into a check pending state where only limited access by a restricted set of statements and commands is allowed. Primary key and unique constraints continue to be checked.
Both turn the integrity checking back on for one or more tables and to carry out all the deferred checking. If the table is a summary table, the data is refreshed as necessary and, when defined with the REFRESH IMMEDIATE attribute, immediate refreshing of the data is turned on.
Turn on integrity checking for one or more tables without first carrying out any deferred integrity checking. If the table is a summary table defined with the REFRESH IMMEDIATE attribute, immediate refreshing of the data is turned on.
Place the table into check pending state if the table is already in DataLink Reconcile Pending (DRP) or DataLink Reconcile Not Possible (DRNP) state. If a table is not in either of those states, then unconditionally set the table to DRP state and check pending state.

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:

Turn off integrity checking.
The privileges of the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on the tables and all their dependents and descendants in referential integrity constraints
- SYSADM or DBADM authority
- LOAD authority
Both turn on integrity checking and carry out checking.
The privileges of the authorization ID of the statement must include at least one of the following:
- SYSADM or DBADM authority
- CONTROL privilege on the tables that are being checked and if exceptions are being posted to one or more tables, INSERT privilege on the exception tables
- LOAD authority and, if exceptions are being posted to one or more tables:
  - SELECT and DELETE privilege on each table being checked; and
  - INSERT privilege on the exception tables.
Turn on integrity checking without first carrying out checking.
The authorization ID of the statement must have at least one of the following:
- SYSADM or DBADM authority
- CONTROL privilege on the tables that are being checked
- LOAD authority

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-'       '-FORCE GENERATED-'
 
>-----+----------------------+----------------------------------|
      '-| exception-clause |-'
 
exception-clause
 
                      .-,----------------------------------.
                      V                                    |
|---FOR--EXCEPTION-------IN--table-name--USE--table-name---+----|
 
integrity-options
 
|---+-ALL----------------------------------+--------------------|
    |  .-,-------------------------------. |
    |  V                                 | |
    '----+-FOREIGN KEY----------------+--+-'
         +-CHECK----------------------+
         +-DATALINK RECONCILE PENDING-+
         +-SUMMARY--------------------+
         '-GENERATED COLUMN-----------'

Notes:

For compatibility with previous versions, the keyword CONSTRAINTS will continue to be supported.

Description

table-name

Identifies a table for integrity processing. It must be a table described in the catalog and must not be a view, catalog table, or typed table.

OFF

Specifies that the tables are to have their foreign key constraints, check constraints, and column generation turned off and are, therefore to be placed into the check pending state. If it is a summary table, then immediate refreshing is turned off (if applicable) and the summary table is placed into check pending state.

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.

TO DATALINK RECONCILE PENDING

Specifies that the tables are to have DATALINK integrity constraint checking turned off and the tables placed in check pending state. If the table is already in DataLink Reconcile Not Possible (DRNP) state, it remains in this state with check pending. Otherwise, the table is set to DataLink Reconcile Pending (DRP) state.

Dependent and descendent table are not affected when this option is specified.

IMMEDIATE CHECKED

Specifies that the table is to have its integrity checking turned on and that the integrity checking that was deferred is to be carried out. This is done in accordance with the information set in the STATUS and CONST_CHECKED columns of the SYSCAT.TABLES catalog. That is:

The value in STATUS must be C (the table is in the check pending state) or an error (SQLSTATE 51027) is returned.
The value in CONST_CHECKED indicates which integrity options are to be checked.

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.

check-options

FORCE GENERATED

If the table includes generated columns, the values are computed based on the expression and stored in the column. If this clause is not specified, the current values are compared to the computed value of the expression as if an equality check constraint existed.

INCREMENTAL

Specifies the application of deferred integrity checks on the appended portion (if any) of the table. If such a request cannot be satisfied (i.e. the system detects that the whole table needs to be checked for data integrity), an error (SQLSTATE 55019) will be returned. If the attribute is not specified, the system will determine if incremental processing is possible; if not, the whole table will be checked. See Notes for situations in which system will favor full processing (checking whole table for integrity) over incremental processing. Also, see Notes for situations in which the INCREMENTAL option is necessary and situations in which it cannot be specified.

If the table is not in the check pending state, an error (SQLSTATE 55019) is returned.

exception-clause

FOR EXCEPTION

Indicates that any row that is in violation of a foreign key constraint or a check constraint will be copied to an exception table and deleted from the original table. See Appendix N, Exception Tables for more information on these user-defined tables. Even if errors are detected the constraints are turned back on again and the table is taken out of the check pending state. A warning (SQLSTATE 01603) is issued to indicate that one or more rows have been moved to the exception tables.

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).

IN table-name

Specifies the table from which rows that violate constraints are to be copied. There must be one exception table specified for each table being checked.

USE table-name

Specifies the exception table into which error rows are to be copied.

integrity-options

Used to define the integrity options that are set to IMMEDIATE UNCHECKED.

ALL: This indicates that all integrity-options are to be turned on.
FOREIGN KEY: This indicates that foreign key constraints are to be turned on.
CHECK: This indicates that check constraints are to be turned on.
DATALINK RECONCILE PENDING: This indicates that DATALINK integrity constraints are to be turned on.
SUMMARY: This indicates that immediate refreshing should be turned on for a summary table with the REFRESH IMMEDIATE attribute.
GENERATED COLUMN: This indicates that generated columns are to be turned on.

IMMEDIATE UNCHECKED

Specifies one of the following:

The table is to have its integrity checking turned on (and, thus, are to be taken out of the check pending state) without having the table checked for integrity violations or the summary table is to have immediate refreshing turned on and be taken out of check pending state.
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, or by specifying GENERATED COLUMN when only column generation is off for that table.
The table is to have one type of integrity checking turned on, but is to be left in the check pending state.
This is specified for a given table by specifying only CHECK, FOREIGN KEY, SUMMARY, GENERATED COLUMN, 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.

Notes

Effects on tables in the check pending state:
- Use of SELECT, INSERT, UPDATE, or DELETE is disallowed on a table that is either:
  - in the check pending state itself
  - or requires access to another table that is in the check pending state.
  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.
- New constraints added to a table are normally enforced immediately. However, if the table is in check pending state the checking of any new constraints is deferred until the table is taken out of the check pending state.
- The CREATE INDEX statement cannot reference any tables that are in the check pending state. Similarly, ALTER TABLE to add a primary key or unique constraint cannot reference any tables that are in the check pending state.
- The utilities EXPORT, IMPORT, REORG, and REORGCHK are not allowed to operate on a table in the check pending state. Note that the IMPORT utility differs from the LOAD utility in that it always checks the constraints immediately.
- The utilities LOAD, BACKUP, RESTORE, ROLLFORWARD, UPDATE STATISTICS, RUNSTATS, LIST HISTORY, and ROLLFORWARD are allowed on a table in the check pending state.
- The statements ALTER TABLE, COMMENT ON, DROP TABLE, CREATE ALIAS, CREATE TRIGGER, CREATE VIEW, GRANT, REVOKE, and SET INTEGRITY can reference a table that is in the check pending state.
- Packages, views and any other objects that depend on a table that is in the check pending state will return an error when the table is accessed at run time.
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. Similarly, updating generated columns using the FORCE GENERATED option does not activate triggers.
Because incremental processing is the default behavior, the INCREMENTAL option is not needed in most cases. It is needed, however, in two cases:
- To force incremental processing on a table that was previously taken out of the check pending state with the IMMEDIATE UNCHECKED option. By default, the system chooses full processing to verify integrity of ALL data. This default behavior can be overridden by specifying the INCREMENTAL option to check only the newly appended portion. (Refer to the bullet "Warning about the use of IMMEDIATE UNCHECKED clause" for further details.)
- To ensure that integrity checks are indeed processed incrementally. By specifying the INCREMENTAL option, the system returns an error (SQLSTATE 55019) when the system detects that full processing is needed to ensure data integrity.
Warning about the use of the IMMEDIATE UNCHECKED clause:
- This clause is intended to be used by utility programs and its use by application programs is not recommended.
  The fact that integrity checking 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 table is put back into the check pending state (by referencing the table in a SET INTEGRITY statement with the OFF clause), at which time the 'U' values in the CONST_CHECKED column will be changed to the 'W' values, indicating that the user had previously assumed responsibility for data integrity and the system needs to verify the data.
  - All unchecked constraints for the table are dropped.
  - A REFRESH TABLE statement is issued for a summary table.
  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.
After appending data using Load Insert, the SET INTEGRITY ... IMMEDIATE CHECKED statement checks the table for constraint violation and then brings the table out of the check pending state. The system determines if incrementally processing on the table is possible. If so, only the appended portion is checked for integrity violations. If not, the system will check the whole table for integrity violations (see below for situations when the system favors full processing).
Situations where the system checks the whole table for integrity when the user did not specify the INCREMENTAL option for the statement SET INTEGRITY for T IMMEDIATE CHECKED are:
1. when the table T has one or more 'W' values in its CONST_CHECKED column in the SYSCAT.TABLES catalog.
Situations in which the system must check the whole table for integrity (INCREMENTAL option cannot be specified) for the statement SET INTEGRITY for T IMMEDIATE CHECKED are:
1. when new constraints have been added to T itself, or to any of its parents which are in check pending state
2. when a Load Replace has taken place into T, or the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check on T
3. (cascading effect of full processing) when any parent of T has been Load Replaced or checked for integrity non-incrementally
4. if the table was in check pending state before migration, full processing is required the first time the table is checked for integrity after migration
5. if the table space containing the table or its parent has been rolled forward to a point in time.
A table that is in DataLink Reconcile Not Possible (DRNP) state requires corrective action to be taken (possibly outside of the database). Once corrective action is completed, the table is taken out of DRNP state using the IMMEDIATE UNCHECKED option. The RECONCILE command or API should then be used to check the DATALINK integrity constraints. For more details refer on removing a table from DataLink Reconcile Not Possible state refer to Administration Guide.
While integrity is being checked an exclusive lock is held on each table specified in the SET INTEGRITY invocation.
A shared lock is acquired on each table that is not listed in the SET INTEGRITY invocation but is a parent table of one of the dependent tables being checked.
If an error occurs during integrity checking, all the effects of the checking including deleting from the original and inserting into the exception tables will be rolled back.
If a SET INTEGRITY statement issued with a FORCE GENERATED clause fails because of a lack of log space, and log space cannot be sufficiently increased, the db2gncol command can be used to generate the values by using intermittent commits. SET INTEGRITY can then be rerun, without the FORCE GENERATED clause.

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

Example 7: Set integrity for generated columns.

   SET INTEGRITY FOR T1 IMMEDIATE CHECKED 
      FORCE GENERATED

Footnotes:

¹⁰⁵: The SET INTEGRITY statement, rather than the SET CONSTRAINTS statement, is the preferred method for working with integrity checking in DB2.

[ Top of Page | Previous Page | Next Page ]