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

DB2 MAY ISSUE SQL0901N EXECUTING A QUERY WITH CORRELATED REFERENCES
TO A TABLE FROM AN OUTER JOIN

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
An SQL0901N may be issued when executing a query containing a 
LEFT or RIGHT OUTER JOIN (LOJ) if correlation exists to the 
table from the row-preserving (RP) side of the LOJ. 
 
The following conditions must be satisfied for the scenario to 
occur: 
- The RP table has a unique key associated with it 
- 2 or more levels of correlation exist to the RP table 
- All correlated constructs are inner-joined after the LOJ 
(either directly or via transitivity) on the correlated column 
 
An example scenario follows: 
 
-- Table DDL 
CREATE TABLE RP (PK_C CHAR(2) NOT NULL PRIMARY KEY); 
CREATE TABLE NP (J_C CHAR(2)); 
 
-- Table Function DDL 
CREATE FUNCTION F1(IN_C CHAR(2)) 
RETURNS TABLE   (OUT_C CHAR(2)) 
LANGUAGE SQL 
DETERMINISTIC 
NO EXTERNAL ACTION 
BEGIN ATOMIC 
 DECLARE DUMMY CHAR(2);-- 
SET (DUMMY) = (IN_C);-- 
RETURN VALUES(IN_C);-- 
END; 
 
CREATE FUNCTION F2(IN_C CHAR(2)) 
RETURNS TABLE   (OUT_C CHAR(2)) 
LANGUAGE SQL 
DETERMINISTIC 
NO EXTERNAL ACTION 
READS SQL DATA 
RETURN 
SELECT  A.J_C 
FROM  (VALUES(IN_C)) AS A(J_C) 
LEFT OUTER JOIN NP AS B 
ON  B.J_C = A.J_C; 
 
-- Query that will issue the SQL0901N 
SELECT A.PK_C 
FROM   RP AS A, 
      TABLE(F1(A.PK_C)) AS B, -- First level of correlation 
     TABLE(F2(B.OUT_C)) AS C   -- Second level of correlation 
WHERE A.PK_C = B.OUT_C   -- Explicit inner-join 
AND   B.OUT_C = C.OUT_C   -- Inner-join via transitivity 
; 
 
-- SQLCODE issued 
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.5 Fix Pack 5.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Use a query containing a LEFT or RIGHT OUTER JOIN            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* An SQL0901N may be issued when executing a query containing  * 
* a LEFT or RIGHT OUTER JOIN (LOJ) if correlation exists to    * 
* the                                                          * 
* table from the row-preserving (RP) side of the LOJ.          * 
*                                                              * 
* The following conditions must be satisfied for the scenario  * 
* to occur:                                                    * 
* - The RP table has a unique key associated with it           * 
* - 2 or more levels of correlation exist to the RP table      * 
* - All correlated constructs are inner-joined after the LOJ   * 
* (either directly or via transitivity) on the correlated      * 
* column                                                       * 
*                                                              * 
* An example scenario follows:                                 * 
*                                                              * 
* -- Table DDL                                                 * 
* CREATE TABLE RP (PK_C CHAR(2) NOT NULL PRIMARY KEY);         * 
* CREATE TABLE NP (J_C CHAR(2));                               * 
*                                                              * 
* -- Table Function DDL                                        * 
* CREATE FUNCTION F1(IN_C CHAR(2))                             * 
* RETURNS TABLE   (OUT_C CHAR(2))                              * 
* LANGUAGE SQL                                                 * 
* DETERMINISTIC                                                * 
* NO EXTERNAL ACTION                                           * 
* BEGIN ATOMIC                                                 * 
* DECLARE DUMMY CHAR(2);--                                     * 
* SET (DUMMY) = (IN_C);--                                      * 
* RETURN VALUES(IN_C);--                                       * 
* END;                                                         * 
*                                                              * 
* CREATE FUNCTION F2(IN_C CHAR(2))                             * 
* RETURNS TABLE   (OUT_C CHAR(2))                              * 
* LANGUAGE SQL                                                 * 
* DETERMINISTIC                                                * 
* NO EXTERNAL ACTION                                           * 
* READS SQL DATA                                               * 
* RETURN                                                       * 
* SELECT  A.J_C                                                * 
* FROM  (VALUES(IN_C)) AS A(J_C)                               * 
* LEFT OUTER JOIN NP AS B                                      * 
* ON  B.J_C = A.J_C;                                           * 
*                                                              * 
* -- Query that will issue the SQL0901N                        * 
* SELECT A.PK_C                                                * 
* FROM   RP AS A,                                              * 
* TABLE(F1(A.PK_C)) AS B, -- First level of correlation        * 
* TABLE(F2(B.OUT_C)) AS C   -- Second level of                 * 
* correlation                                                  * 
* WHERE A.PK_C = B.OUT_C   -- Explicit inner-join              * 
* AND   B.OUT_C = C.OUT_C   -- Inner-join via transitivity     * 
* ;                                                            * 
*                                                              * 
* -- SQLCODE issued                                            * 
* 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 workaround exists aside from manually rewriting the query in 
question.
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