DB2 FOR LUW / DB2FORLUW / 970 - DB2

The optimizer might choose a one-fetch group by alternative, 
requiring a less-optimal join order below the group by 
operation, for a SQL statement that includes the following: 
 
a. an aggregation function in the SELECT list with no grouping 
columns, or grouping columns that are constant values 
b. one or more joins in the query 
c. the aggregation is on a single table, T 
d. the only filtering on T is through one or more joins 
e. the table T is well clustered on the join columns reference 
in the filtering joins 
 
The following example SQL statement satisfies the above 
conditions since it includes a MAX aggregation function on T1, 
which is filtered through the join with T2 only, and the 
statement does not include and grouping columns: 
SELECT MAX(T1.A) 
FROM T1,T2 
WHERE T1.B=T2.B AND T2.A=1 
 
You can identify if a group by operation is a one-fetch by 
collecting an EXPLAIN of the query and searching for the 
ONEFETCH flag in the details of the group by operator: 
        ONEFETCH: (One Fetch flag) 
            TRUE 
 
In most cases, a one-fetch group by provides optimal 
performance, so its presence in the query execution plan is not 
an indicator that you are hitting this issue.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 10.                      * 
****************************************************************

You can use an optimization guideline to request a different 
query execution plan

First fixed in DB2 Version 9.7 Fix Pack 10.

not known / see Local fix

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

01.05.2014
17.11.2014
17.11.2014

9.7.FP10