DB2 FOR LUW / DB2FORLUW / A50 - 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 DB2 version 10.5.0.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 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

The problem is first fixed in DB2 version 10.5.0.4.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

14.04.2014
08.09.2014
08.04.2015