DB2 FOR LUW / DB2FORLUW / A50 - DB2

The optimizer might compute an incorrect cardinality estimate in 
an access plan for a SQL statement on which a statistical view 
is eligible, if the following conditions are true: 
1) There is a statview ( sv1 ), whose SELECT list contains 
expression column. 
2) There is another statview ( sv2 ), which contains a predicate 
involving the same expression. 
 
For example, consider the following statistical views: 
create view sv1(x) as (select c1+5 from t1); 
create view sv2(y) as (select c1 from t1 where c2+1 < 2 and c1+5 
= 5); 
 
Please note the expression 'c1+5'. It is the output column of 
sv1 and it is also used in predicate for sv2. In this case, the 
optimizer might compute an incorrect cardinality estimate for 
the following SQL statement: 
select * from t1 where c1+5=5 and c2+1<2; 
 
The explain plan ( i.e. db2exfmt output ) of above SQL may show 
the following diagnostic messages: 
Diagnostic Details:     EXP0233I  The predicate "Q4.C1 + 5 = 5" 
contains 
                        an expression matching the statistical 
view column 
                        "SCHEMA1.SV1" 
 
Diagnostic Details:     EXP0147W  The following statistical view 
was used 
                        by the optimizer to estimate 
cardinalities: 
                        "SCHEMA1"."SV2".

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.5 Fix Pack 4                       * 
****************************************************************

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
 

Upgrade to DB2 Version 10.5 Fix Pack 4

not known / see Local fix

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

14.05.2014
27.10.2014
27.10.2014