DB2 FOR LUW / DB2FORLUW / 970 - DB2

When a UNION operator is part of a view or a common table 
expression (CTE) and the view or the CTE 
is referenced more than once in the query, local predicates may 
not be pushed down below the UNION 
if there are subqueries with correlation within the view or CTE. 
 
 
The following describes the problem from an SQL perspective and 
correlates the relevant parts to the 
5 conditions required to encounter this problem listed below: 
 
with CTE as 
(select c1,c2 from t1 
union all 
/* [1] */ 
select c1,c2 from t1_archive t2 
where not exists (select 1 from t1 where t1.c0=t2.c0)    /* [2] 
*/ 
select ... 
from   CTE parent 
where parent.c1 = all (select c1 from CTE subq             /* 
[3] */ 
                                 where subq.c2=parent.c2) 
/* [4] */ 
          parent.c2 = <const>) 
/* [5] */ 
 
 
 
[1] The CTE has a UNION operator. 
[2] The CTE contain a subquery (not exists) with a correlation. 
[3] The CTE is referenced twice in the query. 
[4] There is a correlation from one reference of the CTE to 
another reference of the CTE. 
[5] There is a local predicate referencing the same column from 
[4].

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 Fix Pack 8.                       * 
****************************************************************

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
 

Upgrade to DB2 version 9.7 Fix Pack 8.

not known / see Local fix

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

16.11.2012
15.04.2013
15.04.2013

9.7.FP8