DB2 FOR LUW / DB2FORLUW / 970 - DB2

Queries may experience poor performance when the query contains 
a join between: 
1) a sub-select comprised of a VALUES or UNNEST table function 
and a GROUP BY clause 
2) a view, common-table-expression or nested-table-expression 
containing a UNION or UNION ALL operation 
 
The GROUP BY sub-select is not pushed below the UNION ALL to 
provide filtering on each arm of the UNION. 
 
The following examples show this query pattern: 
 
1) A UNION ALL view joined with a VALUES table function GROUP BY 
 
CREATE VIEW employees as ( 
                SELECT * FROM employees_CA WHERE country = 'CA' 
                 UNION ALL 
                SELECT * FROM employees_US where country = 'US' 
       ); 
 
SELECT empid, lastname, deptno 
  FROM ( SELECT deptid 
           FROM table( values ?,?,?,?,?,?,?,?,?,?) as 
tmp(deptid) 
           GROUP BY deptid 
       ) as d, employees as e 
 WHERE e.deptno = d.deptid 
 
2) A UNION ALL common-table-expression joined with an UNNEST 
table-function GROUP BY 
 
CREATE TYPE intArray as INTEGER ARRAY[100]; 
CREATE VARIABLE deptid_arr intArray; 
 
WITH employees as ( 
                SELECT * FROM employees_CA WHERE country = 'CA' 
                 UNION ALL 
                SELECT * FROM employees_US where country = 'US' 
       ) 
SELECT empid, lastname, deptno 
  FROM ( SELECT deptid 
           FROM table( UNNESTT(deptid_arr) ) as tmp(deptid) 
           GROUP BY deptid 
       ) as d, employees as e 
 WHERE e.deptno = d.deptid

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* .                                                            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Apply version 9.7 Fix Pack 5 on the database server.         * 
****************************************************************

Rewrite the query to manually push the join to VALUES or UNNEST 
GROUP BY into each arm of the UNION ALL. For example 
 
WITH d(deptid) as ( SELECT deptid 
                      FROM table( UNNESTT(deptid_arr) ) as 
tmp(deptid) 
                      GROUP BY deptid 
                  ) 
SELECT empid, lastname, deptno 
  FROM employees_CA as e, d 
 WHERE country  = 'CA' 
   AND E.DEPTNO = D.DEPTID 
 UNION ALL 
SELECT empid, lastname, deptno 
  FROM employees_US as e, d 
 where country  = 'US' 
   AND E.DEPTNO = D.DEPTID;

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
 

Problem was first fixed in Version 9.7 Fix Pack 5

not known / see Local fix

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

03.10.2011
19.12.2011
19.12.2011

9.7.FP5