The constraint statement specifies settings for check pending status.
When a table for which check constraints or referential constraints have been defined is reorganized, the check pending status is not changed because constraint integrity can be guaranteed except when a row in the referenced table that is being referenced by a referencing table is deleted by a UOC. The check pending status is set only when a referenced table is reorganized by a UOC.
If reloading is performed, such as reloading using an old unload data file or reloading into a separate table, constraint integrity may no longer be guaranteed. Therefore, the applicable table and related referencing tables are placed in check pending status.
You can also specify a control statement that will prevent the check pending status from being changed (set). For details about check pending status, see the manual HiRDB Version 8 Installation and Design Guide.
constraint [pending=no] |
Specifies that when reorganization or reloading is to be performed on a referencing table or a table for which check constraints have been defined, the table's check pending status is not to be changed (set). For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.
Specifies that when reorganization or reloading is to be performed on a referenced table, the check pending status of a referencing table related to the referenced table is not to be changed (set). For details about the default value that is assumed when this operand is omitted, see (4) Range of check pending status settings.
Table 8-17 shows the pdrorg execution conditions and whether or not the check pending status is set.
Table 8-17 pdrorg execution conditions and whether or not the check pending status is set
Execution condition | Whether or not check pending status is set | ||||||
---|---|---|---|---|---|---|---|
pd_check_pending operand value in the system definition | Exe facility | Operand specification in the constraint statement | Use of UOC | Target table | |||
Ref'd table | Ref'ing table | Table with check constraints defined | |||||
USE | Reorg | pending=no | Yes | Yes | NC | NC | NC |
No | NC | NC | NC | ||||
No | Yes | NC | NC | NC | |||
No | NC | NC | NC | ||||
ref_pending=no | Yes | Yes | NC | NC | NC | ||
No | NC | NC | NC | ||||
No | Yes | S | NC | NC | |||
No | NC | NC | NC | ||||
Reload | pending=no | Yes | ![]() | NC | NC | NC | |
No | ![]() | NC | S | S | |||
ref_pending=no | Yes | ![]() | NC | NC | NC | ||
No | ![]() | S | NC | NC | |||
Other | ![]() | ![]() | ![]() | NC | NC | NC | |
Other | ![]() | ![]() | ![]() | ![]() | NC | NC | NC |
Table 8-18 shows the range of check pending status settings for check constraints during reorganization or reloading. Table 8-19 shows the range of check pending status settings for referential constraints.
Table 8-18 Range of check pending status settings for check constraints
Unit of execution | Range of check pending status settings for check constraints | ||
---|---|---|---|
Data dictionary table | Table information in RDAREA | ||
SQL_TABLES table CHECK_PEND2 column | SQL_CHECKS table CHECK_PEND2 column | Check constraint status | |
Table | C | C | T* |
RDAREA | C | C | R* |
Server | C | C | A* |
Table 8-19 Range of check pending status settings for referential constraints
Unit of execution | Target table | |||||
---|---|---|---|---|---|---|
Referenced table | Referencing table | |||||
Check pending status of the referencing table related to referenced table | Check pending status of referencing table | |||||
Data dictionary table | TIRDA | Data dictionary table | TIRDA | |||
SQLTC | SQLTC | REFCS | SQLTC | SQLTC | REFCS | |
Table | C | C | T* | C | C | T* |
RDAREA | C | C | T* | C | C | R* |
Schema | C | C | T* | C | C | T* |
Server | C | C | T* | C | C | A* |
This subsection describes whether or not check pending status can be set for tables T1 through T5 when the pending and ref_pending operands are specified in the constraint statement.
Table 8-20 shows the table's check pending status when reloading is performed on tables T1 through T5.
Table 8-20 Table's check pending status when reloading is performed
Table subject to reloading and whether or not there are constraint definitions | Operand specification in the constraint statement | Check pending status for table | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
Table name | Referential constraint | Check constraint | pend | ref_pend | T1 | T2 | T3 | T4 | T5 | |
Referenced table | Referencing table | |||||||||
T1 | Y | N | N | Omit'd | Omit'd | ![]() | P | ![]() | ![]() | ![]() |
Spec'd | ![]() | N | ![]() | ![]() | ![]() | |||||
Spec'd | Omit'd | ![]() | P | ![]() | ![]() | ![]() | ||||
Spec'd | ![]() | N | ![]() | ![]() | ![]() | |||||
T2 | Y | Y | N | Omit'd | Omit'd | ![]() | P | P | ![]() | ![]() |
Spec'd | ![]() | P | N | ![]() | ![]() | |||||
Spec'd | Omit'd | ![]() | N | P | ![]() | ![]() | ||||
Spec'd | ![]() | N | N | ![]() | ![]() | |||||
T3 | N | Y | N | Omit'd | Omit'd | ![]() | ![]() | P | ![]() | ![]() |
Spec'd | ![]() | ![]() | P | ![]() | ![]() | |||||
Spec'd | Omit'd | ![]() | ![]() | N | ![]() | ![]() | ||||
Spec'd | ![]() | ![]() | N | ![]() | ![]() | |||||
T4 | N | N | Y | Omit'd | Omit'd | ![]() | ![]() | ![]() | P | ![]() |
Spec'd | ![]() | ![]() | ![]() | P | ![]() | |||||
Spec'd | Omit'd | ![]() | ![]() | ![]() | N | ![]() | ||||
Spec'd | ![]() | ![]() | ![]() | N | ![]() | |||||
T5 | N | N | N | Omit'd | Omit'd | ![]() | ![]() | ![]() | ![]() | ![]() |
Spec'd | ![]() | ![]() | ![]() | ![]() | ![]() | |||||
Spec'd | Omit'd | ![]() | ![]() | ![]() | ![]() | ![]() | ||||
Spec'd | ![]() | ![]() | ![]() | ![]() | ![]() |
The target table subject to reorganization or reloading and the referencing tables related to that target table are placed in check pending status before reorganization or reloading is executed. If pdrorg rolls back due to an error after it has set the table's check pending status, the table is still placed in check pending status because setting of the check pending status has been completed (commit is completed).
If reorganization or reloading is performed for each schema, each target table is placed in check pending status before reorganization or reloading is executed. If the process that sets the check pending status on the table results in an error, pdrorg terminates with an error. Therefore, all tables processed before the occurrence of the error are placed in check pending status, but unprocessed tables are not placed in check pending status.