How to check referential integrity (RI) when no DB2 RI exists between two tables?

Document ID : KB000054639
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

How to load and check the validity of data between two tables which do not have DB2 RI associated with them? Application maintained RI has been used. RI violations are suspected due to program errors but DB2 RI may not be created as this will disrupt the existing application.

Solution:

Some legacy application systems have been implemented without the use of DB2 Referential Integrity. Instead the application programs implement this within the programs themselves. Early in the development of DB2, DB2 RI was often not implemented by developers. This method relies on the quality of the application programs to ensure that proper logical referential integrity is maintained. Quite often errors can begin to creep into the data itself which must be manually identified and corrected in some manner. Also the loading and unloading of data to such tables must be carefully carried out to ensure that logical referential integrity is maintained.

In order to check the data integrity of two tables not related in DB2 it is necessary firstly to create some User Defined RI. User Defined RI is created with the use of CA RI Manager for DB2 for z/OS (PRI). CA RI manager will store this User Defined RI within it's own product tables. This User Defined RI is not known to or recognized by DB2 or other non CA tools but it is recognized by some CA Database Management Tools for DB2 for z/OS like CA RC/Extract, CA Fast Check, CA Fast Load and CA RC/Update.

Read section "Creating RI Relationships" in the CA RI Manager User Guide for an example of the method used to create User Defined RI where no RI or indexes presently exist.

CA Fast Check for DB2 for z/OS (PFC)
CA Fast Check has a parameter called "USERRI". This parameter causes the utility to check the data on the table in reference to the User Defined RI which has been created with CA RI Manager. It should be used in combination with "SCOPE ALL". Any existing records which are found to be in violation of the RI will be reported and may then be investigated. As the tablespace does not contain DB2 RI it will never be placed in CHECK PENDING status. PFC will check the tablespace regardless of a lack of CHECK PENDING as long as these two parms are used.

CA Fast Load for DB2 for z/OS (PFL)
If additional data must be added to the tables via a utility then CA Fast Load has a parameter called "ENFORCE CONSTRAINTS USER". This will lend additional support to the same user defined RI. This feature causes the utility to check the data that it is loading in reference to the User Defined RI which has been created with CA RI Manager ensuring that no referentially invalid data is loaded to the table. CA Fast Check is called in the background to carry out this check of RI.

Conclusion and business benefits
In this way the data in the tables may be checked and corrected without any impact on the existing application. In addition CA RI Manager contains functions which will allow this User Defined RI to be converted to DB2 RI including any indexes needed to support it. If the opportunity arises to carry out such an enhancement the user can be confident in the full knowledge that the RI in question has already been checked for logical validity by CA Fast Check.

When data errors of this sort occur the normal method used to deal with them is to write some in-house programs to read the data and check that the correct logical referential integrity exists and to report records in error. Having identified the records in error they are then manually corrected. The method described above avoids the need to write a "Validation Suite" to find the records in error and as it can remain in place indefinitely it will provide a permanent means to check the data. CA Fast Check may also be used to remove any records in error.

Documentation References
CA RI Manager for DB2 for z/OS User Guide
CA Fast Check for DB2 for z/OS User Guide
CA Fast Load for DB2 for z/OS User Guide