DB2 FOR LUW / DB2FORLUW / 970 - 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 V97 FP7 and earlier                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V97 Fp8                                       * 
****************************************************************

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 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First Fixed in DB2 V97 Fp8

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC91738 
follow-up : 

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

02.11.2012
23.04.2013
23.04.2013

9.7.FP8