home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC88478 Status: Closed

DB2 OPTIMIZER MIGHT NOT CHOOSE A ONE-FETCH GROUP BY WHEN CARDINALITY IS
UNDERESTIMATED

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
The optimizer will consider an optimization for an SQL statement 
containing a MIN or MAX aggregation without a group by clause, 
referred to as a one-fetch group by operation, if the stream is 
ordered on the aggregation source column. You can identify if 
the group by operation is optimized as a one-fetch by collecting 
an EXPLAIN of the statement and searching for the ONEFETCH 
argument in the group by operator details: 
 
                ONEFETCH: (One Fetch flag) 
                        TRUE 
 
If the input cardinality is significantly underestimated to 
value of 1 or less, then the optimizer might not choose to 
perform this optimization, and the ONEFETCH argument will not 
exist in the formatted EXPLAIN output.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V97 FP9                                       * 
****************************************************************
Local Fix:
In most cases, collecting up-to-date statistics will improve the 
cardinality estimate if the SQL statement contains predicates 
that qualify the latest data in the table and the statistics are 
stale.
available fix packs:
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

Solution
First fixed in DB2 V97 FP9
Workaround
In most cases, collecting up-to-date statistics will improve the 
cardinality estimate if the SQL statement contains predicates 
that qualify the latest data in the table and the statistics are 
stale.
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC96658 IC96826 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
21.11.2012
14.02.2014
14.02.2014
Problem solved at the following versions (IBM BugInfos)
9.7.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.9 FixList
9.7.0.9 FixList