SET INTEGRITY statement

The SET INTEGRITY statement is used to set the integrity pending state on tables, place tables into full access state, and prune the contents of one or more staging tables.

The following operations can be performed with the SET INTEGRITY statement:

Bring one or more tables out of set integrity pending state (previously known as check pending state) by performing required integrity processing on those tables.
Bring one or more tables out of set integrity pending state without performing required integrity processing on those tables.
Place one or more tables in set integrity pending state.
Place one or more tables into full access state.
Prune the contents of one or more staging tables.

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.

SMP parallelization of set integrity constraint and referential integrity checking can be enabled in non-MPP environments by setting the DB2_EXTENDED_OPTIMIZATION registry variable. If the PRLSI ON option is used, only not incremental set integrity statements will be parallelized. To parallelize incremental as well as non-incremental set integrity statements, use the PRLSI INCR option. To see an example of how to set this, refer to Example 14.

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges required to execute the SET INTEGRITY statement depend on the purpose, as outlined in the following list.

Bringing tables out of set integrity pending state and performing the required integrity processing.
The privileges held by the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on:
  - The tables on which integrity processing is performed and, if exception tables are provided for one or more of those tables, INSERT privilege on the exception tables or INSERTIN privilege on the schema containing the exception tables
  - All descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables that will implicitly be placed in set integrity pending state by the statement
- LOAD authority on the database or LOAD authority on the schema containing the table (with conditions). The following conditions must all be met before LOAD authority can be considered as providing valid privileges:
  - The required integrity processing does not involve the following actions:
    - Refreshing a materialized query table
    - Propagating to a staging table
    - Updating a generated or identity column
  - If exception tables are provided for one or more tables, the required access is granted for the duration of the integrity processing to the tables on which integrity processing is performed, and to the associated exception tables. That is:
    - SELECT and DELETE privilege on each table on which integrity processing is performed, or SELECTIN and DELETEIN privilege on the schema containing the table on which integrity processing is performed and
    - INSERT privilege on the exception tables or INSERTIN privilege on the schema containing the exception tables
- DATAACCESS authority on the relevant schema
- DATAACCESS authority
Bringing tables out of set integrity pending state without performing the required integrity processing.
The privileges held by the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on the tables that are being processed; CONTROL privilege on each descendent foreign key table, descendent immediate materialized query table, and descendent immediate staging table that will implicitly be placed in set integrity pending state by the statement
- LOAD authority on the database or LOAD authority on the relevant schema
- DATAACCESS authority or DATAACCESS authority on the relevant schema
- DBADM authority or SCHEMAADM authority on the relevant schema
Placing tables in set integrity pending state.
The privileges held by the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on:
  - The specified tables, and
  - The descendent foreign key tables that will be placed in set integrity pending state by the statement, and
  - The descendent immediate materialized query tables that will be placed in set integrity pending state by the statement, and
  - The descendent immediate staging tables that will be placed in set integrity pending state by the statement
- LOAD authority on the database or LOAD authority on the relevant schema
- DATAACCESS authority or DATAACCESS authority on the relevant schema
- DBADM authority or SCHEMAADM authority on the relevant schema
Place a table into the full access state.
The privileges held by the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on the tables that are placed into the full access state
- LOAD authority on the database or LOAD authority on the relevant schema
- DATAACCESS authority or DATAACCESS authority on the relevant schema
- DBADM authority or SCHEMAADM authority on the relevant schema
Prune a staging table.
The privileges held by the authorization ID of the statement must include at least one of the following:
- CONTROL privilege on the table being pruned
- DATAACCESS authority or DATAACCESS authority on the relevant schema

Description

FOR table-name

Identifies one or more tables 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 placed in set integrity pending state. Only very limited activity is allowed on a table that is in set integrity pending state.

access-mode-clauseSpecifies the readability of the table while it is in set integrity pending state.

