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 IC80342 Status: Closed

COLUMN GROUP STATISTICS NOT ALWAYS APPLIED TO CYCLE OF 3 OR MORE JOINS

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
Column Group Statistics (CGS) might not be considered by the DB2 
optimizer when accounting for statistical correlation in a cycle 
of 3 or more tables due to inconclusive parent and child 
determination. For the optimizer to correct for statistical 
correlation using CGS, one table must have a column cardinality 
that is greater than or equal  to the other table for all 
columns involved in the CGS, and it must have a high2key/low2key 
range for each column that is inclusive of the other table. 
 
For non-cyclic joins, these conditions are not critical; 
however, when 3 or more tables are involved in a cycle of joins, 
if these conditions are not met for 1 pair of tables in the 
cycle, the optimizer might end up correcting for statistical 
correlation for some of the joins, and not others.  This can 
lead the optimizer to favour a join between the tables that have 
not had their cardinality corrected. 
 
For example, consider the query: 
 
select * from T1, T2, T3 where T1.C1 = T2.D1 and 
T1.C1 = T3.E3 and T2.D1 = T3.E1 and T1.C2 = T2.D2 and T1.C2 = 
T3.E3 and T2.D2 = T3.E3 
 
with statistics for the 3 tables and their columns as follows: 
 
Table.Column  Col Card  Hi2Key Lo2Key 
T1.C1              100              99      1 
T1.C2              10                  9      1 
T2.D1              50                74    26 
T2.D2                4                  7      4 
T3.E1                40                89    41 
T3.E3                  5                4      1 
 
In the example, T1 has a higher column cardinality and inclusive 
Hi2Key, Lo2Key when comparing T1.C1 = T2.D2 and T1.C1 = T3.E3, 
and when comparing T1.C2 = T2.D2 and T1.C2 = T2.E2. The problem 
is observed when comparing T2.D1 = T3.E1 and T2.D2 = T3.D3 where 
the column cardinalities and the {Hi2Key, Lo2Key} range are 
conflicting. Under these conditions, the optimizer will not 
account for statistical correlation for the T2/T3 join, leading 
to the optimizer potentially favouring this join. 
 
The fix is activated using the DB2 environment variable: 
DB2_CORRELATED_PREDICATES='FORCE_CGS'
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 for LUW users with complex joins                     * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Column Group Statistics (CGS) might not be considered by the * 
* DB2                                                          * 
* optimizer when accounting for statistical correlation in a   * 
* cycle                                                        * 
* of 3 or more tables due to inconclusive parent and child     * 
* determination. For the optimizer to correct for statistical  * 
* correlation using CGS, one table must have a column          * 
* cardinality                                                  * 
* that is greater than or equal  to the other table for all    * 
* columns involved in the CGS, and it must have a              * 
* high2key/low2key                                             * 
* range for each column that is inclusive of the other table.  * 
*                                                              * 
* For non-cyclic joins, these conditions are not critical;     * 
* however, when 3 or more tables are involved in a cycle of    * 
* joins,                                                       * 
* if these conditions are not met for 1 pair of tables in the  * 
* cycle, the optimizer might end up correcting for statistical * 
* correlation for some of the joins, and not others.  This can * 
* lead the optimizer to favour a join between the tables that  * 
* have                                                         * 
* not had their cardinality corrected.                         * 
*                                                              * 
* For example, consider the query:                             * 
*                                                              * 
* select * from T1, T2, T3 where T1.C1 = T2.D1 and             * 
* T1.C1 = T3.E3 and T2.D1 = T3.E1 and T1.C2 = T2.D2 and T1.C2  * 
* =                                                            * 
* T3.E3 and T2.D2 = T3.E3                                      * 
*                                                              * 
* with statistics for the 3 tables and their columns as        * 
* follows:                                                     * 
*                                                              * 
* Table.Column  Col Card  Hi2Key Lo2Key                        * 
* T1.C1              100              99      1                * 
* T1.C2              10                  9      1              * 
* T2.D1              50                74    26                * 
* T2.D2                4                  7      4             * 
* T3.E1                40                89    41              * 
* T3.E3                  5                4      1             * 
*                                                              * 
* In the example, T1 has a higher column cardinality and       * 
* inclusive                                                    * 
* Hi2Key, Lo2Key when comparing T1.C1 = T2.D2 and T1.C1 =      * 
* T3.E3,                                                       * 
* and when comparing T1.C2 = T2.D2 and T1.C2 = T2.E2. The      * 
* problem                                                      * 
* is observed when comparing T2.D1 = T3.E1 and T2.D2 = T3.D3   * 
* where                                                        * 
* the column cardinalities and the {Hi2Key, Lo2Key} range are  * 
* conflicting. Under these conditions, the optimizer will not  * 
* account for statistical correlation for the T2/T3 join,      * 
* leading                                                      * 
* to the optimizer potentially favouring this join.            * 
*                                                              * 
* The fix is activated using the DB2 environment variable:     * 
* DB2_CORRELATED_PREDICATES='FORCE_CGS'                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to version 9.7 Fix Pack 6                            * 
****************************************************************
Local Fix:
available fix packs:
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

Solution
Problem was first fixed in version 9.7 Fix Pack 6
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
09.12.2011
13.06.2012
13.06.2012
Problem solved at the following versions (IBM BugInfos)
9.7.FP6
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.6 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.