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 12.19.4 Data manipulation and integrity. For details about how to check data integrity, see 12.19.5 Procedure for checking table integrity.
You can also decide whether to set a referencing table to check pending status or clear its check pending status using the following utilities, commands and SQL statements, in addition to the integrity check utility.
For details about utilities and commands, see the manual HiRDB Version 9 Command Reference. For details about SQL, see the manual HiRDB Version 9 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.
The following table lists and describes the storage locations of check pending status information items.
Table 12-16 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 unpartitioned 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 |
The table below lists operations that are no longer available for tables once they enter 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 12-17 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.
The following table lists operations that are restricted for particular tables when table T2 alone is in check pending status.
Table 12-18 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 9 Command Reference. | |
Table T3 | There is no restricted operation. For INSERT and DELETE, referenced table T2 is referenced to perform data integrity checking. |
The following table lists operations that are restricted for particular tables when table T3 alone is in check pending status.
Table 12-19 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 9 Command Reference. |
The following table lists operations that are restricted for particular tables when both table T2 and table T3 are in check pending status.
Table 12-20 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 9 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 9 Command Reference. |
Since the check pending status is managed for each RDAREA, if a partitioned table is used, and the table information in the RDAREA actually used is in check pending status, operation on the partitioned table may be restricted. The following subsections explain these cases.
The following figure shows an example of when some RDAREAs that store data in a partitioned table are in check pending status.
Figure 12-36 Data manipulation availability when managing check pending status for each RDAREA in a partitioned table