DB2 FOR LUW / DB2FORLUW / 970 - DB2

The DB2 query optimizer can apply ordered column group OR 
predicates as 
index start/stop key predicates for more efficient access. For 
example the 
following predicate 
(a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 and a3>=3) 
defines a column group (a1, a2, a3) and the inclusive 
condition on the key values (1, 2, 3). So if there is an 
index (a1 asc, a2 asc, a3 asc), then the optimizer can apply 
the composite inclusive key above for better filtering and 
minimal data read from the index. 
Under certain cases, when there are other predicates applicable 
on a subset 
of the key columns, you may encounter incorrect results.  For 
example, the 
following set of predicates includes a join between tables A and 
B that 
applies to the first 2 key columns: 
a1=b1 AND a2=b2 AND ((a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 
and a3>=3)) 
If a nested loop join (NLJOIN) operation is chosen with A on the 
inner, you may 
experience incorrect results.

The DB2 query optimizer can apply ordered column group OR 
predicates as 
index start/stop key predicates for more efficient access. For 
example the 
following predicate 
(a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 and a3>=3) 
defines a column group (a1, a2, a3) and the inclusive 
condition on the key values (1, 2, 3). So if there is an 
index (a1 asc, a2 asc, a3 asc), then the optimizer can apply 
the composite inclusive key above for better filtering and 
minimal data read from the index. 
Under certain cases, when there are other predicates applicable 
on a subset 
of the key columns, you may encounter incorrect results.  For 
example, the 
following set of predicates includes a join between tables A and 
B that 
applies to the first 2 key columns: 
a1=b1 AND a2=b2 AND ((a1>1) or (a1=1 and a2>2) or (a1=1 and a2=2 
and a3>=3)) 
If a nested loop join (NLJOIN) operation is chosen with A on the 
inner, you may 
experience incorrect results.

Optimization profiles may be used to change the join operation 
or reverse 
the order of the join to avoid an access plan that leads to the 
incorrect results.

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

apar: ic63668 
module: engn_sqno 
fixed >= v97 fpk1

Optimization profiles may be used to change the join operation 
or reverse 
the order of the join to avoid an access plan that leads to the 
incorrect results.

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

08.10.2009
02.12.2009
02.12.2009

9.7.FPk1