Under rare scenarios, DB2 might produce SQL0901N when either of
the following set of conditions is true:
condition set 1:
1) The query has references to 2 or more tables
2) The tables in the query have FGAC policies defined on the
them
3) The query references one or more NOT IN or NOT EXISTS
subqueries
condition set 2:
1) The query references two or more NOT IN subqueries
2) The parents of these subqueries are joined to other tables
through inner join
3) The subqueries have nested existential subqueries with
correlated predicates.
Example:
create table t1 (c1 int,c2 int ,c3 int);
create table t2 like t1;
create table t3 (c1 int not null, c2 int not null, c3 int);
create table t4 (c1 int not null, c2 int not null, c3 int);
1)
select * from t1, t2 where t1.c2 = t2.c2 and t1.c2 not in
(select c2 from (select c1,c2 from t3 where not exists (select
t4.c1 from t4 where t3.c1 = t4.c1) fetch first 1000 rows only))
and t2.c2 not in (select c2 from (select c1,c2 from t3 where not
exists (select t4.c1 from t4 where t3.c1 = t4.c1) fetch first
1000 rows only));
2)
select * from t1, t2 where t1.c2 = t2.c2 and t1.c2 not in
(select c2 from t3 where not exists (select t4.c1 from t4 where
t3.c1 = t4.c1) ) and t2.c2 not in (select c2 from t3 where not
exists (select t4.c1 from t4 where t3.c1 = t4.c1) );
Returns:
SQL0901N The SQL statement or command failed because of a
database system
error. (Reason "Internal error: unexpected QGM".)
SQLSTATE=58004
This message will be seen in the db2diag.log:
2014-03-25-17.46.38.788000-240 I4276F906 LEVEL:
Severe
PID : 14780 TID : 12008 PROC :
db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : MTDB0
APPHDL : 0-39828 APPID: *LOCAL.DB2.141004101738
AUTHID : ADMIN HOSTNAME: MYHOST
EDUID : 12008 EDUNAME: db2agent (MTDB0) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
probe:300
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 30
sqlerrmc: Bad Plan; Unresolved QNC found
sqlerrp : SQLNG006
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000124 (5) 0xFFFFFFCE (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
STack will look like the following:
<StackTrace>
<sqloDumpEDU>
<sqldDumpContext>
<sqlrr_dump_ffdc>
<sqlzeDumpFFDC>
<sqlzeSqlCode>
<sqlnn_erds>
<sqlng_build_INDX_key>
<sqlng_build_KEY_obj>
<sqlng_build_INDX_obj>
<sqlng_build_TA_op>
<sqlng_process_iscan_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_eao_plan>
<sqlng_process_iscan_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_nljn_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_eao_plan>
<sqlng_process_filter_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_hsjn_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_nljn_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_nljn_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_process_filter_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_process_nljn_op>
<sqlngProcessLolepop>
<sqlng_process_sort_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_scan_derived_table>
<sqlng_process_scan_op>
<sqlngProcessLolepop>
<sqlng_process_mate_op>
<sqlngProcessLolepop>
<sqlng_process_pipe_op>
<sqlngProcessLolepop>
<sqlng_process_return_op>
<sqlngProcessLolepop>
<sqlng_build_thread>
<sqlng_main>
<sqlnn_cmpl>
...
</StackTrace> |
