Advanced Functions and Administration on DB2 Universal Database for iSeries

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

records.

 SQLCODE 532 indicates that you are deleting a parent key with matching dependent

records.

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

state

–

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

constraint.

–

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.