Set Integrity

Go Up to Available object actions by DBMS

DB2 SET INTEGRITY statement support lets you perform tasks such as taking tables into and out of set integrity pending state, placing tables into full access state, and pruning the contents of one or more staging tables.

Note: This operation should not be performed without an in-depth understanding of the SET INTEGRITY statement. For online access to DB2 documentation, see Accessing Third Party Documentation.

Platform Availability

• DB2 LUW

To Open the Set Integrity Wizard

1. On the Navigator, expand a DB2 database, and click or expand the Table node.
2. Select the appropriate table(s), right-click, and choose Set Integrity from the menu. You can select more than one table by clicking CTRL + the tables you want.

   OR

Select the appropriate table, click Command on the toolbar, and then choose Set Integrity from the drop-down menu.

The table below lists all fields you can see in the Set Integrity Wizard. Depending on the options you choose, you may not see them all.

Required Field	Description
Tables
Tables	The list of one or more tables you selected appear.
Integrity Option
Integrity Option	OFF--When selected, tables have their foreign key and check constraints, and column generation disabled and so are put in a pending state. This also means materialized query or staging tables are not immediately refreshed and are put in a pending state. TO DATALINK RECONCILE PENDING--When selected, DATALINK integrity is disabled and tables are put in a check pending no access state. Dependent and descendant tables are not affected. IMMEDIATE CHECKED--This turns on a table’s integrity checking turned on. Any checking that was deferred is carried out. FULL ACCESS--Tables become fully accessible as soon as the SET INTEGRITY statement executes. PRUNE--This is appropriate only for staging tables. The content of the staging table is pruned and set to an inconsistent state. If any table in the table-name list is not a staging table, an error is returned. UNCHECKED--Allows you turn on some or all integrity checking but the table will not be checked for integrity violations. This can affect data integrity.
Table Readability/Cascade/Descendent Types
Specifies the readability of the table while it is in check pending state:	NO ACCESS--The table(s) are put in a check pending no access state so read/write access to the table is prohibited. READ ACCESS--The table(s) are put in a check pending read state. This allows read access to the non-appended portions of any tables.
Specifies whether to be immediately cascaded to all descendents	CASCADE IMMEDIATE--The check pending state for foreign key constraints is immediately extended to descendant foreign key constraints or to materialized query or staging tables. CASCADE DEFERRED--Only the selected tables are put in the check pending state. Descendant foreign key, materialized query, or staging tables remain unchanged.
Descendent Types	Materialized Query Tables--When selected, the check pending state is immediately cascaded to only descendant materialized query tables. Foreign Key Tables--When selected, the check pending state is cascaded immediately only to descendant foreign key tables. Staging Tables--When selected, the check pending state is cascaded immediately only to descendant staging tables.
Check Appended Portion?
Do you want to check on the appended portion (if any) of the table?	Default/Yes/No Force Generated--If you do not specify this generated column current values will be compared to the computed value of the expression as if an equality check constraint existed. If this is specified, generated columns are computed only for the appended portion. Prune--Possible only for staging tables. When you check this, the contents of the staging table are pruned and the staging table is set to an inconsistent state. Full Access--When selected, tables will become accessible after the SET INTEGRITY statement executes.
Specify Exception Tables
List of Base Tables	Any row that is in violation of a foreign key or check constraint is copied to the exception table you select.
Integrity Options: IMMEDIATE UNCHECKED
IMMEDIATE UNCHECKED options	Foreign Key--These constraints are turned on when the table is removed from check pending status. Check--Check constraints are turned on when the table is removed from check pending status. Datalink Reconcile Pending--DATALINK integrity constraints are turned on when the table is removed from check pending status. Materialized Query--Immediate refreshing is turned on for a materialized query table when it is removed from a check pending state. Generated Column--When the table is removed from check pending status, generated columns are turned on. Staging--Immediate propagation is turned on for a staging table.
Do you want tables to become fully accessible after the SET INTEGRITY statement executes?	Yes/No

3. When ready, click the Finish button to preview and submit the generated DDL. For more information, see Preview.