DB2 FOR LUW / DB2FORLUW / A10 - DB2

Optimizer can choose a wrong index due to incorrect index scan 
cost calculation. This code was introduced due to the jump scan 
feature introduced from DB2 v10.1 onwards hence this defect can 
be hit only in DB2 v10 releases. There are a few pre-conditions 
for this defect to be hit. They are: 
 
a)  the query includes a join between two tables on 2 or more 
columns that are statistically correlated 
b)  there is an index that can apply the join preds as start and 
stop keys 
c)  the query includes one or more local predicates on the join 
columns (it is more likely to be hit for equality local preds) 
 
Symptoms can be an upgrade is performed from v9.5, v9.7 to v10.1 
or v10.5 and overall performance is slow due to pre-dominance of 
above kind of queries running in the database. No obvious 
bottlenecks will be visible in the database and it'll show that 
its working but plan changes will happen which will make the 
optimizer choose a wrong index and make it run longer and read 
abnormal amount of rows. 
 
Due to the new code introduced for jump-scan, this caused a 
regression in the index-scan costing calculation.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 10.1 customers with low cardinality estimates, mixed * 
* index applied local and join predicates.                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 5                       * 
****************************************************************

Workaround 
============ 
If there are not many queries like this, 
a) collect a column group statistic on the join predicate 
columns not referenced by equality predicates; or 
b) apply an optimization profile to specify the desired index 
access

First fixed in DB2 version 10.1 Fix Pack 5

See Local Fix

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

04.11.2014
15.07.2015
15.07.2015