NO ACCESS: Specifies that the table is to be put in set integrity pending no access state, which does not allow read or write access to the table.
READ ACCESS: Specifies that the table is to be put in set integrity pending read access state, which allows read access to the non-appended portion of the table. This option is not allowed on a table that is in set integrity pending no access state (SQLSTATE 428FH).

cascade-clauseSpecifies whether the set integrity pending state of the table referenced in the SET INTEGRITY statement is to be immediately cascaded to descendent tables.

CASCADE IMMEDIATE

Specifies that the set integrity pending state is to be immediately extended to descendent tables.

to-descendent-typesSpecifies the type of descendent tables to which the set integrity pending state is immediately cascaded.

TO ALL TABLES: Specifies that the set integrity pending state is to be immediately cascaded to all descendent tables of the tables in the invocation list. Descendent tables include all descendent foreign key tables, immediate staging tables, and immediate materialized query tables that are descendants of the tables in the invocation list, or descendants of descendent foreign key tables.
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.
TO MATERIALIZED QUERY TABLES: If only TO MATERIALIZED QUERY TABLES is specified, the set integrity pending state is to be immediately cascaded only to descendent immediate materialized query tables. Other descendent tables might later be put in set integrity pending state, if necessary, when the table is brought out of set integrity pending state. If both TO FOREIGN KEY TABLES and TO MATERIALIZED QUERY TABLES are specified, the set integrity pending state will be immediately cascaded to all descendent foreign key tables, all descendent immediate materialized query tables of the tables in the invocation list, and to all immediate materialized query tables that are descendants of the descendent foreign key tables.
TO FOREIGN KEY TABLES: Specifies that the set integrity pending state is to be immediately cascaded to descendent foreign key tables. Other descendent tables might later be put in set integrity pending state, if necessary, when the table is brought out of set integrity pending state.
TO STAGING TABLES: Specifies that the set integrity pending state is to be immediately cascaded to descendent staging tables. Other descendent tables might later be put in set integrity pending state, if necessary, when the table is brought out of set integrity pending state. If both TO FOREIGN KEY TABLES and TO STAGING TABLES are specified, the set integrity pending state will be immediately cascaded to all descendent foreign key tables, all descendent immediate staging tables of the tables in the invocation list, and to all immediate staging tables that are descendants of the descendent foreign key tables.

CASCADE DEFERRED

Specifies that only the tables in the invocation list are to be put in set integrity pending state. The states of the descendent tables will remain unchanged. Descendent foreign key tables might later be implicitly put in set integrity pending state when their parent tables are checked for constraints violations. Descendent immediate materialized query tables and descendent immediate staging tables might be implicitly put in set integrity pending state when one of their underlying tables is checked for integrity violations. A query of a table that is in the set integrity pending state might succeed if an eligible materialized query table that is not in the set integrity pending state is accessed by the query instead of the specified table.

If cascade-clause is not specified, the set integrity pending state is immediately cascaded to all descendent tables.

IMMEDIATE CHECKED

Specifies that the table is to be taken out of set integrity pending state by performing required integrity processing on the table. This is done in accordance with the information set in the STATUS and CONST_CHECKED columns of the SYSCAT.TABLES catalog view. That is:

The value in the STATUS column must be C (the table is in set integrity pending state), or an error is returned (SQLSTATE 51027), unless the table is a descendent foreign key table, descendent materialized query table, or descendent staging table of a table that is specified in the list, is in set integrity pending state, and whose intermediate ancestors are also in the list.
If the table being checked is in set integrity pending state, the value in CONST_CHECKED indicates which integrity options are to be checked.

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 or shadow tables.) If the table is a staging table, the data is checked against its query definition and propagated as necessary.

When the integrity of a child table is checked:

None of its parents can be in set integrity pending state, or
Each of its parents must be checked for constraints violations in the same SET INTEGRITY statement

When an immediate materialized query table is refreshed, or deltas are propagated to a staging table:

None of its underlying tables can be in set integrity pending state, or
Each of its underlying tables must be checked in the same SET INTEGRITY statement

Otherwise, an error is returned (SQLSTATE 428A8).

table-checked-options

