DB2 FOR LUW / DB2FORLUW / 970 - DB2

A query using like predicate as: 
. 
c1 like '%' 
. 
where 
(1) c1 is defined as NOT NULLABLE, and 
(2) there exists an MQT or statistical view with no group-by 
operation that the query without the above predicate can be 
matched to, and 
(3) the MQT or statistical view does not reference c1. 
. 
may fail with SQL0901N during MQT matching. 
. 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason 
"sqlnn_tra_1st_err: the 
tra stack is empty!".)  SQLSTATE=58004 
In the diag, you would see: 
. 
2008-06-17-08.34.26.780481-240 I3141488A448       LEVEL: Severe 
PID     : 1458304              TID  : 1           PROC : 
db2agent (INTTEST) 0 
INSTANCE: inttest              NODE : 000         DB   : EDWINT 
APPHDL  : 0-325                APPID: *N0.inttest.080617123426 
AUTHID  : INTTEST 
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:20 
RETCODE : ZRC=0x80310025=-2144272347=SQLNN_E_BAD 
          "unexpected error and state is bad" 
2008-06-17-08.34.26.788812-240 I3141937A810       LEVEL: Severe 
PID     : 1458304              TID  : 1           PROC : 
db2agent (INTTEST) 0 
INSTANCE: inttest              NODE : 000         DB   : EDWINT 
APPHDL  : 0-325                APPID: *N0.inttest.080617123426 
AUTHID  : INTTEST 
FUNCTION: DB2 UDB, trace services, sqlt_logerr_data, probe:0 
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 42 
 sqlerrmc: sqlnn_tra_1st_err: the tra stack is empty! 
 sqlerrp : SQLNN066 
 sqlerrd : (1) 0x80310025      (2) 0x00000025      (3) 
0x00000000 
           (4) 0x00000000      (5) 0xFFFFFDA8      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
. 
The stack for the process shows: 
. 
0x0900000008303F10 sqloDumpEDU + 0x14 
0x0900000008C7A070 
sqldDumpContext__FP20sqle_agent_privatecbiN42PcPvT2 + 0x130 
0x0900000007C42B68 
sqldDumpContext__FP20sqle_agent_privatecbiN42PcPvT2@glue43A + 
 0x94 
0x0900000008701F2C sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x224 
0x0900000008F407BC 
sqlzeDumpFFDC__FP20sqle_agent_privatecbUiP5sqlcai + 0x24 
0x090000000870444C 
sqlzeMapZrc__FP20sqle_agent_privatecbUiUlT2P5sqlcai + 0x154 
0x090000000821EA28 sqlnn_erdm__FiN31 + 0x15C 
0x090000000822139C sqlnn_erdm__FiN31@glue20 + 0x84 
0x0900000007D78528 
sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_co 
mpileModeT3P14sqlrr_cmpl_enviT7PP9sqlnq_qur + 0x610 
0x0900000008DDA294 
sqlnn_cmpl__FP20sqle_agent_privatecbP11sqlrrstrings17sqlnn_co 
mpileModeT3P14sqlrr_cmpl_env + 0x24 
0x0900000008DD8D18 
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P16sq 
lra_cached_varPiPUl + 0x608 
0x0900000008FAC43C 
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_i 
dUiT4PUcT4UsUcP14sqlra_cmpl_enviPiT11_T12_N311_T12_P14SQLP_LOCK_ 
INFOPP16sqlra_ca 
ched_varT12_PUlb + 0x3B0 
.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description text.                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version Version 9.7 Fix Pack 1.               * 
****************************************************************

1.  Remove the like predicate from the query. 
It is not needed because the predicate "c1 like '%'" is always 
TRUE when c1 is non-nullable. 
. 
2.  Add c1 to the SELECT list of the MQT or statistical view. 
3.  Drop the MQT or statistical view that matches to the query.

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
 

The problem is first fixed in DB2 Version 9.7 Fix Pack 1.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

22.10.2009
16.02.2010
16.02.2010

9.7.FP1