DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

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.

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

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 9a 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 10 for Linux, UNIX, and Windows
 

This APAR is first fixed on the DB2 V9.7 fixpak2

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

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

24.09.2009
13.05.2010
13.05.2010

9.7.