DB2 FOR LUW / DB2FORLUW / 970 - DB2

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

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

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

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
 

Problem was first fixed in version 9.7 Fixpak 1

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

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

07.08.2009
19.12.2009
19.12.2009

9.7.