DB2 FOR LUW / DB2FORLUW / 970 - DB2

DB2 has a component called Query ReWrite (QRW) which rewrites 
the query to improve query performance. 
One of the optimizations done is to find common expressions and 
tables and combine them to save on processing and I/O costs. 
Under rare scenarios, this can prevent some subsequent 
optimizations from making progress and cause performance 
degradation. 
 
eg. 
CREATE VIEW v1 AS SELECT t3.* FROM t3 INNER 
JOIN (SELECT c1 FROM t5 WHERE t5.c1=APPLICATION_ID() 
and c2 > 0) tmp ON tmp.c1 = t3.c1; 
 
ALTER VIEW v1 ENABLE QUERY OPTIMIZATION; 
 
with cte1 as ( select t1.* from t4, t1 left join (t2 join v1 on 
t2.c1 = v1.c1) on t1.c2 = t2.c2 and t1.c1 is null where t4.c1 = 
t1.c1), 
cte2 as ( select t1.* from t1 left join (t2 join v1 on t2.c1 = 
v1.c1) on t1.c2 = t2.c2 and t1.c1 is null) 
select o.cnt old_cnt, n.cnt new_cnt, o.cnt - n.cnt diff from 
(select count(1) cnt from cte1) o 
join 
(select count(1) cnt from cte2) n 
on 1=1;

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above.                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 9.                       * 
****************************************************************

db2set DB2COMPOPT=8716421 and restart instance

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 9.

not known / see Local fix

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

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

12.10.2012
17.12.2013
17.12.2013

9.7.FP9