DB2 FOR LUW / DB2FORLUW / A10 - DB2

Incorrect results might be returned when an index jump scan is 
used in referential integrity checking, and when another 
concurrent transaction moves a child row during the referential 
integrity scan of the child table by updating the index key of 
the child table. 
 
For example, assume we have a child table c (c1, c2, c3), where 
(c1, c2) is foreign key with delete cascade. And we have an 
index on (c1, c3, c2). 
 
The index has the following key values 
('b', 'a', 'a'), ('b', 'c', 'a'), ('b', 'x', 'b'), 
('b', 'y', 'b'), ('b', 'z', 'b'). 
 
Transaction A executes a delete statement: 
delete from p where c1 = 'b' and c2 ='b' 
 
A referential integrity scan is needed on the child table to 
delete the rows that have the corresponding foreign key value. 
 
If we choose to use index jump scan using key predicates c1='b' 
and c2='b', when the scan is positioned at ('b', 'x', 'b'), 
transaction B updates ('b', 'z', 'b') to ('b', 'b', 'b'); that 
is, transaction B moves the row behind the current position of 
the referential integrity scan. 
 
After both transactions finish, the child table has a foreign 
key value that doesn't exist in the parent table. 
 
$ db2 "select * from p" 
 
C1 C2 
-- -- 
a  a 
b  a 
 
  2 record(s) selected. 
 
$ db2 "select * from c" 
 
C1 C2 C3 
-- -- -- 
b  a  a 
b  b  b 
b  a  c 
 
  3 record(s) selected. 
 
To confirm if you hit the problem, get a query plan of the SQL 
statement. If you see index jump scan is used in the referential 
integrity validation scan (Look for "Gap Info" to identify jump 
scan), e.g. 
 
                Gap Info:            Status 
                ---------            ------ 
                Index Column 1:      No Gap 
                Index Column 2:      Gap 
                Index Column 3:      No Gap 
 
and the following in the operator details of the index jump 
scan: 
 
                ROWLOCK : (Row Lock intent) 
                        ROWLOCK : ... WITHOUT END OF RANGE 
 
                TBISOLVL: (Table access Isolation Level) 
                        TBISOLVL: REPEATABLE READ 
 
Then you will probably hit the problem.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.1 Fix Pack 4                           * 
****************************************************************

You can avoid the problem by disabling jump scan using the 
JUMPSCAN option of the DB2_REDUCED_OPTIMIZATION variable: 
DB2_REDUCED_OPTIMIZATION="JUMPSCAN OFF". 
 
You can also use the REGISTRY optimization guideline element to 
set this at the application or statement level.

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in Version 10.1 Fix Pack 4

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

05.03.2014
18.07.2014
18.07.2014