Latest versionsfixlist
11.1.0.6 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
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC70379 Status: Closed

THE OPTIMIZER MIGHT CHOOSE A LESS-THAN-OPTIMAL QUERY ACCESS PLAN AFTER A
RUNSTATS IS EXECUTED

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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 Summary:
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
Local Fix:
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
available fix packs:
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Problem is first fixed in Version 9.5 Fix Pack 8
Workaround
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
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
04.08.2010
05.07.2011
05.07.2011
Problem solved at the following versions (IBM BugInfos)
9.5.FP8
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.8 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.