When the statement is used to perform integrity processing for a table after it has been loaded or attached, the system can incrementally process the table by checking only the appended portion for constraints violations. If the subject table is a materialized query table or a staging table, and load, attach, or detach operations are performed on its underlying tables, the system can incrementally refresh the materialized query table or incrementally propagate to the staging table with only the delta portions of its underlying tables. However, there are some situations in which the system will not be able to perform such optimizations and will instead perform full integrity processing to ensure data integrity. Full integrity processing is done by checking the entire table for constraints violations, recomputing a materialized query table's definition, or marking a staging table as inconsistent. The latter implies that a full refresh of its associated materialized query table is required. There is also a situation in which you might want to explicitly request incremental processing by specifying the INCREMENTAL option.
The SET INTEGRITY statement is under transaction control.
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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
The privileges required to execute the SET INTEGRITY statement depend on the purpose, as outlined below.
>>-SET--INTEGRITY-----------------------------------------------> .-,----------. V | >--+-FOR----table-name-+--+-OFF--| access-mode-clause |--| cascade-clause |-+-----------------+->< | +-FULL ACCESS-------------------------------------+ | | '-PRUNE-------------------------------------------' | | .-,-------------------------------------. | | V | | +-FOR----table-name--| table-checked-options |-+--IMMEDIATE CHECKED--+-------------------+-+ | '-| check-options |-' | | .-,---------------------------------------. | | V | | '-FOR----table-name--| table-unchecked-options |-+--IMMEDIATE UNCHECKED--------------------' access-mode-clause .-NO ACCESS---. |--+-------------+----------------------------------------------| '-READ ACCESS-' cascade-clause .-CASCADE IMMEDIATE--| to-descendent-types |-. |--+--------------------------------------------+---------------| '-CASCADE DEFERRED---------------------------' to-descendent-types .-TO ALL TABLES-------------------------. |--+---------------------------------------+--------------------| | .-,-----------------------------. | | V | | '-TO----+-MATERIALIZED QUERY TABLES-+-+-' +-FOREIGN KEY TABLES--------+ '-STAGING TABLES------------' table-checked-options .-,----------------------------------. V | |----+-| online-options |-------------+-+-----------------------| +-GENERATE IDENTITY--------------+ '-| query-optimization-options |-' online-options .-ALLOW NO ACCESS----. |--+--------------------+---------------------------------------| +-ALLOW READ ACCESS--+ '-ALLOW WRITE ACCESS-' query-optimization-options |--+---------------------------------------------------------------------------------------+--| | .-ALLOW QUERY OPTIMIZATION-. .-WITH REFRESH AGE ANY-. | '-+--------------------------+--USING REFRESH DEFERRED TABLES--+----------------------+-' check-options |--●--| incremental-options |--●--+-----------------+-----------> '-FORCE GENERATED-' >--●--+-------+--●--+-------------+-----------------------------> '-PRUNE-' '-FULL ACCESS-' >--●--+----------------------+----------------------------------| '-| exception-clause |-' incremental-options |--+-----------------+------------------------------------------| +-INCREMENTAL-----+ '-NOT INCREMENTAL-' exception-clause .-,-----------------------. V | |--FOR EXCEPTION----| in-table-use-clause |-+-------------------| in-table-use-clause |--IN--table-name--USE--table-name------------------------------| table-unchecked-options .-,--------------------------------------. V | |----| integrity-options |--+-------------+-+-------------------| '-FULL ACCESS-' integrity-options |--+-ALL------------------------+-------------------------------| | .-,----------------------. | | V | | '---+-FOREIGN KEY--------+-+-' +-CHECK--------------+ +-MATERIALIZED QUERY-+ +-GENERATED COLUMN---+ '-STAGING------------'
Specifying TO ALL TABLES is equivalent to specifying TO FOREIGN KEY TABLES, TO MATERIALIZED QUERY TABLES, and TO STAGING TABLES, all in the same statement.
When the table is taken out of set integrity pending state, its descendent tables are, if necessary, put in set integrity pending state. A warning to indicate that descendent tables have been put in set integrity pending state is returned (SQLSTATE 01586).
If the table is a system-maintained materialized query table, the data is checked against the query and refreshed as necessary. (IMMEDIATE CHECKED cannot be used for user-maintained materialized query tables.) If the table is a staging table, the data is checked against its query definition and propagated as necessary.
When an underlying table (that has dependent immediate materialized query tables or dependent immediate staging tables) in the invocation list is incrementally processed, the underlying table is put in no data movement state, as required, after the SET INTEGRITY statement executes. When all incrementally refreshable dependent immediate materialized query tables and staging tables are taken out of set integrity pending state, the underlying table is automatically brought out of the no data movement state into the full access state. If the FULL ACCESS option is specified with the IMMEDIATE CHECKED option, the underlying table is put directly in full access state (bypassing the no data movement state). In DB2® Version 9.7. Fix Pack 1 and later, specifying the FULL ACCESS option only removes the dependency between the dependent tables and underlying table. The underlying table continues to be unavailable until the data partition detach process is completed by the asynchronous partition detach task.
Dependent immediate materialized query tables that have not been refreshed might undergo a full recomputation in the subsequent REFRESH TABLE statement, and dependent immediate staging tables that have not had the appended portions of the table propagated to them might be flagged as inconsistent.
When an underlying table in the invocation list requires full processing, or does not have dependent immediate materialized query tables, or dependent immediate staging tables, the underlying table is put directly into full access state after the SET INTEGRITY statement executes, regardless of whether the FULL ACCESS option was specified.
If the FOR EXCEPTION option is not specified and any constraints are violated, only the first detected violation is returned (SQLSTATE 23514). If there is a violation in any table, all of the tables are left in set integrity pending state.
It is recommended to always use the FOR EXCEPTION option when checking for constraints violations to prevent a rollback of the SET INTEGRITY statement if a violation is found.
If no other types of integrity processing are required on the table after a specific type of integrity processing has been marked as bypassed, the table is immediately taken out of set integrity pending state.
When an underlying table in the invocation list is incrementally processed, and it has dependent immediate materialized query tables or dependent immediate staging tables, the underlying table is placed, as required, in the no data movement state after the SET INTEGRITY statement executes. When all incrementally refreshable dependent immediate materialized query tables and staging tables have been taken out of set integrity pending state, the underlying table is automatically brought out of the no data movement state into the full access state. If the FULL ACCESS option is specified with the IMMEDIATE UNCHECKED option, the underlying table is placed directly in full access state (it bypasses the no data movement state). Dependent immediate materialized query tables that have not been refreshed might undergo a full recomputation in the subsequent REFRESH TABLE statement, and dependent immediate staging tables that have not had the appended portions of the table propagated to them might be flagged as inconsistent.
In DB2 V9.7. Fix Pack 1 and later, specifying the FULL ACCESS option only removes the dependency between the dependent tables and underlying table. The underlying table continues to be unavailable until the data partition detach process is completed by the asynchronous partition detach task.
When an underlying table in the invocation list requires full processing, or does not have dependent immediate materialized query tables, or dependent immediate staging tables, the underlying table is placed directly in full access state after the SET INTEGRITY statement executes, regardless of whether the FULL ACCESS option has been specified.
If the FULL ACCESS option has been specified with the IMMEDIATE UNCHECKED option, and the statement does not bring the table out of set integrity pending state, an error is returned (SQLSTATE 428FH).
Consider the data integrity implications of this option before using it. See the "Notes" section below.
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.
The 'W' state differs from the 'N' state in that it records the fact that integrity was previously checked by the user, but not yet by the system. If the user issues the SET INTEGRITY ... IMMEDIATE CHECKED statement with the NOT INCREMENTAL option, the system rechecks the whole table for data integrity (or performs a full refresh on a materialized query table), and then changes the 'W' state to the 'Y' state. If IMMEDIATE UNCHECKED is specified, or if NOT INCREMENTAL is not specified, the 'W' state is changed back to the 'U' state to record the fact that some data has still not been verified by the system. In the latter case (when the NOT INCREMENTAL is not specified), a warning is returned (SQLSTATE 01636).
For a dependent immediate materialized query table, this propagation is done whenever the underlying table is brought out of set integrity pending state, and whenever the materialized query table is refreshed. For a dependent deferred materialized query table, this propagation is done whenever the materialized query table is refreshed. For dependent staging tables, this propagation is done whenever the underlying table is brought out of set integrity pending state. These propagated 'U' values in the CONST_CHECKED columns of dependent materialized query tables and staging tables record the fact that these materialized query tables and staging tables depend on some underlying table whose required integrity processing has been bypassed using the IMMEDIATE UNCHECKED option.
For a materialized query table, the 'U' value in the CONST_CHECKED column that was propagated by the underlying table will remain until the materialized query table is fully refreshed and none of its underlying tables have a 'U' value in their corresponding CONST_CHECKED column. After such a refresh, the 'U' value in the CONST_CHECKED column for the materialized query table will be changed to 'Y'.
For a staging table, the 'U' value in the CONST_CHECKED column that was propagated by the underlying table will remain until the corresponding deferred materialized query table of the staging table is refreshed. After such a refresh, the 'U' value in the CONST_CHECKED column for the staging table will be changed to 'Y'.
SET INTEGRITY FOR T IMMEDIATE CHECKED
In
the following scenarios, neither the INCREMENTAL check option for
T nor an incremental refresh of T---if T is a materialized query table
(MQT) or a staging table---is supported: SELECT TABNAME, STATUS, ACCESS_MODE,
SUBSTR(CONST_CHECKED,1,1) AS FK_CHECKED,
SUBSTR(CONST_CHECKED,2,1) AS CC_CHECKED,
SUBSTR(CONST_CHECKED,5,1) AS MQT_CHECKED,
SUBSTR(CONST_CHECKED,6,1) AS GC_CHECKED,
SUBSTR(CONST_CHECKED,7,1) AS STG_CHECKED,
SUBSTR(CONST_CHECKED,8,1) AS DP_CHECKED
FROM SYSCAT.TABLES
SET INTEGRITY FOR PARENT OFF
NO ACCESS CASCADE IMMEDIATE
SET INTEGRITY FOR PARENT OFF
READ ACCESS CASCADE DEFERRED
SET INTEGRITY FOR FACT_TABLE IMMEDIATE CHECKED
SET INTEGRITY FOR SALES, PRODUCTS IMMEDIATE CHECKED
FOR EXCEPTION IN SALES USE SALES_EXCEPTIONS,
IN PRODUCTS USE PRODUCTS_EXCEPTIONS
SET INTEGRITY FOR MANAGER FOREIGN KEY,
EMPLOYEE CHECK IMMEDIATE UNCHECKED
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
FOR EXCEPTION IN EMP_ACT USE EMP_ACT_EXCEPTIONS
SET INTEGRITY FOR SALES IMMEDIATE CHECKED
FORCE GENERATED
LOAD FROM 2000_DATA.DEL OF DEL
INSERT INTO SALES ALLOW READ ACCESS;
LOAD FROM 2001_DATA.DEL OF DEL
INSERT INTO SALES ALLOW READ ACCESS;
SET INTEGRITY FOR SALES ALLOW READ ACCESS IMMEDIATE CHECKED
FOR EXCEPTION IN SALES USE SALES_EXCEPTIONS;
REFRESH TABLE SALES_SUMMARY;
ALTER TABLE SALES
ATTACH PARTITION STARTING (100) ENDING (200)
FROM SOURCE;
SET INTEGRITY FOR SALES ALLOW WRITE ACCESS, SALES_SUMMARY ALLOW WRITE ACCESS
IMMEDIATE CHECKED FOR EXCEPTION IN SALES
USE SALES_EXCEPTIONS;
ALTER TABLE SALES
DETACH PARTITION 2000_PART INTO ARCHIVE_TABLE;
SET INTEGRITY FOR SALES_SUMMARY
IMMEDIATE CHECKED;
CREATE TABLE YEARLY_SALES
AS (SELECT YEAR, SUM(SALES)AS SALES
FROM FACT_TABLE GROUP BY YEAR)
DATA INITIALLY DEFERRED REFRESH DEFERRED MAINTAINED BY USER
SET INTEGRITY FOR YEARLY_SALES
ALL IMMEDIATE UNCHECKED