The integrity check facility checks referential constraints or check constraints for integrity errors. If no row violates any constraints, the facility releases the check pending status.
You can execute the integrity check facility in units of a table or a constraint, as explained below.
The facility checks all of the referential and check constraints defined for the table that have been placed in check pending status. You specify the -t option to perform integrity checking on a table.
Integrity checking by table is applied in the following cases:
In this case the facility checks only one constraint at a time for integrity errors, regardless of the check pending status. You specify the -c option to perform integrity checking by constraint.
Integrity checking by constraint is applied in the following case:
To perform integrity checking for referential constraints, you use the SQL statement shown below for each constraint to be checked.
SELECT DISTINCT "foreign-key-component-column-1", "foreign-key-component-column-2", ..., "foreign-key-component-column-n" |
The table below shows the relationship between the execution result (SQLCODE) of the SQL statement shown in (a) and whether integrity has been maintained.
Table 13-5 Relationship between SQL's execution result and whether or not integrity has been maintained (referential constraint)
SQLCODE (number of selection rows) | Whether or not integrity has been maintained |
---|---|
100 (0) | Yes |
0 (1 or greater) | No |
Other | Execution error |
After execution of the SQL statement described in (a), the key values resulting in constraint violations are output to the process results file. The key values resulting in constraint violations are output after duplicates have been eliminated. For details about the output format for the key values, see 13.5(2)(a) Processing results of integrity checking on referential constraints.
If the number of row data items resulting in constraint violations exceeds the maximum output count for violation key values, the facility terminates integrity checking on that constraint. You use the -w option to change the maximum number of violation key values that can be output.
To perform integrity checking for check constraints, you use the SQL statement shown below for each constraint to be checked.
SELECT DISTINCT# "check-constraint-target-column-1", "check-constraint-target-column-2", ..., "check-constraint-target-column-n" |
The table below shows the relationship between the execution result (SQLCODE) of the SQL statement shown in (a) and whether integrity has been maintained.
Table 13-6 Relationship between SQL's execution result and whether or not integrity has been maintained (check constraint)
SQLCODE (number of selection rows) | Whether or not integrity has been maintained |
---|---|
100 (0) | Yes |
0 (1 or more) | No |
Other | Execution error |
After execution of the SQL statement described in (a), the key values resulting in constraint violations are output to the process results file. If the data types of the columns specified in the search conditions do not include BLOB or BINARY, the key values resulting in constraint violations are output after duplicates have been eliminated. If the columns include any that are of the BLOB or BINARY type, duplicates of key values resulting in constraint violations are not eliminated. For details about the output format for the key values, see 13.5(2)(b) Processing results of integrity checking on check constraints.
If the number of row data items resulting in constraint violations exceeds the maximum output count for violation key values, the facility terminates integrity checking on that constraint. You use the -w option to change the maximum number of violation key values that can be output.
pdconstck sets a table, constraint, or RDAREA in check pending status or non-check pending status on the basis of the results of integrity checking for each constraint. The change in the check pending status for an RDAREA depends on the unit of execution of pdconstck.
This subsection describes the change in check pending status for a constraint, table, and RDAREA. A change in check pending status for a table, constraint, or RDAREA occurs separately for check constraints and referential constraints.
Table 13-7 Change in the check pending status for a constraint
Result of integrity checking on a constraint | Constraint check pending status (setting) |
---|---|
Integrity is maintained | Non-check pending status (NULL value) |
Integrity is not maintained | Check pending status ('C') |
Table 13-8 Change in the check pending status for a table
Constraint check pending status | Table check pending status (setting) |
---|---|
All constraints are in non-check pending status | Non-check pending status (NULL value) |
At least one constraint is in check pending status (even if the others are in non-check pending status) | Check pending status ('C') |
Table 13-9 Change in the check pending status for an RDAREA
Result of integrity checking | RDAREA check pending status (setting) |
---|---|
Integrity is maintained for all constraints | Non-check pending status (NULL value) |
Integrity is not maintained for at least one constraint (even if it is maintained for the others) | Check pending status ('C') |
When integrity checking is performed by table, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking for each constraint.
Figure 13-1 Example of integrity checking by table shows an example of executing integrity checking by table on a table for which referential constraints have been defined. Table 13-10 Changes in the check pending status by table (example) shows the changes in the check pending status in this example.
Figure 13-1 Example of integrity checking by table
Table 13-10 Changes in the check pending status by table (example)
Result of integrity checking | Check pending status | |||||
---|---|---|---|---|---|---|
Integrity of REF1 | Integrity of REF2 | Table check pending status | Constraint check pending status | Table storage RDAREA check pending status | ||
REF1 | REF2 | USR1 | USR2 | |||
Maintained | Maintained | N | N | N | N | N |
Lost | P | N | P | P | P | |
Lost | Maintained | P | P | N | P | P |
Lost | P | P | P | P | P |
When integrity checking is performed by constraint, the check pending status of the table, the specified constraint, or the RDAREA changes based on the results of integrity checking on the specified constraint. The following describes the changes in the check pending status.
The constraint's check pending status changes based on the results of integrity checking on the specified constraint.
The table below shows the changes in the check pending status when integrity checking by constraint is performed on constraint CHK1 based on the example shown in Figure 13-1 Example of integrity checking by table.
Table 13-11 Changes in the check pending status by constraint (example)
Result of integrity checking | Constraint check pending status | Check pending status | ||||
---|---|---|---|---|---|---|
Integrity of CHK1 | CHK2 | Table check pending status | Constraint check pending status | RDAREA check pending status | ||
CHK1 | CHK2 | U1 | U2 | |||
Maintained | Non-check pending status | N | N | -- | N | N |
Maintained | Check pending status | P | N | -- | P | P |
Lost | Non-check pending status | P | P | -- | P | P |
Lost | Check pending status | P | P | -- | P | P |
Table 13-12 Example of order in which integrity checking by table is performed
Constraint for table | Order of integrity checking | ||||
---|---|---|---|---|---|
Referential constraint (foreign key) | Check constraint | Referential constraint (foreign key) | Check constraint | ||
F1 | F2 | C1 | C2 | ||
Maintained | Maintained | 1. | 2. | 3. | 4. |
Lost | 1. | 2. | -- | -- | |
Lost | Maintained | -- | -- | 1. | 2. |
Lost | -- | -- | -- | -- |
Table 13-13 Conditions for performing integrity checking and whether or not integrity checking is executed
Check pending status | Whether or not integrity checking is executed | Remarks | ||
---|---|---|---|---|
Table check pending status | Constraint check pending status | Table storage RDAREA check pending status#1 | ||
Check pending status ('C') | Check pending status ('C') | Check pending status | Yes | pdconstck determines that the status is normal (check pending status). |
Non-check pending status | Yes | pdconstck checks integrity because the data dictionary table is in check pending status although the RDAREA is in non-check pending status. | ||
Non-check pending status (NULL value)#2 | Check pending status | No | pdconstck determines that the status is normal (other constraints are in check pending status). | |
Non-check pending status | Yes | pdconstck checks integrity because the data dictionary table is in check pending status although the RDAREA is in non-check pending status. | ||
Non-check pending status (NULL value) | Check pending status ('C') | Check pending status | No | pdconstck terminates with an error because the data dictionary table is invalid. |
Non-check pending status | No | |||
Non-check pending status (NULL value)#2 | Check pending status | Yes | pdconstck checks integrity because the RDAREA is in check pending status. | |
Non-check pending status | No | pdconstck determines that the status is normal (non-check pending status). |
Table 13-14 Check pending status based on the table information in each table storage RDAREA
Condition | Table information in table storage RDAREA |
---|---|
All storage RDAREAs are in check pending status | Check pending status |
Some storage RDAREAs are in check pending status and some are in non-check pending status | Check pending status |
All storage RDAREAs are in non-check pending status | Non-check pending status |
Table 13-15 pdconstck processing when the data dictionary table is in check pending status
Check pending status | pdconstck processing | |
---|---|---|
Table check pending status | Constraint check pending status | |
Check pending status | Some constraints were placed in check pending status | Resumes processing |
All constraints are in non-check pending status | Terminates with an error | |
Non-check pending status | Some constraints were placed in check pending status | Terminates with an error |
All constraints are in non-check pending status | Resumes processing |