DB2 FOR LUW / DB2FORLUW / A50 - 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 v10.5 customers with low cardinality estimates,      * 
* mixed index applied local and join predicates.               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Customers experiencing this issue should upgrade to DB2      * 
* Cancun Release 10.5.0.4 or higher.                           * 
****************************************************************

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

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
 

DB2 Cancun Release 10.5.0.4 and higher contains this fix. 
After upgrading, customer should see higher cardinality 
estimates, when the appropriate Column Group Statistics have 
been gathered using runstats.

keiner bekannt / siehe Local-Fix

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

16.04.2014
10.11.2014
10.11.2014