home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC62585 Status: Geschlossen

QUERY RECEIVES ERROR DURING STATEMENT COMPILATION WITH SQLCODE -901

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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 ;
Problem-Zusammenfassung:
QUERY RECEIVES ERROR DURING STATEMENT COMPILATION WITH SQLCODE 
-901
Local-Fix:
- 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
verfügbare FixPacks:
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

Lösung
First fixed in DB2 UDB Version 9.7, FixPak 1
Workaround
- 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
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
18.08.2009
28.12.2009
28.12.2009
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.
Problem behoben lt. FixList in der Version
9.7.0.1 FixList