A fix is available
Closed as program error.
This APAR will improve concurrency on tables which have referential integrity constraints. . When tables have defined referential constraints, for a delete statement involving a parent table, or an insert involving a child table, RI checking logic is compiled in. The RI checking needs to access the other table. Under certain circumstances, the scan on the other table cannot be done at the isolation level defined for the statement and must be upgraded internally to Repeatable Read in order to protect the integrity of the scan. The use of Repeatable Read on the other table scan results in additional locking that can cause concurrency problems. . Note that unlike primary keys, the declaration of a foreign key on a table does not result in the creation of an index internally. It is recommended that an index be created that matches the foreign key definition. If the optimizer chooses to use this index to scan the child table, then there is no isolation level upgrade. However, the optimizer is cost-based, and it chooses the access method that provides the cheapest plan. Locking and isolation levels are not factors in this decision. . If the optimizer chooses to use an index scan to access the child table, and the chosen index is defined on the foreign key columns plus additional trailing columns, then the isolation level must be upgraded to RR. This is required if the delete rule is CASCADE or SET NULL, to prevent child rows from being missed. . A similar situation can occur when inserting into a child table. If the optimizer chooses a different index than the primary key to do the RI scan on the parent, it could result in the isolation level for that scan being upgraded internally to Repeatable Read, for the same reasons. . This APAR includes two fixes. . 1. When the RI action rule is RESTRICT or NO ACTION, the isolation level does not need to be upgraded regardless of how the table is accessed. This will result in improved concurrency for these cases. . 2. A new optimizer heuristic is being provided to allow an end user to force the optimizer to choose the index that matches the RI key columns only, even if that index does not provide the cheapest access plan. This will result in reduced locking and better concurrency, but may also result in a less than optimal access plan. Caution should be used to determine if this behaviour is desirable for your specific scenario. The heuristic setting is applied instance-wide and will affect all applicable statements. . The heuristic is enabled by setting the registry variable DB2_REDUCED_OPTIMIZATION=JULIE and recycling the database. . Scenario: . Here are the table and index definitions - . CREATE TABLE TEST_TABLE (ROW1 INT NOT NULL, ROW2 INT NOT NULL, constraint pk1 primary key (ROW1), constraint fk1 foreign key (ROW2) references TEST_TABLE (ROW1) on delete cascade) . CREATE INDEX IX1 ON TEST_TABLE(ROW2,ROW1) . inserts the following rows into the table TEST_TABLE - . ROW1 ROW2 ---- ---- 0 0 101 0 102 0 103 0 104 0 11 101 12 102 13 103 14 104 501 11 502 12 503 13 504 14 . The application starts 4 threads, and each makes a connection to the database and does a DELETE from table TEST_TABLE i.e . delete from test_table where row1 = ? . For thread 1, it does a delete where row1 = 11. For thread 2, it does a delete where row1 = 12. For thread 3, it does a delete where row1 = 13. For thread 4, it does a delete where row1 = 14. . One of the thread would eventually get an exception with SQL0911.
For the DELETE case, define an index on the child table that matches the foreign key columns exactly and make sure the statistics are up to date. If the optimizer chooses this index for the child table scan, the isolation level upgrade can be avoided. This may not work when the statistics and cost estimation do not favour this index.
Users Affected: Only applicable if you are doing concurrent DELETE on the table with Referential Integrity (RI) defined on primary key with "on delete cascade". Problem Description: If this APAR is not applied, it is possible to cause problem as described in the Error Description. Problem Summary: See Error Description.
First fixed in DB2 UDB Version 8, FixPak 15
Reported component name
DB2 UDB ESE WIN
Reported component ID
Last modified date
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
JR25588 JR25591 JR25592
Fixed component name
DB2 UDB ESE WIN
Fixed component ID
Applicable component levels
06 October 2021