DB2 FOR LUW / DB2FORLUW / A10 - DB2

Under registry DB2_SELECTIVITY 'YES' setting, user can specify 
the selectivity of a predicate and optimizer will apply it 
instead of its estimated version as predicate filter factor. The 
"SELECTIVITY" keyword and its value are expected to be present 
at "Optimized Statement" as well as "Plan Details" section in 
db2exfmt output plan file. If they are missed, it indicates 
the user specified selectivity is lost during query compilation 
and optimizer doesn't make use of it. This type of problem can 
happen to query statement with mixed outer join and inner join. 
 
Assume the outer join predicate is R.x = N.x and inner join 
predicate is R.y = I.y. The problem requires all the following 
conditions to be satisfied: 
 
1. I.y is unique. 
2. The SELECTIVITY clause is appended after a simple local 
equality predicate on table R. That predicate should be an 
equality predicate between simple base column and a literal. It 
can't involve any expression.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users on DB2 V101 FP2 and earlier                        * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V101 FP2                                      * 
****************************************************************

Manually rewrite local equality predicate by replacing <literal> 
in "R.z = <literal>"  with an equivalent expression, e.g. max(?, 
cast(null as int)). 
 
Ex: 
 
original query predicate ---> Table1.Column1 = ? selectivity 
.000001 
 
rewritten query predicate ----> TTable1.Column1=max(?,0) 
selectivity .000001

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

First Fixed in DB2 V101 FP2

keiner bekannt / siehe Local-Fix

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

22.04.2013
07.10.2013
07.10.2013