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 IC63303 Status: Geschlossen

POSSIBLE UNEXPECTED RESULTS IN SQL STATEMENT CONTAINING OR
PREDICATE(S) WITH IN SUBQUERIES.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
This problem could occur when all the following conditions are 
met: 
. 
1) The SQL statement has an OR predicate with subquery of the 
form 
. 
   <col-1> IN (select <col-sq> from....) OR <other-subterm> 
. 
   -OR- 
   <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 
2) The statement also has an OR predicate with subquery of the 
form 
   <col-2> IN (select <col-sq> from....) OR <other-subterm> 
   -OR- 
    <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> 
    Note that the OR predicate in 1) and 2) could be the same OR 
predicate or two different OR predicates 
3) The <col-sq> in 1) and 2) must be the same value of a 
constant, a host variable, 
    a parameter marker, or a special register such as CURRENT 
DATE. In addition, 
    <col-sq> could be a normal column in the user written SQL 
statement but is 
    substituted by DB2 Query Rewrite to one of those 
aforementioned types, 
    which could be observed in the Optimized Statement of an 
output of db2exfmt. 
4) There are join predicates in other part of the same SQL 
statement such that DB2 Optimizer 
    could derive the following relationship 
    <col-1> = <col-2> = <col-3> 
    An example of such join predicates is 
    <col-1> = <col-3> AND <col-2> = <col-3> 
5) When all the above conditions are met, DB2 incorrectly 
derives that 
    <col-1> = <col-2> = <col-3> = <col-sq> 
In some certain choices of access plan where DB2 Optimizer 
chooses to apply the two join predicates 
<col-1> = <col-3> and <col-2> = <col-3> in the same join 
operator, the Optimizer may not apply one of 
the join predicates in 4). As a result, DB2 will produce 
possibly more rows in the result set.
Problem-Zusammenfassung:
This problem could occur when all the following conditions are 
met: 
. 
1) The SQL statement has an OR predicate with subquery of the 
form 
. 
   <col-1> IN (select <col-sq> from....) OR <other-subterm> 
. 
   -OR- 
   <col-1> NOT IN (select <col-sq> from....) OR <other-subterm> 
2) The statement also has an OR predicate with subquery of the 
form 
   <col-2> IN (select <col-sq> from....) OR <other-subterm> 
   -OR- 
    <col-2> NOT IN (select <col-sq> from....) OR <other-subterm> 
    Note that the OR predicate in 1) and 2) could be the same OR 
predicate or two different OR predicates 
3) The <col-sq> in 1) and 2) must be the same value of a 
constant, a host variable, 
    a parameter marker, or a special register such as CURRENT 
DATE. In addition, 
    <col-sq> could be a normal column in the user written SQL 
statement but is 
    substituted by DB2 Query Rewrite to one of those 
aforementioned types, 
    which could be observed in the Optimized Statement of an 
output of db2exfmt. 
4) There are join predicates in other part of the same SQL 
statement such that DB2 Optimizer 
    could derive the following relationship 
    <col-1> = <col-2> = <col-3> 
    An example of such join predicates is 
    <col-1> = <col-3> AND <col-2> = <col-3> 
5) When all the above conditions are met, DB2 incorrectly 
derives that 
    <col-1> = <col-2> = <col-3> = <col-sq> 
In some certain choices of access plan where DB2 Optimizer 
chooses to apply the two join predicates 
<col-1> = <col-3> and <col-2> = <col-3> in the same join 
operator, the Optimizer may not apply one of 
the join predicates in 4). As a result, DB2 will produce 
possibly more rows in the result set.
Local-Fix:
verfügbare FixPacks:
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

Lösung
module engn_sqnr 
fixed >= v97 fpk1
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
21.09.2009
11.02.2010
11.02.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FPk1
Problem behoben lt. FixList in der Version
9.7.0.1 FixList