online-optionsSpecifies the accessibility of the table while it is being processed.

ALLOW NO ACCESS: Specifies that no other users can access the table while it is being processed, except if they are using the Uncommitted Read isolation level.
ALLOW READ ACCESS: Specifies that other users have read-only access to the table while it is being processed.
ALLOW WRITE ACCESS: Specifies that other users have read and write access to the table while it is being processed.

GENERATE IDENTITY

Specifies that if the table includes an identity column, the values are generated by the SET INTEGRITY statement. By default, when the GENERATE IDENTITY option is specified, only attached rows will have their identity column values generated by the SET INTEGRITY statement. The NOT INCREMENTAL option must be specified in conjunction with the GENERATE IDENTITY option to have the SET INTEGRITY statement generate identity column values for all rows in the table, including attached rows, loaded rows, and existing rows. If the GENERATE IDENTITY option is not specified, the current identity column values for all rows in the table are left unchanged. When the table is a system-period temporal table, GENERATE IDENTITY with the NOT INCREMENTAL option is allowed only if you first issue an ALTER TABLE statement with the DROP VERSIONING clause (SQLSTATE 428FH).

query-optimization-optionsSpecifies the query optimization options for the maintenance of REFRESH DEFERRED materialized query tables.

ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES WITH REFRESH AGE ANY: Specifies that when the CURRENT REFRESH AGE special register is set to ANY, the maintenance of table-name will allow REFRESH DEFERRED materialized query tables to be used to optimize the query that maintains table-name. If table-name is not a REFRESH DEFERRED materialized query table, an error is returned (SQLSTATE 428FH). REFRESH IMMEDIATE materialized query tables are always considered during query optimization.

check-options

incremental-options

INCREMENTAL: Specifies the application of integrity processing on the appended portion (if any) of the table. If such a request cannot be satisfied (that is, the system detects that the whole table needs to be checked for data integrity), an error is returned (SQLSTATE 55019).
NOT INCREMENTAL: Specifies the application of integrity processing on the whole table. If the table is a materialized query table, the materialized query table definition is recomputed. If the table has at least one constraint defined on it, this option causes full processing of descendent foreign key tables and descendent immediate materialized query tables. If the table is a staging table, it is set to an inconsistent state.

If the incremental-options clause is not specified, the system determines whether incremental processing is possible; if not, the whole table is checked.

FORCE GENERATED

If the table includes generated by expression columns, the values are computed on the basis of the expression and stored in the column. If this option is not specified, the current values are compared to the computed value of the expression, as though an equality check constraint were in effect. If the table is processed for integrity incrementally, generated columns are computed only for the appended portion. When the table is a system-period temporal table, the FORCE GENERATED option is allowed only if you first issue an ALTER TABLE statement with the DROP VERSIONING clause (SQLSTATE 428FH).

PRUNE

This option can be specified for staging tables only. Specifies that the content of the staging table is to be pruned, and that the staging table is to be set to an inconsistent state. If any table in the table-name list is not a staging table, an error is returned (SQLSTATE 428FH). If the INCREMENTAL check option is also specified, an error is returned (SQLSTATE 428FH).

FULL ACCESS

Specifies that the table is to become fully accessible after the SET INTEGRITY statement executes.

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.

exception-clause

FOR EXCEPTION

Specifies that any row that is in violation of a constraint being checked is to be moved to an exception table. Even if errors are detected, the table is taken out of set integrity pending state. A warning to indicate that one or more rows have been moved to the exception tables is returned (SQLSTATE 01603).

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.

When the table specified after the IN keyword is a system-period temporal table, the FOR EXCEPTION option is allowed only if you first issue an ALTER TABLE statement with the DROP VERSIONING clause (SQLSTATE 428FH).

IN table-name

Specifies the table from which rows that violate constraints are to be moved. There must be one exception table specified for each table being checked. This clause cannot be specified for a materialized query table or a staging table (SQLSTATE 428A7).

USE table-name

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

FULL ACCESS

