How to check referential integrity (RI) when no Db2 RI exists between two tables
Article ID: 54639
Updated On:
Products
Issue/Introduction
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 may disrupt the existing application.
Environment
Release: R20
Component: PRI, PFC, PFL, RCX
Resolution
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 RI Manager for DB2 for z/OS (PRI). RI manager will store this User Defined RI within its own product tables. This User Defined RI
is not known to or recognized by Db2 or other non Database Management for Db2 for z/OS tools but it is recognized by some
products such as RC/Extract for Db2 for z/OS, Fast Check for Db2 for z/OS, Fast Load for Db2 for z/OS and RC/Update for Db2 for z/OS.
Read section View and Create Relationships in the RI Manager User Guide for an example of the method used to Define User Defined RI
where no RI or indexes presently exist.
Fast Check for DB2 for z/OS (PFC)
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 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.
//SYSIN DD *
CHECK DATA
TABLESPACE xxxxxxxx.xxxxxxxx
USERRI
CHKLEVEL 2
MSGLEVEL 0
SORTFLAG ALL
SORTSIZE 4M
SCOPE ALL
Fast Load for DB2 for z/OS (PFL)
If additional data must be added to the tables via a utility then 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 RI Manager ensuring that no referentially invalid data is loaded to the table.
Fast Check is called in the background to carry out this check of RI. A Fast Check for DB2 for z/OS license is required to
use ENFORCE CONSTRAINTS.
//SYSIN DD *
FASTLOAD
COPY-BUFFERS 10
SPACE-DEFN NO
DISPLAY-STATUS 10000
ESTIMATED-ROWS 2000000
INPUT-FORMAT UNLOAD
IO-BUFFERS 50
MAXTASKS 1
NONLEAF-PCTFREE LEAF
OUTPUT-CONTROL ALL
UNLOAD CONTINUE
RECLUSTER NO SORT-CLINDX
SORTSIZE 4M
ENFORCE CONSTRAINTS USER
SORTNUM 4
STOP-LIMIT 1
VSAM-BUFFERS 96
RESUME YES
INTO TABLE xxxxxxxx.xxxxxxxx
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 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 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. Fast Check may also be used to remove any records in error.
This is done with the DELETE keyword in Fast Check.
Additional Information
Feedback
