|
Problem IC88197
|
Status: Closed |
QUERY WITH LEFT OUTER JOIN AND IS NULL PREDICATE MIGHT FAIL WITH SQL0901N |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
Under rare scenarios, DB2 might return a SQL0901N error message
when the following conditions are satisfied.
A. The query has a Left Outer Join
B. There is a 'IS NULL' predicate on the null producing side of
the left join.
C. The column used in the IS NULL predicate is also used in
another predicate
Example:
select t1.ID
from t1
left outer join t2
on t1.ID = T2.ID
left outer join t3 <--condition 1
on t2.ID = t3.ID
where
(t2.c2 = 'VVVV0001' or t3.c2='VVVV0001') <--condition 3
and t3.c2 is null; <--condition 2
SQL0901N The SQL statement failed because of a non-severe
system error.
Subsequent SQL statements can be processed. (Reason "Had
troubles while removing an unreferenced QNC.".) SQLSTATE=58004
Similar stack will be dumped in the Trap file:
ossDumpStackTraceEx
OSSTrapFile::dumpEx
sqlo_trce
sqloDumpDiagInfoHandler
__restore_rt
pthread_kill
sqloDumpEDU
sqldDumpContext
sqlrr_dump_ffdc
sqlzeDumpFFDC
sqlzeSqlCode
sqlnn_erds
sqlnr_Manual2NativeAJ
sqlnr_optprep
sqlnr_optprep_action
sqlnr_comp |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Please upgrade to DB2 version 10.1 fixpack 3 *
**************************************************************** |
| Local Fix: |
1. Set the following registry variable
db2set DB2COMPOPT=327685,67108864
2. Recycle the instance |
| available fix packs: |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
Fixed in DB2 version 10.1 fixpack 3 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 13.11.2012
11.11.2013
11.11.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.3
|
 |
| 10.1.0.3
|
|
