DB2 FOR LUW / DB2FORLUW / 970 - DB2

Query receives error during statement compilation with sqlcode 
-901: 
 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason 
"sqlno_get_best_plan [200]:rc(     0)                ".) 
SQLSTATE=58004 
 
- a stack with sqlnn_cmpl calling sqlnn_erdm 
 
0000002A9B785047 ossDumpStackTraceEx + 0x01f7 
0000002A9B780BBC _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm + 0x00b4 
0000002A9B780C83 _ZN11OSSTrapFile4dumpEmiP7siginfoPv + 0x0009 
0000002A97CEEA35 sqlo_trce + 0x03f3 
0000002A97D2D849 sqloDumpDiagInfoHandler + 0x00dd 
0000002A95679260 address: 0x0000002A95679260 ; dladdress: 
0x0000002A9566D000 ; offset in lib: 0x000000000000C260 ; 
0000002A956765A3 pthread_kill + 0x0033 
0000002A97D2DC1E sqloDumpEDU + 0x0018 
0000002A97064C43 _Z15sqldDumpContextP8sqeAgentiiiiiPKcPvi + 
0x0635 
0000002A96B20186 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x0482 
0000002A96CC6606 _Z13sqlzeDumpFFDCP8sqeAgentjP5sqlcai + 0x0020 
0000002A96CC6AA2 
_Z11sqlzeMapZrcP8sqeAgentjmjP5sqlcaiPK12sqlzeContext + 0x0436 
0000002A97593602 _Z10sqlnn_erdmiiiiPK12sqlzeContext + 0x00d4 
0000002A968E7182 
_Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sq 
lrr_cmpl_enviiPP9sqlnq_qur + 0x1442 
 
 
- an Info diagnostic entry in the db2diag.log with following 
internal compiler error stack 
 
2009-03-10-01.01.45.738577-240 E51370E884          LEVEL: Info 
(Origin) 
PID     : 32657                TID  : 183341410656 PROC : 
db2sysc 
INSTANCE: db2inst1             NODE : 000          DB   : DB95 
APPHDL  : 0-7                  APPID: 
*LOCAL.db2inst1.090310050122 
AUTHID  : DB2INST1 
EDUID   : 16                   EDUNAME: db2agent (DB95) 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 
MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS 
          "unexpected error but state is OK" 
DATA #1 : String, 353 bytes 
Compiler error stack for rc = -2144272209: 
sqlnn_cmpl[390] 
sqlno_exe[900] 
sqlno_planning_phase[200] 
sqlno_planning_scan[400] 
sqlno_scan_utils_qgm[200] 
sqlno_each_qur[400] 
sqlno_top_qtb[100] 
sqlno_each_opr[200] 
sqlno_walk_qun[100] 
sqlno_each_opr[900] 
sqlno_plan_end_opr[100] 
sqlno_gen_partitions_for_Q[400] 
sqlno_crule_join[100] 
sqlno_get_best_plan[200] 
 
 
- the problem occurs when: 
    - a table (S.T1 in example below) has a generated column 
    - the same table is used in a join MQT (S.M1 below) with 
another table (S.T2 below) 
    - the MQT selects the column that the generated column is 
generated on 
    - a query references the tables in the join MQT and a third 
table (S.T3 below) 
    - the MQT is considered in query matching for the query 
 
 
- repro / example: 
 
create table sa.t1 (c1 int, c2 int, 
                   g2 int GENERATED ALWAYS AS (c2/100) ); 
create unique index sa.i_t1c1 on sa.t1 (c1); 
 
create table sa.t2 (c1 int, c2 int, c3 int); 
create unique index sa.i_t2c1 on sa.t2 (c3); 
 
create table sa.t3 (c1 int not null); 
 
create summary table sa.m1  as 
  (select t1.c1 as t1c1, t1.c2 as t1c2, t2.c3 as t2c3 
   from sa.t1 t1, sa.t2 t2 where t1.c1=t2.c1 ) 
   DATA INITIALLY DEFERRED REFRESH immediate ENABLE QUERY 
OPTIMIZATION; 
 
refresh table sa.m1; 
 
-- query gets -901 error 
select 1 from sa.t1 t1, sa.t2 t2, sa.t3 t3 
 where t1.c2 = 92624 and t1.c1 = t2.c1 and t2.c1 = t3.c1 ; 
 
-- query does not get -901 error when predicate on generated 
column also manually provided 
select 1 from sa.t1 t1, sa.t2 t2, sa.t3 t3 
 where t1.c2 = 92624 and t1.c1 = t2.c1 and t2.c1 = t3.c1 
 and t1.g2 = 92624 ;

QUERY RECEIVES ERROR DURING STATEMENT COMPILATION WITH SQLCODE 
-901

- alter the query, then add a redundant predicate on the 
generated column (as show in the example) 
 
- prevent MQT matching for the affected query via setting 
CURRENT REFRESH AGE to 0 for REFRESH DEFERRED MQTs, or via 
setting CURRENT QUERY OPTMIZATION to 0, 1, or 3

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
 

First fixed in DB2 UDB Version 9.7, FixPak 1

- alter the query, then add a redundant predicate on the 
generated column (as show in the example) 
 
- prevent MQT matching for the affected query via setting 
CURRENT REFRESH AGE to 0 for REFRESH DEFERRED MQTs, or via 
setting CURRENT QUERY OPTMIZATION to 0, 1, or 3

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

18.08.2009
28.12.2009
28.12.2009

9.7.