DB2 FOR LUW / DB2FORLUW / 970 - DB2

If a query contains a join for which a statistical view is also 
defined,  the optimizer will not use the statview's statistics 
to adjust cardinality estimates if the query contains a 
NODENUMBER predicate on one of the tables involved in the 
statistical view,  i.e. 
 
CREATE VIEW SV AS (SELECT T1.*, T2.* FROM T1, T2 WHERE 
T1.C1=T2-C1) 
 
Query: SELECT * FROM T1, T2, T3 WHERE T1.C1=T2.C1 AND 
T2.C2=T3.C2 AND NODENUMBER(T1.C3)=1 
 
  With this scenario,  an explain output may show the following 
message which indicates a failure to apply the statview in the 
extended diagnostics output: 
 
Diagnostic Details:     EXP0073W  The following MQT or 
statistical view was 
                        not eligible because one or more data 
filtering 
                        predicates from the query could not be 
matched with 
                        the MQT: "MRT     "."SV".

**************************************************************** 
* USERS AFFECTED:                                              * 
* Queries using nodenumber predicates may be affected          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 6                        * 
****************************************************************

DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 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 Version 9.7 Fix Pack 6

not known / see Local fix

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

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

15.12.2011
07.06.2012
07.06.2012

9.7.FP6