If the FULL ACCESS option is specified as the only operation of the statement, the table is placed into the full access state without being rechecked for integrity violations. However, dependent immediate materialized query tables that have not been refreshed might require a full recomputation in subsequent REFRESH TABLE statements, and dependent immediate staging tables that have not had the delta portions of the table propagated to them might be changed to incomplete state. This option can only be specified for a table that is in the no data movement state or the no access state, but not in the set integrity pending state (SQLSTATE 428FH).

PRUNE

table-unchecked-options

integrity-optionsUsed to define the types of required integrity processing that are to be bypassed when the table is taken out of the set integrity pending state.

ALL: The table will be immediately taken out of set integrity pending state without any of its required integrity processing being performed.
FOREIGN KEY: Required foreign key constraints checking will not be performed when the table is brought out of set integrity pending state.
CHECK: Required check constraints checking will not be performed when the table is brought out of set integrity pending state.
MATERIALIZED QUERY: Required refreshing of a materialized query table will not be performed when the table is brought out of set integrity pending state.
GENERATED COLUMN: Required generated column constraints checking will not be performed when the table is brought out of set integrity pending state.
STAGING: Required propagation of data to a staging table will not be performed when the table is brought out of set integrity pending state.

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.

FULL ACCESS

Specifies that the tables are to become fully accessible after the SET INTEGRITY statement executes.

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

IMMEDIATE UNCHECKED

Specifies one of the following:

The table is to be brought out of set integrity pending state immediately without any required integrity processing.
The table is to have one or more types of required integrity processing bypassed when the table is brought out of set integrity pending state by a subsequent SET INTEGRITY statement using the IMMEDIATE CHECKED option.

Consider the data integrity implications of this option before using it. See the Notes section.

Notes

The following restrictions apply to the SMP parallelization of set integrity checking:
- SMP parallelization of set integrity checking is not enabled by default. The DB2_EXTENDED_OPTIMIZATION registry variable with the PRLSI option must be set in order to enable it.
- The CURRENT DEGREE special register should be used to ensure an appropriate degree of parallelism.
- SMP parallelization is not supported in MPP environments.
- SMP parallelization is not supported for set integrity after attach.
- SMP parallelization is not supported when any of the following are involved:
  - Materialized query tables or staging tables
  - Identity or generated columns
