13.18.4 Data manipulation and integrity

When a referenced or referencing table is updated, added to, or deleted by a data manipulation SQL statement (excluding the PURGE TABLE statement), HiRDB performs checking during execution to guarantee data integrity. However, if the operations described in Tables 13-20 and 13-21 are executed, data integrity may no longer be guaranteed. If you specify USE in the pd_check_pending operand and perform these operations, the referencing table is placed in check pending status.

Table 13-20 Operations on referenced tables that nullify the guarantee of data integrity and the conditions under which loss of data integrity occurs

Operation on table or RDAREACondition for loss of data integrity
Database load utility (pdload)Data load of creation mode (-d option)The loaded primary key column does not contain a value that is the same as a value in a foreign key column of the referencing table.
Database reorganization utility (pdrorg)Reload (-k reld)The reloaded primary key column does not contain a value that is the same as a value in a foreign key column of the referencing table.
Reorganization (-k rorg)UOC was used to delete a row that contains a value that is the same as a value in a foreign key column of the referencing table.
Database structure modification utility (pdmod)Reinitialization of RDAREA (initialize rdarea)The referencing table is stored in an RDAREA that is different from the re-initialized RDAREA.
Reflection processing of updatable online re-organization (pdorend command)You perform any of operations (1) to (4) below on the replica RDAREA and original RDAREA in the current database during operation of updatable online reorganization# on tables having a referential relationship (data mismatch occurs after reflection processing):
(1) When you perform operations in the following order:
1. Insert data into the referencing table in the replica RDAREA.
2. From the referenced table in the original RDAREA, delete a row that has a value that is the same as one of the foreign key values inserted in step 1.
(2) When you perform operations in the following order:
1. Update foreign key data in the referencing table in the replica RDAREA.
2. From the referenced table in the original RDAREA, delete a row that has a value that is the same as one of the foreign key values updated in step 1.
(3) When you perform operations in the following order:
1. Delete data from the referenced table in the replica RDAREA.
2. In the referencing table in the original RDAREA, insert a row that has a value that is the same as one of the primary key values deleted in step 1.
(4) When you perform operations in the following order:
1. Update data in the referenced table in the replica RDAREA.
2. In the referencing table in the original RDAREA, insert a row that has a value that is the same as one of the primary key values that existed before it was updated in step 1.
PURGE TABLE statementData exists in the referencing table.
Modification of table partition storage conditions by the ALTER TABLEAs a result of partitioning or integration of RDAREAs, a row that contains a value that is the same as a value in the foreign key column of the referencing table is not included.
#
For details about using updatable online reorganization, see the manual HiRDB Staticizer Option Version 7 Description and User's Guide.

Table 13-21 Operations on referencing tables that nullify the guarantee of data integrity and the conditions under which loss of data integrity occurs

Operation on table or RDAREACondition for loss of data integrity
Database load utility (pdload)Data loadThe loaded foreign key column does not contain a value that is the same as a value in the primary key column of the referenced table.
Database reorganization utility (pdrorg)Reload (-k reld)The reloaded foreign key column does not contain a value that is the same as a value in the primary key column of the referenced table.
Reflection processing of updatable online re-organization
(pdorend command)
You perform any of operations (1) to (5) on the replica RDAREA and original RDAREA in the current database during operation of updatable online reorganization# on tables having a referential relationship (data mismatch occurs after reflection processing):
(1) When you perform operations in the following order:
1. Insert data into the referencing table in the replica RDAREA.
2. From the referenced table in the original RDAREA, delete a row that has a value that is the same as one of the foreign key values inserted in step 1.
(2) When you perform operations in the following order:
1. Update foreign key data in the referencing table in the replica RDAREA.
2. From the referenced table in the original RDAREA, delete a row that has a value that is the same as one of the foreign key values updated in step 1.
(3) When you perform operations in the following order:
1. Delete data from the referenced table in the replica RDAREA.
2. In the referencing table in the original RDAREA, insert a row that has a value that is the same as one of the primary key values deleted in step 1.
(4) When you perform operations in the following order:
1. Update data in the referenced table in the replica RDAREA.
2. In the referencing table in the original RDAREA, insert a row that has a value that is the same as one of the primary key values that existed before it was updated in step 1.
(5) When you perform the following operation:
Use the database load utility (pdload) to perform operation on the referencing table in the replica RDAREA, which causes loss of data integrity.
#
For details about using updatable online reorganization, see the manual HiRDB Staticizer Option Version 7 Description and User's Guide.
Organization of this subsection
(1) When the target table is a partitioned table
(2) Other conditions under which loss of data integrity may occur

(1) When the target table is a partitioned table

If the target table is a partitioned table and the table contains mismatched data, execution of a utility may move the RDAREA in which the mismatched data is stored. For example, assume there is mismatched data in RDAREA 1 for a table that is partitioned and stored in RDAREAs 1, 2 and 3. Executing a utility could cause the mismatched data to be moved to RDAREA 3. Table 13-22 lists conditions that cause moving of mismatched data between RDAREAs.

Table 13-22 Conditions that cause moving of mismatched data in a table between RDAREAs when the target table is a partitioned table

Operation on table or RDAREAConditions that cause moving of mismatched data in a table between RDAREAs
Database reorganization utility (pdrorg)Reorganization (-k rorg)You perform the following steps in the order listed below on a table partitioned using flexible hash partitioning or a matrix-partitioned table whose second dimension partitioning column is partitioned using flexible hash partitioning:
  1. Perform data load for each RDAREA.
  2. In HiRDB/Single Server, execute reorganization for each table.#
    In HiRDB/Parallel Server, specify the -g option to execute reorganization for each table.#
Rebalancing utility (pdrbal)You add an RDAREA for a table that has mismatched data to execute the rebalancing utility (pdrbal).#
#
You cannot execute the utility when you specify USE in the pd_check_pending operand if the target table is in check pending status.

(2) Other conditions under which loss of data integrity may occur

When all of the following conditions are met, data mismatch may occur; therefore, you need to check data integrity. For details about how to check data integrity, see 13.18.5 Procedure for checking table integrity. These conditions are the same regardless of whether the referential constraint action is RESTRICT or CASCADE.

When you manipulate referenced tables and referencing tables, make sure that all the above conditions are not true at the same time. You can guarantee data integrity by locking the target table with the LOCK statement's shared mode or lock mode. Note that there are some adverse effects on concurrent execution efficiency.