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 parent(s) of these subqueries are joined to other tables
through inner join
3) The subqueries contain two or more tables or 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) );
3)
select *
from t1, t2
where t1.c2 = t2.c2 and
t1.c2 not in (select t3.c2
from t3, t4
where t3.c1 = t4.c1) and
t2.c2 not in (select t3.c2
from t3, 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
For 10.1 fp3 and lower, the message seen in the db2diag.log is:
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:
And the stack will look like this:
<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>
For v10.1 fp4, the message seen in the db2diag.log is:
014-05-02-09.59.21.917000-300 I233696F905 LEVEL: Severe
PID : 1648 TID : 6896 PROC :
db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : TEST
APPHDL : 0-29 APPID: *LOCAL.DB2.140502145901
AUTHID : BARRETT3 HOSTNAME: MYHOST
EDUID : 6896 EDUNAME: db2agent (TEST)
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: Internal error: unexpected QGM
sqlerrp : SQLNR12D
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0xFFFFFEF7 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
And the stack will look like this:
<StackTrace>
<sqloDumpEDU>
<sqldDumpContext>
<sqlrr_dump_ffdc>
<sqlzeDumpFFDC>
<sqlzeSqlCode>
<sqlnn_erds>
<sqlnr_NotExists2OJ_detail>
<sqlnr_NotExists2OJ_action>
<sqlnr_seq>
<sqlnr_rcc>
<sqlnr_startqtb_action>
<sqlnr_seq>
<sqlnr_rcc>
<sqlnr_start_action>
<sqlnr_prep2_round1_action>
<sqlnr_seq>
<sqlnr_rcc>
<sqlnr_exe>
<sqlnn_cmpl>
...
</StackTrace>
----------------------------------------------------
Also In v10.1 fp 4 -SQL0901N is seen with the below stack.
sqlnn_cmpl [300]:rc(-2144272270) Error in parser
sqlnp_main [250]:rc(-2144272270) Invoke LPG
parser (semantic phase)
sqlnp_parser [330]:rc(-2144272270) Call smactn
sqlnp_smactn [100]:rc(-2144272270) Processing
semantic action
sqlnq_sem [420]:rc(-2144272270)
sqlnq_handle_from_table_ref called
sqlnq_handle_from_table_re[ 22]:rc(-2144272270) call
sqlnq_check_referenced_qtb
sqlnq_check_referenced_qtb[200]:rc(-2144272270) calling qtb
version of check referenced
sqlnq_check_referenced_qtb[180]:rc(-2144272270)
sqlnq_handle_new_view called
sqlnq_handle_new_view [158]:rc(-2144272270) Parse the view
sqlnp_main [250]:rc(-2144272270) Invoke LPG
parser (semantic phase)
sqlnp_parser [330]:rc(-2144272270) Call smactn
sqlnp_smactn [100]:rc(-2144272270) Processing
semantic action
sqlnq_sem_function_call [ 10]:rc(-2144272270) Unexpected error
from sqlnq_fun_tres
sqlnq_fun_tres [140]:rc(-2144272270) Call
sqlnq_fun_res_walk
sqlnq_fun_res_walk [310]:rc(-2144272270) Call function
encapsulator
sqlnq_encap_doit [1880]:rc(-2144272270) encap UDF
sqlnq_encap_UDF [110]:rc(-2144272270) arg promotion
failed
sqlnq_convertNode [ 50]:rc(-2144272364) Can't find a
cast function
SQL0901N The SQL statement or command failed because of a
database system
error. (Reason "arg promotion failed".) SQLSTATE=58004 |
