If data integrity between tables can no longer be guaranteed due to execution of an SQL statement or of a utility, HiRDB restricts data manipulation in the referencing table. The status in which data manipulation is restricted due to loss of guaranteed data integrity is called check pending status. To place a referencing table in check pending status for the purpose of restricting data manipulation, you must either specify USE in the pd_check_pending operand or do not specify (omit) the operand. You can use the integrity check utility (pdconstck) to clear the check pending status of a table. You can also use the integrity check utility to forcibly place a table into check pending status.
If you have specified NOUSE in the pd_check_pending operand, data manipulation is not restricted even when data integrity between tables cannot be guaranteed. In this case, if you execute an SQL statement or a utility that nullifies the guarantee of data integrity, you can use the integrity check facility to forcibly place the table into check pending status, and then check data integrity.
For details about operations that cause loss of guaranteed data integrity, see 13.18.4 Data manipulation and integrity. For details about how to check data integrity, see 13.18.5 Procedure for checking table integrity.
In addition to the integrity check utility, you can also use the following utilities, commands, and SQL statements to place a referencing table into check pending status or to clear a check pending status that has been applied to a referencing table.
For details about utilities and commands, see the manual HiRDB Version 8 Command Reference. For details about SQL, see the manual HiRDB Version 8 SQL Reference.
Check pending status is managed based on dictionary tables and on the table information of the RDAREAs in which the tables are stored. In dictionary tables, check pending status is managed for each table and constraint. In table information, check pending status is managed for each RDAREA if the table is a partitioned table, and for each table if the table is not a partitioned table.
Table 13-15 lists the storage locations of check pending status information and their contents.
Table 13-15 Storage locations of check pending status information and contents (referential constraint)
Storage location | Stored information | ||
---|---|---|---|
Dictionary table | SQL_TABLES table | CHECK_PEND column | Check pending status of referential constraint for each table |
SQL_REFERENTIAL_CONSTRAINTS table | CHECK_PEND column | Check pending status of referential constraint for each constraint | |
RDAREA table information | For non-partitioned table | Check pending status of referential constraint or check constraint for each table | |
For partitioned table | Check pending status of referential constraint or check constraint for each RDAREA |
Table 13-16 lists operations that are disabled for tables in check pending status. When a target table is accessed by a trigger action, restricted operations depend on the availability of SQL operations specified in the triggered SQL statement. If a target table is a view table, the restricted operations depend on the availability of operations on the base table that is the source of the view table.
Table 13-16 Availability of operations on tables in check pending status
Operation on check pending status tables | Availability | ||
---|---|---|---|
Data manipulation SQL | SELECT statement | Searches the target table | Y#1 |
Searches a list created from the target table | |||
INSERT statement | Inserts data into the target table | ||
UPDATE statement | Updates the target table | ||
DELETE statement | Deletes a row from the target table | ||
ASSIGN LIST statement | Creates a list from the target table | ||
Utility | Rebalancing utility (pdrbal) | N | |
Database reorganization utility (pdrorg) | Reorganizes | Y#2 |
In this example, tables have the following referential relationship; only tables T2 and T3 are in check pending status.
The following subsections explain operations restricted for each table when either table T2 or T3 or both tables are in check pending status.
Table 13-17 lists operations restricted for each table when only table T2 is in check pending status.
Table 13-17 Restricted operations when table T2 is in check pending status
Target table | Restricted operation | Contents |
---|---|---|
Table T1 | UPDATE (updates the target table) | Restrictions depend on the referential constraint action specification defined in table T2.
|
DELETE (deletes rows from the target table) | ||
Table T2 | SELECT statement (searches the target table or a list created from the target table) | These operations can be performed only when both of the following conditions are met:
|
INSERT statement (inserts data into the target table) | ||
UPDATE statement (updates the target table) | ||
DELETE statement (deletes rows from the target table) | ||
ASSIGN LIST statement (creates a list from the target table) | ||
Rebalancing utility (pdrbal) | This operation cannot be performed. | |
Reorganization by the database reorganization utility (pdrorg) | Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference. | |
Table T3 | There is no restricted operation. For INSERT and DELETE, referenced table T2 is referenced to perform data integrity checking. |
Table 13-18 lists operations restricted for each table when only T3 is in check pending status.
Table 13-18 Restricted operations when table T3 is in check pending status
Target table | Restricted operation | Contents |
---|---|---|
Table T1 | UPDATE (updates the target table) | When the referential constraint action defined for table T2 and T3 is CASCADE, these operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the values are the same. |
DELETE (deletes rows from the target table) | ||
Table T2 | UPDATE (updates the target table) | Restrictions depend on the referential constraint action specification defined for tables T2 and T3.
|
DELETE (deletes rows from the target table) | ||
Table T3 | SELECT statement (searches the target table or a list created from the target table) | These operations can be performed only when both of the following conditions are met:
|
INSERT statement (inserts data into the target table) | ||
UPDATE statement (updates the target table) | ||
DELETE statement (deletes rows from the target table) | ||
ASSIGN LIST statement (creates a list from the target table) | ||
Rebalancing utility (pdrbal) | The operation cannot be performed. | |
Reorganization by the database reorganization utility (pdrorg) | Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference. |
Table 13-19 lists operations restricted for each table when tables T2 and T3 are in check pending status.
Table 13-19 Restricted operations when tables T2 and T3 are in check pending status
Target table | Restricted operation | Contents |
---|---|---|
Table T1 | UPDATE (updates the target table) | If the referential constraint action defined for table T2 and T3 is CASCADE, these operations cannot be performed if the table information of the RDAREA that is the target of referential constraint action is in check pending status. However, update operations can be performed if the value are the same. These operations can be performed if the referential constraint action specification defined for tables T2 and T3 is RESTRICT. Referencing table T2 is referenced to perform data integrity checking. |
DELETE (deletes rows from the target table) | ||
Table T2 | SELECT statement (searches the target table or a list created from the target table) | These operations can be performed only when both of the following conditions are met:
|
INSERT statement (inserts data into the target table) | ||
UPDATE statement (updates the target table) | ||
DELETE statement (deletes rows from the target table) | ||
ASSIGN LIST statement (creates a list from the target table) | ||
Rebalancing utility (pdrbal) | This operation cannot be performed. | |
Reorganization by the database reorganization utility (pdrorg) | Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference. | |
Table T3 | SELECT statement (searches the target table or a list created from the target table) | These operations can be performed only when both of the following conditions are met:
|
INSERT statement (inserts data into the target table) | ||
UPDATE statement (updates the target table) | ||
DELETE statement (deletes rows from the target table) | ||
ASSIGN LIST statement (creates a list from the target table) | ||
Rebalancing utility (pdrbal) | This operation cannot be performed. | |
Reorganization by the database reorganization utility (pdrorg) | Reorganization may not be possible for a table partitioned using flexible hash partitioning. For details, see Database Reorganization Utility (pdrorg) in the manual HiRDB Version 8 Command Reference. |
Since the check pending status is managed for each RDAREA, if a partitioned table or the inner replica facility is used, and the table information in the RDAREA actually used is in check pending status, operation on the partitioned table or generation may be restricted. The following subsections explain these cases.
Figure 13-34 shows an example wherein part of an RDAREA that stores data in a partitioned table is in check pending status.
Figure 13-34 Data manipulation availability when managing check pending status for each RDAREA in a partitioned table
Figure 13-35 shows an example in which the inner replica facility is used, and, in one of the generations of an RDAREA, a part of it is in check pending status.
Figure 13-35 Data manipulation availability when using the inner replica facility and managing check pending status by RDAREA