DB2 CHECK DATA Utility, Here’re Top Features

Utilities in DB2 checks data consistency while loading data into Tables. CHECKDATA utility has two top functionalities. It checks for referential integrity and Data validation constraints. Features of CHECKDATA Utility

2 Top Features

1. The CHECK DATA option checks the violation of the referential integrity rules between two tables.
2. It also checks whether data values conform to the data validation constraints applied to the table space that is specified with the utility command.

Errors You will get

• For example, if two tables EMP and DEPT are joined with the data field dept_no, the utility will check whether the values in both tables correspond with each other. If the utility detects any error, it will report the error and possibly resolve it.

• The utility can be run for a single or multiple table space or for a specific partition of a table space. If the utility encounters any violation, it deletes the invalid rows of data or copies them to an exception table.

• The table space being checked is placed in the CHECK-pending status. After reporting and solving the errors, the CHECK-pending status is reset. The CHECK DATA option runs after a conditional restart or when the base tables do not correspond to each other.

When you need this utility

1. When a table is loaded without the ENFORCE CONSTRAINT option. This option of the LOAD utility implements the constraints that were defined when the table was created.
2. When a check constraint is added in an existing table and it violates the existing data.
3. When the CHECK DATA utility detects any violation of the data integrity rules, while deleting rows of erroneous data during its execution. You can run the utility again to fix the problem.
4. When you need to perform partial recovery of the table spaces that are referentially joined.
5. When the reliability of data that binds the table spaces referentially needs to be checked.
6. When the CHECK-pending status of the table space is to be removed for future access and use of data.

//SYSIN DD *
 CHECK DATA TABLESPACE DSN9D41A.DSN9S41D
 FOR EXCEPTION IN DSN9410.DESIG
 USE DSN9410.DESIG_EXCPTN
 SCOPE ALL DELETE YES
//*

The above CHECK DATA statement works on the DSN9D41A.DSN9S41D table space.

• The utility checks that the DSN9410.DESIG dataset meets all the referential constraints. If a violation is encountered, the utility deletes all improper rows and puts them into the exception table DSN9410.DESIG_EXCPTN.
• Note : The CHECK DATA utility run parallel to the DIAGNOSE, MERGECOPY, MODIFY, REPORT, and STOSPACE utilities.