Chapter 3. Referential integrity 53
3.8.2 Check pending
A referential constraint is placed in check pending status if the DBMS determines that
mismatches may exist between the parent and foreign keys. The check pending status only
applies to referential constraints in the
There are several operations that can cause a check pending condition:
Adding referential constraints to existing files with invalid data
Abnormal system failures
Apply/remove journal changes
When a referential constraint relationship has been marked as check pending, the associated
parent and dependent files can be opened, but the system imposes some restrictions on the
I/O operations to those files:
Only read and insert operations are allowed on the parent file.
No I/O operations are allowed on the dependent file.
The system imposes these restrictions to ensure that applications and users are not
accessing and changing records that are possibly inconsistent and, therefore, violating
To move a constraint relationship out of check pending, you must use disable (CHGPFCST)
to disable the constraint that allows any I/O operations to be performed on the parent and
dependent file. You can then correct your parent and foreign key values so that they again
meet referential integrity. Once the data corrections are completed, you can enable the
constraint that causes DB2 UDB for iSeries to process and verify that every non-null foreign
key value is valid. If this verification finds mismatches, the relationship is again marked as
check pending and the process repeats itself.
The check pending status of a file can be determined with the Work with Physical File
Constraints (WRKPFCST) command (refer to Figure 3-20 on page 57) and the Display
Physical File Description (DSPFD) command (refer to Figure 3-23 on page 62).
3.8.3 Constraint commands
The commands provided to manage referential integrity constraints are:
Change Physical File Constraint (CHGPFCST)
Display Check Pending Constraint (DSPCPCST)
Work with Physical File Constraints (WRKPFCST)
Edit Check Pending Constraint (EDTCPCST)
Remove Physical File Constraint (RMVPFCST)
The Change Physical File Constraint (CHGPFCST) command provides a way to:
Enable a referential constraint:
Enable causes the system to verify the data integrity of the specified constraint (for
example, every non-null foreign key value has a matching parent key). If the verification is
successful, the referential constraint is enforced by DB2 UDB for iSeries. Remember that
this enable process may not be a short-running operation when the associated files
contain a large number of records.