DB2 FOR LUW / DB2FORLUW / 970 - DB2

When a generated column can be used to derive new predicates in 
an outer join, even it's not referenced in the optimized 
statement, the optimizer may still want to retrieve it, which 
may cause suboptimal plans. 
 
For example: 
Consider two tables that are outer joined as follows: 
create table t1(c1 char(10) not null, c2 char(20) not null, c3 
char(10) not null, c2sub5 char(10) not null generated always as 
(substr(c2,1,5))); 
create unique index i1 on t1(c1, c2) include (c3); 
create table t2(c1 char(10), c2 char(20)); 
 
select t1.c3 from t2 left join t1 on (t1.c1=t2.c1 and 
t1.c2=t2.c2); 
 
In this case, c2sub5 can be used to generate a derived join 
predicate based on t1.c2=t2.c2. Even though a perfect index (i1) 
exists, the optimizer may still generate a TBSCAN or 
IXSCAN/FETCH plan in order to retrieve c2sub5. 
This can be worked around by including the unnecessary column 
(c2sub5) in the index definition: 
create unique index i1 on t1(c1, c2) include (c3, c2sub5); 
 
Note this only happens in outer joins.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5 or later.              * 
****************************************************************

Include the generated column in the index definition.

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 was first fixed in DB2 Version 9.7 Fix Pack 5.

not known / see Local fix

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

13.05.2011
12.12.2011
12.12.2011

9.7.FP5