- Parallelization will be limited when there are multiple tables referenced or self-references involved. The constraint and referential checking will be parallelized for one table at the most. The checks for the table with the most expensive constraint checking will be the one which may be parallelized. However, if a table has multiple self-references, some or all of its constraint checking may not be parallelized.
- Exception handling will not be parallelized.
Effects on tables in one of the restricted set integrity-related states:
- Use of INSERT, UPDATE, or DELETE is disallowed on a table that is in read access state or in no access state. Furthermore, any statement that requires this type of modification to a table that is in such a state will be rejected. For example, deletion of a row in a parent table that cascades to a dependent table that is in the no access state is not allowed.
- Use of SELECT is disallowed on a table that is in the no access state. Furthermore, any statement that requires read access to a table that is in the no access state will be rejected.
- New constraints added to a table are normally enforced immediately. However, if the table is in set integrity pending state, the checking of any new constraints is deferred until the table is taken out of set integrity pending state. If the table is in set integrity pending state, addition of a new constraint places the table into set integrity pending no access state, because validity of data is at risk.
- The CREATE INDEX statement cannot reference any table that is in read access state or in no access state. Similarly, an ALTER TABLE statement to add a primary key or a unique constraint cannot reference any table that is in read access state or in no access state.
- The import utility is not allowed to operate on a table that is in read access state or in no access state.
- The export utility is not allowed to operate on a table that is in no access state, but is allowed to operate on a table that is in read access state. If a table is in read access state, the export utility will only export the data that is in the non-appended portion.
- Operations (like REORG, REDISTRIBUTE, update distribution key, update multidimensional clustering key, update range clustering key, update table partitioning key, and so on) that might involve data movement within a table are not allowed on a table that is in any of the following states: read access, no access, or no data movement.
- The load, backup, restore, update statistics, runstats, reorgchk, list history, and rollforward utilities are allowed on a table that is in any of the following states: full access, read access, no access, or no data movement.
- The ALTER TABLE, COMMENT, DROP TABLE, CREATE ALIAS, CREATE TRIGGER, CREATE VIEW, GRANT, REVOKE, and SET INTEGRITY statements can reference a table that is in any of the following states: full access, read access, no access, or no data movement. However, they might cause the table to be put into no access state.
- Packages, views, and any other objects that depend on a table that is in no access state will return an error when the table is accessed at run time. Packages that depend on a table that is in read access state will return an error when an insert, update, or delete operation is attempted on the table at run time.
- The ALL or GENERATED COLUMN option cannot be specified with the IMMEDIATE UNCHECKED option if the table's database partitioning key, table-partitioning key, multidimensional clustering key, or range-clustering key references a generated column whose expression was altered through an ALTER TABLE statement.
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.
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. If there is data in the table that does not meet the integrity specifications that were defined for the table, and the IMMEDIATE UNCHECKED option is used, incorrect query results might be returned.
  The fact that the table was taken out of the set integrity pending state without performing the required integrity processing will be recorded in the catalog (the respective byte 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 unchanged until either:
  - The table is put back into set integrity pending state (by referencing the table in a SET INTEGRITY statement with the OFF option), at which time U values in the CONST_CHECKED column are changed to 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.
  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).
  If an underlying table's integrity has been checked using the IMMEDIATE UNCHECKED clause, the U values in the CONST_CHECKED column of the underlying table will be propagated to the corresponding CONST_CHECKED column of:
  - Dependent immediate materialized query tables
  - Dependent deferred materialized query tables
  - Dependent staging tables
  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.
