DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under rare scenarios, join predicates might not be pushed 
through a Union All if the following conditions are satisfied. 
 
1. The query contains 1 or more Union All. 
2. The Union All has a join to another table 
3. There are multiple subqueries present where 1 or more 
subqueries are complex. Subqueries are considered complex if 
they contain another subquery or have constructs like group by, 
outer join, Union All, etc. 
4. All the subqueries are either non-correlated or correlated to 
the same Union All. 
 
Ex. V1 is a view containing a basic UNION ALL : 
 
create view v1 as ( select * from tab1 where col1 = 0 union all 
select * from tab1 ) 
 
-- Query: 
select distinct v1.col3 
from v1, tab2 t2 
where v1.col1 = t2.col1 and 
   ( 
   exists ( select 1 from tab2 tt2 where tt2.col2 = v1.col2) 
   ) 
   or 
   ( 
   -- condition 3. Complex subquery 
   exists ( select 1 from tab2 tt2 where tt2.col2 = v1.col1 
              and not exists ( select 1 from tab2 tt3  where 
              tt3.col3 = v1.col2)) 
   )

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to version 9.7 fixpack 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
 

first fixed in version 9.7 fixpack 8

not known / see Local fix

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

21.09.2012
01.04.2013
01.04.2013

9.7.FP8