52 Advanced Functions and Administration on DB2 Universal Database for iSeries
Referential integrity messages in ILE COBOL programs
Status 9R handles all the notify messages previously listed for referential integrity
exceptions. Both escape messages are handled by the status code 90 set for the exceptions
in the CPF5200 range.
Referential integrity messages in ILE C programs
ILE/C maps these messages to the existing error number values.
SQLCODE values mapping referential integrity messages
The SQLCODE values are:
SQLCODE 530 handles the notify message CPF502D.
SQLCODE 531 indicates that you are updating a parent key with matching dependent
SQLCODE 532 indicates that you are deleting a parent key with matching dependent
See Appendix B, Referential integrity: Error handling example on page 337, for a coding
example about error handling when using referential integrity.
3.8 Referential integrity constraint management
This section describes:
Constraint states
Check pending condition
Commands you can use to manage referential integrity constraints
Save and restore
How to obtain information about referential integrity constraints
3.8.1 Constraint states
A referential constraint can be in one of the following states:
DEFINED state: The constraint definition exists at the file level, but the constraint is not
enforced. Defined constraints are purely by definition and not by function. The file
members do not have to exist for the constraint to be defined.
Defined/enabled: A constraint that remains enabled when it is moved to the established
Defined/disabled: A constraint that remains disabled when it is moved to the
established state
ESTABLISHED state: A referential constraint is established when the foreign key
attributes match those of the parent key and both files contain a member. The constraint
has now been formally created in the DBMS. In this state, the constraint can be:
Established/enabled: DB2 UDB for iSeries enforces referential integrity for this
Established/disabled: DB2 UDB for iSeries does not enforce referential integrity for a
constraint in this state. However, the access paths associated with the constraint are
still maintained.
See Database Programming, SC41-5701, for a complete discussion of constraint states.
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
established/enabled state.
There are several operations that can cause a check pending condition:
Adding referential constraints to existing files with invalid data
Abnormal system failures
Save/restore operations
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
referential integrity.
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)
CHGPFCST command
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.

Get Advanced Functions and Administration on DB2 Universal Database for iSeries now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.