- If a child table and its parent table are checked in the same SET INTEGRITY statement with the IMMEDIATE CHECKED option, and the parent table requires full checking of its constraints, the child table will have its foreign key constraints checked, independently of whether or not the child table has a U value in the CONST_CHECKED column for foreign key constraints.
If the table is data partitioned and there are nonpartitioned indexes (except the XML column path index) to maintain, IMMEDIATE UNCHECKED behavior when a single target table is specified is the same as IMMEDIATE CHECKED behavior with the ALLOW WRITE ACCESS option: all integrity processing is performed and any resulting errors are returned. If the statement references more than one target table, an error is returned (SQLSTATE 428FH).
After appending data using LOAD INSERT or ALTER TABLE ATTACH, the SET INTEGRITY statement with the IMMEDIATE CHECKED option checks the table for constraints violations. The system determines whether incremental processing on the table is possible. If so, only the appended portion is checked for integrity violations. If not, the system checks the whole table for integrity violations.
Consider the statement:
```
   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:
- New constraints have been added to T while it is in set integrity pending state
- When a LOAD REPLACE operation against T, it parents, or its underlying tables has taken place
- When the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check on T, its parents, or its underlying tables
- The cascading effect of full processing, when any parent of T (or underlying table, if T is a materialized query table or a staging table) has been checked for integrity non-incrementally
- If the table space containing the table or its parent (or underlying table of a materialized query table or a staging table) has been rolled forward to a point in time, and the table and its parent (or underlying table if the table is a materialized query table or a staging table) reside in different table spaces
- T is an MQT, and a LOAD REPLACE or LOAD INSERT operation directly into T has taken place after the last refresh
Incremental processing will be used whenever the situation allows it, because it is more efficient. The INCREMENTAL option is not needed in most cases. It is needed, however, to ensure that integrity checks are indeed processed incrementally. If the system detects that full processing is needed to ensure data integrity, an error is returned (SQLSTATE 55019).
If the conditions for full processing described in the previous bullet are not satisfied, the system will attempt to check only the appended portion for integrity, or perform an incremental refresh (if it is a materialized query table) when the user does not specify the NOT INCREMENTAL option for the statement SET INTEGRITY FOR T IMMEDIATE CHECKED.
If an error occurs during integrity processing, all the effects of the processing (including deleting from the original and inserting into the exception tables) will be rolled back.
If a SET INTEGRITY statement issued with the FORCE GENERATED option fails because of a lack of log space, increase available active log space and reissue the SET INTEGRITY statement. Alternatively, use the SET INTEGRITY statement with the GENERATED COLUMN and IMMEDIATE UNCHECKED options to bypass generated column checking for the table. Then, issue a SET INTEGRITY statement with the IMMEDIATE CHECKED option and without the FORCE GENERATED option to check the table for other integrity violations (if applicable) and to bring it out of set integrity pending state. After the table is out of the set integrity pending state, the generated columns can be updated to their default (generated) values by assigning them to the keyword DEFAULT in an UPDATE statement. This is accomplished by using either multiple searched update statements based on ranges (each followed by a commit), or a cursor-based approach using intermittent commits. A with hold cursor should be used if locks are to be retained after intermittent commits using the cursor-based approach.
A table that was put into set integrity pending state using the CASCADE DEFERRED option of the SET INTEGRITY statement or the LOAD command, or through the ALTER TABLE statement with the ATTACH clause, and that is checked for integrity violations using the IMMEDIATE CHECKED option of the SET INTEGRITY statement, will have its descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables put in set integrity pending state, as required:
- If the entire table is checked for integrity violations, its descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables will be put in set integrity pending state.
- If the table is checked for integrity violations incrementally, its descendent immediate materialized query tables and staging tables will be put in set integrity pending state, and its descendent foreign key tables will remain in their original states.
- If the table requires no checking at all, its descendent immediate materialized query tables, descendent staging tables, and descendent foreign key tables will remain in their original states.
A table that was put in set integrity pending state using the CASCADE DEFERRED option (of the SET INTEGRITY statement or the LOAD command), and that is brought out of set integrity pending state using the IMMEDIATE UNCHECKED option of the SET INTEGRITY statement, will have its descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables put in set integrity pending state, as required:
- If the table has been loaded using the REPLACE mode, its descendent foreign key tables, descendent immediate materialized query tables, and descendent immediate staging tables will be put in set integrity pending state.
- If the table has been loaded using the INSERT mode, its descendent immediate materialized query tables and staging tables will be put in set integrity pending state, and its descendent foreign key tables will remain in their original states.
- If the table has not been loaded, its descendent immediate materialized query tables, descendent staging tables, and its descendent foreign key tables will remain in their original states.
SET INTEGRITY is usually a long running statement. In light of this, to reduce the risk of a rollback of the entire statement because of a lock timeout, you can issue the SET CURRENT LOCK TIMEOUT statement with the WAIT option before executing the SET INTEGRITY statement, and then reset the special register to its previous value after the transaction commits. Note, however, that the CURRENT LOCK TIMEOUT special register only impacts a specific set of lock types.
If you use the ALLOW QUERY OPTIMIZATION USING REFRESH DEFERRED TABLES WITH REFRESH AGE ANY option, ensure that the maintenance order is correct for REFRESH DEFERRED materialized query tables. For example, consider two materialized query tables, MQT1 and MQT2, whose materialized queries share the same underlying tables. The materialized query for MQT2 can be calculated using MQT1, instead of the underlying tables. If separate statements are used to maintain these two materialized query tables, and MQT2 is maintained first, the system might choose to use the contents of MQT1, which has not yet been maintained, to maintain MQT2. In this case, MQT1 would contain current data, but MQT2 could still contain stale data, even though both were maintained at almost the same time. The correct maintenance order, if two SET INTEGRITY statements are used instead of one, is to maintain MQT1 first.
When using the SET INTEGRITY statement to perform integrity processing on a base table that has been loaded or attached, it is recommended that you process its dependent REFRESH IMMEDIATE materialized query tables and its PROPAGATE IMMEDIATE staging tables in the same SET INTEGRITY statement to avoid putting these dependent tables in set integrity pending no access state at the end of SET INTEGRITY processing. Note that for base tables that have a large number of dependent REFRESH IMMEDIATE materialized query tables and PROPAGATE IMMEDIATE staging tables, memory constraints might make it impossible to process all of the dependents in the same statement as the base table.
If the FORCE GENERATED or the GENERATE IDENTITY option is specified, and the column that is generated is part of a unique index, the SET INTEGRITY statement returns an error (SQLSTATE 23505) and rolls back if it detects duplicate keys in the unique index. This error is returned even if there is an exception table for the table being processed.
This scenario can occur under the following circumstances:
- The SET INTEGRITY statement runs after a LOAD command against the table, and the GENERATEDOVERRIDE or the IDENTITYOVERRIDE file type modifier is specified during the load operation. To prevent this scenario, it is recommended that you use the GENERATEDIGNORE or the GENERATEDMISSING file type modifier instead of GENERATEDOVERRIDE, and that you use the IDENTITYIGNORE or the IDENTITYMISSING modifier instead of IDENTITYOVERRIDE. Using the recommended modifiers will prevent the need for any generated by expression column or identity column processing during SET INTEGRITY statement execution.
- The SET INTEGRITY statement is run after an ALTER TABLE statement that alters the expression of a generated by expression column.
To bring a table out of the set integrity pending state after encountering such a scenario:
- Do not use the FORCE GENERATED or the GENERATE IDENTITY option to regenerate the column values. Instead, use the IMMEDIATE CHECKED option in conjunction with the FOR EXCEPTION option to move any rows that violate the generated column expression to an exception table. Then, re-insert the rows into the table from the exception table, which will generate the correct expression and perform unique key checking. This prevents having to reprocess the entire table, because only those rows that violated the generated column expression will need to be processed again.
- If the table being processed has attached partitions, detach those partitions before performing the actions that are described in the previous bullet. Then, re-attach the partitions and execute a SET INTEGRITY statement to process integrity on the attached partitions separately.
If a protected table is specified for the SET INTEGRITY statement along with an exception table, all of the following table criteria must be met; otherwise, an error is returned (SQLSTATE 428A5):
- The tables must be protected by the same security policy.
- If a column in the protected table has data type DB2SECURITYLABEL, the corresponding column in the exception table must also have data type DB2SECURITYLABEL.
- If a column in the protected table is protected by a security label, the corresponding column in the exception table must also be protected by the same security label.
Rows that violate the integrity being checked in a system-period temporal table cannot be moved to an exception table. If the violating rows must be moved to an exception table, the table must be altered to drop versioning before issuing the SET INTEGRITY statement with the FOR EXCEPTION clause.
Syntax alternatives: The following are supported for compatibility with previous versions of Db2 and with other database products. These alternatives are non-standard and should not be used.
- SET CONSTRAINTS can be specified in place of SET INTEGRITY
- SUMMARY can be specified in place of MATERIALIZED QUERY

Examples

Example 1: The following is an example of a query that provides information about the set integrity pending state and the set integrity-related access restriction states of tables. SUBSTR is used to extract individual bytes of the CONST_CHECKED column of SYSCAT.TABLES. The first byte represents foreign key constraints; the second byte represents check constraints; the fifth byte represents materialized query table integrity; the sixth byte represents generated column constraints; the seventh byte represents staging table integrity; and the eighth byte represents data partitioning constraints. STATUS gives the set integrity pending state, and ACCESS_MODE gives the set integrity-related access restriction state.
```
   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
