DB2 FOR LUW / DB2FORLUW / A50 - DB2

The DB2 query optimizer might choose suboptimal jumpscan index 
access plan for UPDATE/DELETE statements when the following 
conditions are true: 
 
1) The only way to apply the predicate as start/stop key is 
using jumpscan i.e. there is no such index available that has 
the predicate-column as a leading column. 
2) The 'gap' column of the index is almost unique. 
3) The predicate column is a foreign key column. 
 
For example, consider the following DDLs: 
   CREATE TABLE T1( C1 INTEGER NOT NULL , ... ); 
   ALTER TABLE T1 ADD PRIMARY KEY (C1); 
 
   CREATE TABLE T2( C2 INTEGER NOT NULL , C3 INTEGER , C1 
INTEGER , C4 INTEGER , ... ); 
   ALTER TABLE T2 ADD PRIMARY KEY (C2); 
   CREATE INDEX I2 ON T2 (C3 ASC, C1 ASC, C4 ASC); 
   ALTER TABLE T2  ADD CONSTRAINT "REF1" FOREIGN KEY (C1) 
REFERENCES T1 (C1) 
   ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY 
OPTIMIZATION; 
 
For the following DELETE statement, the optimizer might choose a 
jumpscan index access plan. 
   DELETE FROM T1 WHERE C1 = ? 
 
The db2exfmt output will show the following: 
        5) IXSCAN: (Index Scan) 
                ... 
                Arguments: 
                --------- 
                ... 
                JUMPSCAN: (Jump Scan Plan) 
                        TRUE 
                ... 
                Predicates: 
                ---------- 
                3) Start Key Predicate, 
                ... 
                        Predicate Text: 
                        -------------- 
                        (? = Q5.C1) 
 
                3) Stop Key Predicate, 
                ... 
                        Predicate Text: 
                        -------------- 
                        (? = Q5.C1) 
 
 
                Gap Info:            Status 
                ---------            ------ 
                Index Column 1:      Gap 
                Index Column 2:      No Gap 
 
Here, C3 is the gap column and it is almost unique.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 7.                      * 
****************************************************************

Any one of the following should help optimizer choose a good 
access plan: 
1) Create a new index with the predicate-column as leading 
column 
2) Disable optimizer rule that prunes good plan: 
db2set -im DB2_REDUCED_OPTIMIZATION=NO_JULIE 
3) Disable jumpscan itself: 
db2set -im DB2_REDUCED_OPTIMIZATION='JUMPSCAN OFF'

First fixed in DB2 Version 10.5 Fix Pack 7.

not known / see Local fix

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

29.07.2015
03.02.2016
03.02.2016