DB2 - Problembeschreibung
Problem IC68050 | Status: Geschlossen |
SQL0901N WHEN INNER JOIN IS USED IN AN UPDATE OR SELECT QUERY | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
A customer may encounter SQL0901N running an UPDATE or SELECT query. This happens if the query uses INNER JOIN instead of comma separated join and if one of the following is true: 1. The INNER JOIN is referencing the same table multiple times. 2. There is a subquery joining on a unique column The message displayed to the user can be "DOID1 is missing" or "OID imbalance". Example 1: *** select 1 from t1 join t3 on (t1.c1= t3.c1) join t4 on (t3.c2= t4.c2) join t4 as t5 on (t3.c2 = t5.c2) join t4 as t6 on (t1.c2 = t6.c2) join t4 as t7 on (t1.c1 = t7.c1) where t1.c1 <> 1 ; can be rewritten as: select 1 from t1, t3, t4, t4 as t5, t4 as t6, t4 as t7 where (t1.c1= t3.c1) AND (t3.c2= t4.c2) AND (t3.c2 = t5.c2) AND (t1.c2 = t6.c2) AND (t1.c1 = t7.c1) AND t1.c1 <> 1 ; **** Example 2: **** INSERT INTO T1 SELECT v1.c1, v1.c2 FROM v1 INNER JOIN t2 ON t1.c1=v1.c1 INNER JOIN t3 ON t3.c1 = t2.c1 WHERE v1.c4 IN (SELECT c4 FROM TABLE(FOO(123,'A')) AS table_function); can be rewritten as: INSERT INTO T1 SELECT v1.c1, v1.c2 FROM (SELECT c4 FROM TABLE(FOO(123,'A')) AS table_function) as SQ INNER JOIN v1 ON SQ.c4 = v1.c4 INNER JOIN t2 ON t1.c1=v1.c1 INNER JOIN t3 ON t3.c1 = t2.c1; If the C4 from the table function is NOT unique, this rewritten query is not equivalent to the original query and we CANNOT use this work-around. **** | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * all * **************************************************************** * PROBLEM DESCRIPTION: * * SQL0901N WHEN INNER JOIN IS USED IN AN UPDATE OR SELECT * * QUERY * **************************************************************** * RECOMMENDATION: * * upgrade to DB2 version 950 fix pack 6 * **************************************************************** | |
Local-Fix: | |
Replace the inner join with a comma separated join to fix the issue | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows | |
Lösung | |
problem was fixed in DB2 version 950 fixpack 6 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC68076 IC68077 IC68492 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 20.04.2010 13.05.2010 13.05.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.0 | |
Problem behoben lt. FixList in der Version |