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

DB2 MAY RETURN SQL101N WHEN COMPILING CERTAIN QUERIES WITH AN OUTER JOIN
AND A GROUP-BY SUBQUERY.

Produkt:
DB2 FOR LUW / DB2FORLUW / 910 - DB2
Problembeschreibung:
DB2 may return SQL101N when compiling queries that satisfy all 
of the following conditions: 
 
1. Query has LEFT or RIGHT OUTER JOIN subquery, called ojSubq 
2. Query has Group-By subquery, called gbSubq 
3. gbSubq inner joins with ojSubq. gbSubq join key(s) contain 
all its Group-By columns. Some ojSubq join key(s) is derived 
from its own Group-By subquery or Null Producing stream of its 
own outer join subquery. 
 
How to diagnose the issue: 
1. The query returns SQL101N even when STMTHEAP has been set to 
maximum value. Query goes into an infinite loop until the 
the STMTHEAP is exhausted. 
 
2. The failing trace stack includes the following 
routines:    ..... 
   0x0900000070E4750C 
sqlnr_reorder_IJ_qgm__FCP9sqlnq_qunCP9sqlnq_oprPP13sqlnq_deplist 
PiCP3loc + 0x346C 
   0x0900000070E18890 sqlnr_qrwprep_phase2__FP3locPi + 0xAB0 
   ..... 
 
3. db2trc output indicates that sqlnr_qrwpprep_phase2() calls 
sqlnr_reorder_IJ_qgm() in an infinite loop. 
 
This issue occurs only starting 9.5 Fixpak 4
Problem-Zusammenfassung:
DB2 MAY RETURN SQL101N WHEN COMPILING CERTAIN QUERIES WITH AN 
OUTER JOIN AND A GROUP-BY SUBQUERY.
Local-Fix:
Manually change equality join predicate between gbSubq and 
ojSubq to a BETWEEN predicate. 
For example, change 
     WHERE gbSubq.colX = ojSubq.colY 
to 
     WHERE gbSubq.colX BETWEEN ojSubq.colY AND ojSubq.colY
verfügbare FixPacks:
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

Lösung
Problem was first fixed in Version 9.1 Fixpak 9
Workaround
Manually change equality join predicate between gbSubq and 
ojSubq to a BETWEEN predicate. 
 
For example, change 
 
  WHERE gbSubq.colX = ojSubq.colY 
to 
  WHERE gbSubq.colX BETWEEN ojSubq.colY AND ojSubq.colY
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
20.10.2009
16.04.2010
16.04.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.0.,
9.1.
Problem behoben lt. FixList in der Version
9.1.0.9 FixList