DB2 FOR LUW / DB2FORLUW / 970 - DB2

The DB2 optimizer may take a long time to compile or exhaust 
statement heap if 
the query has a large number of table joins on one common column 
which is also 
the range partitioning column. For example, consider the 
following SQL where all the 
base tables are range partitioned on column A. 
 
select T1.A, T2.B, T3.C, T4.D, T5.E, T6.F, T7.G, T8.H, T9.I, 
T10.J 
from T1,T2,T3,T4,T5,T6,T7,T8,T9,T10 
where T1.A = T2.A and T2.A = T3.A and T3.A = T4.A and T4.A = 
T5.A 
and T5.A = T6.A and T6.A = T7.A and T7.A = T8.A and T8.A = T9.A 
and T9.A = T10.A; 
 
The optimizer will generate all the implied join predicates, 
i.e. T1.A = T3.A, T1.A = T4.A and etc., 
and all these predicates can be used to perform data partition 
elimination, as a result it may 
take a long time to compile.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* same as error description                                    * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* This issue will be addressed in version 9.7                  * 
* Please review version 9.7 corresponding APAR IC71402 for     * 
* further information.                                         * 
****************************************************************

rewrite the join predicates to prevent generating implied 
predicates, for example T1.A = T2.A can be rewritten to 
T1.A = (T2.A + 0).

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
 

This issue is fixed in version 9.7 Fixpack 5

not known / see Local fix

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

22.09.2010
13.12.2011
13.12.2011

9.7.,
9.7.FP5