set integrity not enforced in DB2 I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on the table and insert invalid rows into table arbitrarily ? if that is the case why we create the constraint at the first place ?

further more, there is options to turn on and off access. I am further confused, does that mean even before the integrity of table data being checked, we can allow the data to be read accessed ? is that same as saying we allow dirty read here?

+another question is, if that integrity check is put off, how to bring the table out of pending state ? does this always requires manual intervention?
According to IBM's documentation: "Typically, you need to manually perform integrity processing for a table in three situations: After loading data into a table; when altering a table by adding constraints on the table; and when altering a table to add a generated column."

That would be the why you need to do it. We use it right after we use the LOAD command as this bypasses referential integrity to load (for lack of a better word) a ton of records into tables. We end up needing to use SET INTEGRITY when we are done to have it check the integrity and verify our tables can be used.

In our case we use:

SET INTEGRITY &lt;schema&gt;.&lt;table&gt; ALLOW NO ACCESS IMMEDIATE CHECKED;


And yes, you do have to run the SET INTEGRITY command for each table that is in a pending state. (We use scripts to do it in our case to simplify things.)

For more information about the command see SET INTEGRITY for all available options.