DB2 FOR LUW / DB2FORLUW / 970 - DB2

To determine if you are encountering this problem, produce an 
EXPLAIN for the query and check for the following conditions: 
- there are multiple equality join predicates applied between 
the same two tables 
- the cardinality of the join is very small (a value less than 1 
is a strong indicator) 
- the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is 
more likely a better choice 
 
The following is a query with a join between two tables 
involving multiple join predicates: 
SELECT ... 
FROM ... T1, T2, ... 
WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C

Problem Description: 
THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN 
AFTER A RUNSTATS IS EXECUTED 
 
Problem Summary: 
To determine if you are encountering this problem, produce an 
 
EXPLAIN for the query and check for the following conditions: 
- there are multiple equality join predicates applied between 
the same two tables 
- the cardinality of the join is very small (a value less than 1 
is a strong indicator) 
- the join operation chosen is a NLJOIN when a HSJOIN/MSJOIN is 
more likely a better choice 
 
The following is a query with a join between two tables 
involving multiple join predicates: 
SELECT ... 
FROM ... T1, T2, ... 
WHERE ... T1.A=T2.A AND T1.B=T2.B AND T1.C=T2.C

Collecting column group statistics on the columns involved in 
the join will usually solve the problem.  If column group 
statistics or an index with the appropriate key is already 
available, then you are likely to be encountering this issue. 
The following article provides more details on how column group 
statistics are used by the optimizer and how they could help in 
these scenarios: 
http://www.ibm.com/developerworks/data/library/techarticle/dm-06 
12kapoor/index.html

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 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
 

Problem is first fixed in Version 9.7 Fixpack 4

Collecting column group statistics on the columns involved in 
 
the join will usually solve the problem.  If column group 
statistics or an index with the appropriate key is already 
available, then you are likely to be encountering this issue. 
The following article provides more details on how column group 
statistics are used by the optimizer and how they could help in 
these scenarios: 
http://www.ibm.com/developerworks/data/library/techarticle/dm-06 
12kapoor/index.html

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

04.08.2010
29.04.2011
29.04.2011

9.7.FP4