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

OPTIMIZER MAY OVERESTIMATE JOIN CARDINALITY UNDER CERTAIN CONDITIONS
WHEN STATISTICS ARE NOT COLLECTED ON ONE OF THE TABLE

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
The DB2 Query Optimizer may choose a non-optimal query execution 
plan for an SQL statement that contains a join between two or 
more tables, when the following conditions are satisfied for at 
least one the joins: 
 
1.  the join consists of two or more equality predicates 
2.  statistics are collected on one of the tables and not the 
other 
3.  a unique index or primary key exists on the table that 
statistics are not collected on, with the join predicates 
applied to all the key columns 
4.  a column group statistic or index on the join predicate 
columns does not exist on the table that statistics are 
collected on 
 
For example, consider the following SQL statement 
 
SELECT * 
FROM T1, T2 
WHERE T1.A=T2.A and T1.B=T2.B 
 
If an index on T1(A,B) does not exist and statistics are 
collected on T1 as follows: 
RUNSTATS ON TABLE <schema>.T1 WITH DISTRIBUTION AND DETAILED 
INDEXES ALL; 
and statistics are not collected on T2 but (A,B) is defined as 
the primary key of the table then, under these conditions, the 
optimizer may overestimate the cardinality of the join which may 
result in a non-optimal query execution plan.
Problem Summary:
ERROR DESCRIPTION: 
 
The DB2 Query Optimizer may choose a non-optimal query execution 
plan for an SQL statement that contains a join between two or 
more tables, when the following conditions are satisfied for at 
least one the joins: 
 
1.  the join consists of two or more equality predicates 
2.  statistics are collected on one of the tables and not the 
other 
3.  a unique index or primary key exists on the table that 
statistics are not collected on, with the join predicates 
applied to all the key columns 
4.  a column group statistic or index on the join predicate 
columns does not exist on the table that statistics are 
collected on 
 
For example, consider the following SQL statement 
 
SELECT * 
FROM T1, T2 
WHERE T1.A=T2.A and T1.B=T2.B 
 
If an index on T1(A,B) does not exist and statistics are 
collected on T1 as follows: 
RUNSTATS ON TABLE <schema>.T1 WITH DISTRIBUTION AND DETAILED 
INDEXES ALL; 
and statistics are not collected on T2 but (A,B) is defined as 
the primary key of the table then, under these conditions, the 
optimizer may overestimate the cardinality of the join which may 
result in a non-optimal query execution plan.
Local Fix:
Any of the following actions will resolve this issue: 
 
1.  Collect statistics on all tables, or enable automatic 
statistics collection.  Collecting statistics is one of the best 
practices to minimize the impact of SQL statements on 
performance, as described in the best practice paper on "Writing 
and Tuning Queries for Optimal Performance".  This paper is 
available at: 
http://www.ibm.com/developerworks/data/bestpractices/ 
 
2.  Collect column group statistics on the table that statistics 
are collected on.  In the example, column group statistics can 
be collected on T1(A,B) as follows: 
RUNSTATS ON TABLE <schema>.T1 ON ALL COLUMNS AND COLUMNS ((A,B)) 
WITH DISTRIBUTION AND DETAILED INDEXES ALL; 
If automatic runstats is enabled, a statistics profile will be 
required.  The following article provides more information on 
how the DB2 Query Optimizer makes use of column group statistics 
and how to collect them: 
http://www.ibm.com/developerworks/data/library/techarticle/dm-06 
12kapoor/index.html
available fix packs:
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
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
The APAR is first fixed in the DB2 v9.5 fixpak6
Workaround
Any of the following actions will resolve this issue: 
 
 
1.  Collect statistics on all tables, or enable automatic 
statistics collection.  Collecting statistics is one of the best 
practices to minimize the impact of SQL statements on 
performance, as described in the best practice paper on "Writing 
and Tuning Queries for Optimal Performance".  This paper is 
available at: 
http://www.ibm.com/developerworks/data/bestpractices/ 
 
2.  Collect column group statistics on the table that statistics 
are collected on.  In the example, column group statistics can 
be collected on T1(A,B) as follows: 
RUNSTATS ON TABLE <schema>.T1 ON ALL COLUMNS AND COLUMNS ((A,B)) 
WITH DISTRIBUTION AND DETAILED INDEXES ALL; 
If automatic runstats is enabled, a statistics profile will be 
required.  The following article provides more information on 
how the DB2 Query Optimizer makes use of column group statistics 
and how to collect them: 
http://www.ibm.com/developerworks/data/library/techarticle/dm-06 
12kapoor/index.html
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC63389 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
24.09.2009
13.05.2010
13.05.2010
Problem solved at the following versions (IBM BugInfos)
9.5.
Problem solved according to the fixlist(s) of the following version(s)
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.