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 | |
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 |