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.
When the inner replica facility is used to perform processing by table, the integrity check facility can be executed in the following units:
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. When the inner replica facility is used, integrity checking is performed on the original RDAREA that stores the table for which the constraint has been specified, as well as on all generations of its replica RDAREAs.
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. When the inner replica facility is used, integrity checking is performed on the applicable constraint in a generation.
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 (generation). 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. When the inner replica facility is used, integrity checking is performed on the applicable constraint in a generation.
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 (generation). 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 (and when the inner replica facility is used, for each generation 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
Inner replica facility | Result of integrity checking on a constraint# | Constraint check pending status (setting) |
---|---|---|
Not used | Integrity is maintained | Non-check pending status (NULL value) |
Integrity is not maintained | Check pending status ('C') | |
Used | Integrity is maintained in all generations | Non-check pending status (NULL value) |
Integrity is maintained in some of the generations | 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 |
If integrity checking is performed on all generations when the inner replica facility has been used, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking for each constraint (or each generation).
Figure 13-2 Example of integrity checking by all generations shows an example of performing integrity checking on all generations when the inner replica facility has been used to create one generation of replica RDAREAs containing a table for which check constraints have been defined. Table 13-11 Changes in the check pending status by all generations (example) shows the changes in the check pending status in this example.
Figure 13-2 Example of integrity checking by all generations
Table 13-11 Changes in the check pending status by all generations (example)
Result of integrity checking | Check pending status | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
Integrity of CHK1 | Integrity of CHK2 | Table check pend status | Constraint check pending status | RDAREA check pending status | ||||||
GEN 0 | GEN 1 | GEN 0 | GEN 1 | CHK1 | CHK2 | U1 | U2 | U1G1 | U2G1 | |
Mntd | Mntd | Mntd | Mntd | N | N | N | N | N | N | N |
Mntd | Mntd | Mntd | Lost | P | N | P | N | N | P | P |
Mntd | Mntd | Lost | Mntd | P | N | P | P | P | N | N |
Mntd | Mntd | Lost | Lost | P | N | P | P | P | P | P |
Mntd | Lost | Mntd | Mntd | P | P | N | N | N | P | P |
Mntd | Lost | Mntd | Lost | P | P | P | N | N | P | P |
Mntd | Lost | Lost | Mntd | P | P | P | P | P | P | P |
Mntd | Lost | Lost | Lost | P | P | P | P | P | P | P |
Lost | Mntd | Mntd | Mntd | P | P | N | P | P | N | N |
Lost | Mntd | Mntd | Lost | P | P | P | P | P | P | P |
Lost | Mntd | Lost | Mntd | P | P | P | P | P | N | N |
Lost | Mntd | Lost | Lost | P | P | P | P | P | P | P |
Lost | Lost | Mntd | Mntd | P | P | N | P | P | P | P |
Lost | Lost | Mntd | Lost | P | P | P | P | P | P | P |
Lost | Lost | Lost | Mntd | P | P | P | P | P | P | P |
Lost | Lost | Lost | Lost | P | P | P | P | P | P | P |
When the inner replica facility is used and integrity checking is performed by generation, the check pending status of the table, constraints, or RDAREAs changes based on the results of integrity checking on the specified generation for each constraint.
The table below shows the changes in the check pending status of a constraint and RDAREA based on the results of integrity checking by generation.
Table 13-12 Changes in the check pending status of a constraint and RDAREA based on the results of integrity checking by generation
Execution result | Constraint check pending status | RDAREA check pending status | ||
---|---|---|---|---|
Result of checking on the corresponding generation | Check pending status in other generations of the RDAREA | |||
Integrity is maintained in all constraints | All non-check pending status | All constraints are placed in non-check pending status. | The corresponding generation is placed in non-check pending status. | |
Other | No change | The corresponding generation is placed in non-check pending status. | ||
Other | Constraint whose integrity is maintained | -- | No change | The corresponding generation is placed in check pending status. |
Constraint whose integrity is lost | -- | Placed in check pending status. | The corresponding generation is placed in check pending status. |
The table below shows the changes in the check pending status when integrity checking by generation is performed on Generation 1 based on the example shown in Figure 13-2 Example of integrity checking by all generations.
Table 13-13 Changes in the check pending status by generation (example)
Result of integrity checking | RDAREA check pending status | Check pending status | |||||||
---|---|---|---|---|---|---|---|---|---|
Table check pending status | Constraint check pending status | RDAREA check pending status | |||||||
Integrity of CHK1 | Integrity of CHK2 | GEN 0 | CHK1 | CHK2 | U1G1 | U2G1 | U1 | U2 | |
Mntd | Mntd | Non-check pending status | N | N | N | N | N | (N) | (N) |
Mntd | Mntd | Check pending status | (P) | (P) | (P) | N | N | (P) | (P) |
Mntd | Lost | Non-check pending status | P | (P) | P | P | P | (N) | (N) |
Mntd | Lost | Check pending status | P | (P) | P | P | P | (P) | (P) |
Lost | Mntd | Non-check pending status | P | P | (P) | P | P | (N) | (N) |
Lost | Mntd | Check pending status | P | P | (P) | P | P | (P) | (P) |
Lost | Lost | Non-check pending status | P | P | P | P | P | (N) | (N) |
Lost | Lost | Check pending status | P | P | P | P | P | (P) | (P) |
When the inner replica facility is used and integrity checking is performed by the current RDAREA's generation, the check pending status of the table, constraints, or the specified generation of RDAREA changes based on the results of integrity checking on the specified generation for each constraint.
The changes in the check pending status when integrity checking is performed by the current RDAREA's generation are the same as in (c) Changes in the check pending status when integrity checking is performed by generation.
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.
When the inner replica facility is not used, 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-14 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-15 Changes in the check pending status by constraint when the inner replica facility is used (example)
Result of integrity checking | Other check pending status | Check pending status | |||||||
---|---|---|---|---|---|---|---|---|---|
Table check pending status | Constraint check pending status | RDAREA check pending status | |||||||
Integrity of CHK1 generation 0 | Integrity of CHK1 generation 1 | CHK2 | CHK1 | CHK2 | U1 | U2 | U1G1 | U2G1 | |
Maintained | Maintained | Non-check pending status | N | N | -- | N | N | N | N |
Maintained | Maintained | Check pending status | P | N | -- | P | P | P | P |
Maintained | Lost | Non-check pending status | P | P | -- | P | P | P | P |
Maintained | Lost | Check pending status | P | P | -- | P | P | P | P |
Lost | Maintained | Non-check pending status | P | P | -- | P | P | P | P |
Lost | Maintained | Check pending status | P | P | -- | P | P | P | P |
Lost | Lost | Non-check pending status | P | P | -- | P | P | P | P |
Lost | Lost | Check pending status | P | P | -- | P | P | P | P |
Table 13-16 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-17 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-18 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-19 Check pending status based on the table information in each table storage RDAREA in the generation
Condition | Table information in table storage RDAREA |
---|---|
All storage RDAREAs subject to processing in the generation are in check pending status | Check pending status |
Storage RDAREA subject to processing in the generation is in non-check pending status | Check pending status |
All storage RDAREAs subject to processing in the generation are in non-check pending status | Non-check pending status |
Table 13-20 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 |