```
Example 2: Put the PARENT table in set integrity pending no access state, and immediately cascade the set integrity pending state to its descendants.
```
   SET INTEGRITY FOR PARENT OFF
     NO ACCESS CASCADE IMMEDIATE
```
Example 3: Put the PARENT table in set integrity pending read access state without immediately cascading the set integrity pending state to its descendants.
```
   SET INTEGRITY FOR PARENT OFF
     READ ACCESS CASCADE DEFERRED
```
Example 4: Check integrity for a table named FACT_TABLE. If there are no integrity violations detected, the table is brought out of set integrity pending state. If any integrity violations are detected, the entire statement is rolled back, and the table remains in set integrity pending state.
```
   SET INTEGRITY FOR FACT_TABLE IMMEDIATE CHECKED
```
Example 5: Check integrity for the SALES and PRODUCTS tables, and move the rows that violate integrity into exception tables named SALES_EXCEPTIONS and PRODUCTS_EXCEPTIONS. Both the SALES and PRODUCTS tables are brought out of set integrity pending state, whether or not there are any integrity violations.
```
   SET INTEGRITY FOR SALES, PRODUCTS IMMEDIATE CHECKED
     FOR EXCEPTION IN SALES USE SALES_EXCEPTIONS,
     IN PRODUCTS USE PRODUCTS_EXCEPTIONS
