home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 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
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC76001 Status: Geschlossen

A GENERATED COLUMN WHICH IS NOT USED IN THE SQL CAN BE REFERRED IN THE
ACCESS PLAN AND MAY CAUSE A SUBOPTIMAL PLAN CHOICE.

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
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.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.5 FixPack 9.                            * 
****************************************************************
Local-Fix:
Include the generated column in the index definition.
verfügbare FixPacks:
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 UDB Version 9.5 FixPack 9.
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC76373 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
26.04.2011
22.03.2012
22.03.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP9
Problem behoben lt. FixList in der Version
9.5.0.9 FixList