DB2 FOR LUW / DB2FORLUW / 970 - DB2

Db2 may not push down a range join to a Union All view into the 
tables of the Union All view.This would prevent Db2 to produce 
a sub-optimal plan. 
. 
The following conditions must be met in order to hit this proble 
m: 
. 
1)  There is a Group By clause on the table and the Union 
All view of the range join predicate. 
2)  Any column from the table in the range join predicate 
derives from an expression that is not a column. 
. 
For example: 
. 
create table br1(tranid integer, trandate date, amount decimal); 
create table br2(tranid integer, trandate date, amount decimal); 
create view ua as (select * from br1 union all select * from 
br2); 
. 
create table datedim(durid integer, startdate date, enddate 
date); 
. 
create view vdatedim(durid, startdate, enddate) 
as 
(select  durid, 
           case when month(startdate) = 3 then startdate + 1 day 
else startdate end startdate, 
            enddate 
 from     datedim 
); 
. 
select ua.trandate, count(tranid), sum(amount) 
from    ua, vdatedim 
where  ua.trandate between vdatedim.startdate and 
vdatedim.enddate 
group by ua.trandate 
; 
.

Db2 may not push down a range join to a Union All view into the 
 tables of the Union All view.This would prevent Db2 to produce 
 a sub-optimal plan. 
 . 
 The following conditions must be met in order to hit this 
proble 
 m: 
 . 
 1)  There is a Group By clause on the table and the Union 
 All view of the range join predicate. 
 2)  Any column from the table in the range join predicate 
 derives from an expression that is not a column. 
 . 
 For example: 
 . 
 create table br1(tranid integer, trandate date, amount 
decimal); 
 create table br2(tranid integer, trandate date, amount 
decimal); 
 create view ua as (select * from br1 union all select * from 
 br2); 
 . 
 create table datedim(durid integer, startdate date, enddate 
 date); 
 . 
 create view vdatedim(durid, startdate, enddate) 
 as 
 (select  durid, 
            case when month(startdate) = 3 then startdate + 1 
day 
 else startdate end startdate, 
             enddate 
  from     datedim 
 ); 
 . 
 select ua.trandate, count(tranid), sum(amount) 
 from    ua, vdatedim 
 where  ua.trandate between vdatedim.startdate and 
 vdatedim.enddate 
 group by ua.trandate 
; 
.

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

fixed >= v97 fpk4

not known / see Local fix

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

02.09.2010
29.04.2011
29.04.2011

9.7.FPk4