| DB2 weaves an intricate web of checks and balances to maintain the integrity of the data housed in its tables. DB2 ensures that image copies, recovers, and referential integrity checks are performed as needed, based on an application's job stream. For example, if data is loaded into a table with DB2 logging turned off, no further updates can be made to that table until an image copy is made or the table is reloaded with changes logged. If DB2 did not enforce this, valuable application data could be lost because of hardware or software failures. DB2 controls the integrity of its data through the use of exception states, also called pending flags . A table space is in a pending state when the check pending, copy pending, or recover pending flag is set for that table space. There are other table space exception states (as outlined in the previous chapter), but these are the big three. Why Pending States Occur A table space's check pending flag is set when -
A check constraint is added to a table and data within an existing row of that table violates the constraint. -
A table is altered to add a check constraint and the CURRENT RULES special register contains 'DB2' . -
A table space with a table or tables containing referential constraints is partially recovered (that is, RECOVER TORBA or RECOVER TOCOPY is run). -
The CHECK DATA utility is run for a table in the table space specifying DELETE NO and referential constraint or check constraint violations are encountered . -
The LOAD utility is run for a table in the table space specifying the ENFORCE NO option and either RI or check constraints exist for any table in the table space. -
A table in the table space is altered to add a new foreign key. -
Any table in a referential set is dropped. -
Any database or table space containing tables in a referential set is dropped. A table space's copy pending flag is set when -
The REORG utility is run for the table space specifying LOG NO or the LOAD utility is run for a table in the table space specifying LOG NO . -
A table space with a table or tables containing referential constraints is partially recovered (that is, RECOVER TORBA or RECOVER TOCOPY is run). -
The MODIFY utility is run deleting the last full image copy data set from the SYSIBM.SYSCOPY table. A table space's recover pending flag is set when An index's recover pending (or rebuild pending) flag is set when -
A table space with a table or tables containing referential constraints is partially recovered (that is, RECOVER TORBA or RECOVER TOCOPY is run). -
Abends occur in the REBUILD , RECOVER , REORG , or LOAD utility. -
The index was created specifying DEFER YES . How to Correct Pending States The check pending flag for the table space can be reset by -
Running the CHECK DATA utility for the tables in the table space specifying DELETE YES . -
Running the CHECK DATA utility for the tables in the table space specifying DELETE NO if no constraint violations are encountered. -
Running the LOAD utility specifying the ENFORCE CONSTRAINTS option. -
Altering tables in the table space to drop foreign keys and check constraints. -
Running the REPAIR utility specifying SET NOCHECKPEND for the table space or issuing the START command for the table space with the ACCESS(FORCE) parameter. Neither option corrects the problem flagged by the pending state; they merely reset the pending flag. The copy pending flag for the table space can be reset by -
Running the REORG utility with the LOG YES option or running the LOAD utility with both the REPLACE and LOG YES options. -
Running the COPY utility specifying both the SHRLEVEL REFERENCE and the FULL YES options. -
Running the REPAIR utility specifying SET NOCOPYPEND for the table space or issuing the START command for the table space with the ACCESS(FORCE) parameter. Neither option corrects the problem flagged by the pending state; they merely reset the pending flag. The recover pending flag for the table space can be reset by -
Running the LOAD utility with the REPLACE option. -
Running a full recovery for the table space. -
Running the REPAIR utility specifying SET NORCVRPEND for the table space or issuing the START command for the table space with the ACCESS(FORCE) parameter. Neither option corrects the problem flagged by the pending state; they merely reset the pending flag. The recover pending flag for the index can be reset by -
Running the REBUILD INDEX utility for the index. -
Running the RECOVER INDEX utility for the index. -
Running the REPAIR utility specifying SET NORCVRPEND for the index or issuing the START command for the index with the ACCESS(FORCE) parameter. Neither option corrects the problem flagged by the pending state; they merely reset the pending flag. | 