DB2 FOR LUW / DB2FORLUW / A50 - DB2

The DB2 optimizer query rewrite component might not remove all 
possible redundant joins when a self join query is done within a 
view. 
 
This issue can be recreated as following: 
 
 
 
create table A (A int not null primary key, X int not null) 
organize by column; 
create table B (B int not null primary key, A int not null 
references A (A) not enforced enable query optimization) 
organize by column; 
create table F (B int not null references B(B) not enforced 
enable query optimization, C int not null, I int not null, Y int 
not null) organize by column; 
 
create or replace view BV as select B.*, A.X from B inner join A 
on B.A = A.A; 
create or replace view V as select F.*, BV.A, BV.X from F inner 
join BV on F.B = BV.B; 
 
and this query: 
 
explain plan for 
select SUM(Y) from V where C IN (select C from V where  I IN 
(1,2)) and I NOT IN (1,2); 
 
 
 
In the explain for the above select, the access plan should only 
touch table F, but it also joins to B. 
 
Original Statement: 
------------------ 
select 
  SUM(Y) 
from 
  V 
where 
  C IN 
  (select 
     C 
   from 
     V 
   where 
     I IN (1, 2) 
  ) and 
  I NOT IN (1, 2) 
 
 
Optimized Statement: 
------------------- 
SELECT 
  Q5.$C0 
FROM 
  (SELECT 
     SUM(Q4.Y) 
   FROM 
     (SELECT 
        DISTINCT Q2.Y, 
        Q1.A, 
        Q2.B, 
        Q2.$P-ROWID$ 
      FROM 
        DB2V1055.B AS Q1, 
        DB2V1055.F AS Q2, 
        DB2V1055.F AS Q3 
      WHERE 
        (Q2.B = Q1.B) AND 
        (Q2.C = Q3.C) AND 
        Q3.I IN (1, 2) AND 
        NOT(Q2.I IN (1, 2)) 
     ) AS Q4 
  ) AS Q5

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

First fixed in DB2 Version 10.5 Fix Pack 7.

not known / see Local fix

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

30.01.2015
21.01.2016
21.01.2016