|
Problem IC63859
|
Status: Closed |
DB2 MAY RETURN SQL101N WHEN COMPILING CERTAIN QUERIES WITH AN OUTER JOIN AND A GROUP-BY SUBQUERY. |
| product: |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 |
| Problem description: |
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 Summary: |
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 |
| available fix packs: |
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
|
| Solution |
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 |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.10.2009
16.04.2010
16.04.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.0.,
9.1.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.9
|
 |