```
Example 6: Enable FOREIGN KEY constraint checking in the MANAGER table, and CHECK constraint checking in the EMPLOYEE table, to be bypassed with the IMMEDIATE UNCHECKED option.
```
   SET INTEGRITY FOR MANAGER FOREIGN KEY,
     EMPLOYEE CHECK IMMEDIATE UNCHECKED
```
Example 7: Add a check constraint and a foreign key to the EMP_ACT table, using two ALTER TABLE statements. The SET INTEGRITY statement with the OFF option is used to put the table in set integrity pending state, so that the constraints are not checked immediately upon execution of the two ALTER TABLE statements. The single SET INTEGRITY statement with the IMMEDIATE CHECKED option is used to check both of the added constraints during a single pass through the table.
```
   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
```

Example 8: Update generated columns with the correct values.

   SET INTEGRITY FOR SALES IMMEDIATE CHECKED
     FORCE GENERATED

Example 9: Append (using LOAD INSERT) from different sources into an underlying table (SALES) of a REFRESH IMMEDIATE materialized query table (SALES_SUMMARY). Check SALES incrementally for data integrity, and refresh SALES_SUMMARY incrementally. In this scenario, integrity checking for SALES and refreshing of SALES_SUMMARY are incremental, because the system chooses incremental processing. The ALLOW READ ACCESS option is used on the SALES table to allow concurrent reads of existing data while integrity checking of the loaded portion of the table is taking place.
```
   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;
```
Example 10: Attach a new partition to a data partitioned table named SALES. Incrementally check for constraints violations in the attached data of the SALES table and incrementally refresh the dependent SALES_SUMMARY table. The ALLOW WRITE ACCESS option is used on both tables to allow concurrent updates while integrity checking is taking place.
```
   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;
```

Example 11: Detach a partition from a data partitioned table named SALES. Incrementally refresh the dependent SALES_SUMMARY table.

   ALTER TABLE SALES
     DETACH PARTITION 2000_PART INTO ARCHIVE_TABLE;
   SET INTEGRITY FOR SALES_SUMMARY
     IMMEDIATE CHECKED;

Example 12: Bring a new user-maintained materialized query table out of set integrity pending state.

   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

Example 13: Attach a new partition to a data partitioned table named SALES. Assume that this table has no nonpartitioned user indexes. Assume also that data integrity checking, including range validation and other constraints checking, has already been done (through application logic that is independent of the data server). Optimize the data roll-in process by using the SET INTEGRITY ... ALL IMMEDIATE UNCHECKED statement to skip range and constraints violation checking.
```
   ALTER TABLE SALES
     ATTACH PARTITION STARTING (300) ENDING (400)
     FROM SOURCE_TABLE;
   SET INTEGRITY FOR SALES ALL IMMEDIATE UNCHECKED;
```
The SALES table is brought out of SET INTEGRITY pending state, and the new data is available for applications to use immediately.
Example 14: Setting the DB2_EXTENDED_OPTIMIZATION registry variable with the PRLSI INCR option is done by running:
```
db2set DB2_EXTENDED_OPTIMIZATION='PRLSI INCR'
```

SET INTEGRITY statement

Invocation

Authorization

Syntax

Description

Notes

Examples