LI75693: A RANGE JOIN PREDICATE JOINED TO A UNION ALL VIEW WITH A GROUP B Y CLAUSE MAY PRODUCE A SUB-OPTIMAL PLAN.

DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• 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
  ;
  .
  

Local fix

Problem summary

• apar LI75693
  fixed >= v91 fpk10
  

Problem conclusion

• 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  ;
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  LI75692

• APAR is sysrouted TO one or more of the following:

Modules/Macros

• ENG_SQNR
  

Fix information

• Fixed component name

  DB2 UDE ESE LIN

• Fixed component ID

  5765F4104

Applicable component levels

• R910 PSN

     UP