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

DB2 MAY ISSUE AN SQL0901N COMPILING A QUERY CONTAINING AN OUTER JOIN ON A
TABLE WITH GENERATED COLUMNS

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
During compilation of a query containing a LEFT or RIGHT OUTER 
JOIN, DB2 may issue an SQL0901N if the following conditions are 
satisfied: 
 
1) The table from the NULL-producing (NP) side has a generated 
column (GC) 
2) The parent column (PC) of the GC is used in the ON clause as 
part of the JOIN predicate 
3) The column from the row-preserving (RP) side has a local 
predicate 
4) The GC from the NP table has derivable equivalence to the RP 
column via its PC and the local predicate literal 
 
An example scenario that may generate (depending on the 
Optimizer plan picked) such an SQL0901N follows: 
 
-- Table DDL 
CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' '); 
CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' , 
                 DUMMY CHAR(9) GENERATED ALWAYS AS (SUBSTR(C1, 
1, 9)), 
                 GC CHAR(13) GENERATED ALWAYS AS (SUBSTR(C1, 1, 
13))); 
 
-- Query that may generate the -901 
SELECT          B.C1 
FROM            RP AS A 
LEFT OUTER JOIN NP AS B 
ON  A.C1 = B.C1 
AND A.C1 = '1234567890' 
; 
 
-- -901 text 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason "Bad Plan; 
Unresolved QNC 
found".)  SQLSTATE=58004 
 
This problem will be first fixed in DB2 Version 9.1 Fix Pack 8 
and DB2 Version 9.5 Fix Pack 5.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Use a query containing a LEFT or RIGHT OUTER JOIN            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* During compilation of a query containing a LEFT or RIGHT     * 
* OUTER JOIN, DB2 may issue an SQL0901N if the following       * 
* conditions                                                   * 
* are satisfied:                                               * 
*                                                              * 
* 1) The table from the NULL-producing (NP) side has a         * 
* generated column (GC)                                        * 
* 2) The parent column (PC) of the GC is used in the ON clause * 
* as part of the JOIN predicate                                * 
* 3) The column from the row-preserving (RP) side has a local  * 
* predicate                                                    * 
* 4) The GC from the NP table has derivable equivalence to the * 
* RP column via its PC and the local predicate literal         * 
*                                                              * 
* An example scenario that may generate (depending on the      * 
* Optimizer plan picked) such an SQL0901N follows:             * 
*                                                              * 
* -- Table DDL                                                 * 
* CREATE TABLE RP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ');     * 
* CREATE TABLE NP (C1 CHAR(20) NOT NULL WITH DEFAULT ' ' ,     * 
* DUMMY CHAR(9) GENERATED ALWAYS AS                            * 
* (SUBSTR(C1,1, 9)),                                           * 
* GC CHAR(13) GENERATED ALWAYS AS (SUBSTR(C1,1,13)));          * 
*                                                              * 
* -- Query that may generate the -901                          * 
* SELECT          B.C1                                         * 
* FROM            RP AS A                                      * 
* LEFT OUTER JOIN NP AS B                                      * 
* ON  A.C1 = B.C1                                              * 
* AND A.C1 = '1234567890'                                      * 
* ;                                                            * 
*                                                              * 
* -- -901 text                                                 * 
* SQL0901N  The SQL statement failed because of a non-severe   * 
* system error.                                                * 
* Subsequent SQL statements can be processed.  (Reason "Bad    * 
* Plan; Unresolved QNC found".)  SQLSTATE=58004                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1                        * 
****************************************************************
Local-Fix:
No feasible workaround exists aside from altering the query or 
the table(s) in question. 
Additionally, if a suitable Optimizer plan is picked, the 
problem may not manifest even if all conditions required to 
reproduce the problem are satisfied.
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
Problem is first fixed in DB2 Version 9.7 Fix Pack 1.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
21.08.2009
13.01.2010
13.01.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP1
Problem behoben lt. FixList in der Version
9.7.0.1 FixList