DB2 FOR LUW / DB2FORLUW / 970 - DB2

Query abends DB2 instance with StackTrace in trap file starting 
with 
pp__17sqlnq_qunqncchild 
sqlnq_unify_cstar 
sqlnr_gbelim_action 
 
One sample query is 
WITH gbsubq1 AS (SELECT c11, c12 FROM t1 
                 GROUP BY c11, c12), 
     gbsubq2 AS (SELECT DISTINCT c11, 
                        COUNT (*) AS count1, 
                        COUNT(CASE WHEN c12 = 20 THEN 1 ELSE 0 
END) AS count2 
                        FROM gbsubq1 
                        GROUP BY c11) 
SELECT count1, count2 
FROM   gbsubq1, gbsubq2 
where gbsubq1.c11 = gbsubq2.c11; 
 
No query will get into this problem unless it meets all the 
following conditions: 
1. Query main block has an equivalent join predicate whose join 
keys are from two different group by sub-queries, i.e. gbsubq1 
and gbsubq2 in the example. 
2. gbsubq2 references gbsubq1. Also, the two join keys are 
derived from the common base table column.  In this case, that 
predicate is "gbsubq1.c11 = gbsubq2.c11", and gbsubq1.c11 and 
gbsubq2.c11 are both derived from t1.c11. 
3. gbsubq2 SELECT list contains more than one COUNT aggregates. 
One ore more COUNT aggregate has CASE expression operand. The 
main query block references to at least two gbsubq2's COUNT 
aggregates. 
4. The SELECT of gbsubq2 has DISTINCT.

JOIN BETWEEN TWO GROUPBY SUBQUERY MAY ABEND DB2 INSTANCE

1. Get rid of "DISTINCT" from gbsubq2 in the sql statement. 
2. Replace "COUNT(CASE WHEN c12 = 20 THEN 1 ELSE 0 END)" with 
"COUNT(*)" in the sql statement. 
The two workaround won't change query result.

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

First Fixed on DB2 V9.7 Fixpak 1.

not known / see Local fix

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

22.10.2009
17.12.2009
17.12.2009

9.7.