home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
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
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC98622 Status: Closed

CREATE PROCEDURE WILL FAIL WITH SQL0901N "EMPTY PLAN LIST
DETECTED!" ERROR OR CRASH DB2

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
When failed with SQL0901N error, the message is like below: 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it 
returned: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. Subsequent SQL statements can be processed. 
(Reason "Empty plan list detected!".)  LINE NUMBER=24. 
SQLSTATE=58004 
 
db2diag.log prints the following stack: 
 
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, 62 bytes 
An unexpected error was detected during statement compilation. 
DATA #2 : Boolean, 1 bytes 
true 
DATA #3 : Boolean, 1 bytes 
false 
DATA #4 : Boolean, 1 bytes 
false 
DATA #5 : Boolean, 1 bytes 
false 
DATA #6 : Hex integer, 4 bytes 
0x00000000 
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 25 
 sqlerrmc: Empty plan list detected! 
 sqlerrp : SQLNO261 
 sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 
0x00000000 
           (4) 0x00000000      (5) 0xFFFFFF9C      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
DATA #8 : Hex integer, 4 bytes 
0x00000040 
DATA #9 : 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[1700] 
sqlno_crule_mate[400] 
sqlno_crule_save_plans[100] 
sqlno_crule_save_plans[100] 
 
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, 62 bytes 
An unexpected error was detected during statement compilation. 
DATA #2 : Boolean, 1 bytes 
true 
DATA #3 : Boolean, 1 bytes 
false 
DATA #4 : Boolean, 1 bytes 
false 
DATA #5 : Boolean, 1 bytes 
false 
DATA #6 : Hex integer, 4 bytes 
0x00000000 
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCAL    sqlcabc: 136   sqlcode: -901   sqlerrml: 25 
 sqlerrmc: Empty plan list detected! 
 sqlerrp : SQLNO261 
 sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 
0x00000018 
           (4) 0x00000000      (5) 0xFFFFFF9C      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
DATA #8 : Hex integer, 4 bytes 
0x00000040 
DATA #9 : String, 440 bytes 
Compiler error stack for rc = -2144272209: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[510] 
sqlnp_smactn[100] 
sqlnq_proc_create_stmt[490] 
sqlnq_create_proc_end[570] 
psm_gen_program[400] 
psm_ctrl::psm_generate_PVM[300] 
psmPG::finalizePackage[0] 
psmP::genProgram[300] 
psmP::generate(psm_procedu[110] 
psm_node::genOffspring[100] 
psmP::generate(psm_block_n[200] 
psmP::generate(psm_DECLARE[210] 
psmQS::makeSection[300] 
psmPG::addStatement[100] 
 
When crash db2, the trap file has the following stack: 
 
0x090000005DB1ADAC sp__17sqlnq_oprqunchildCFv + 0xC 
0x090000005DB72630 
sqlnr_joinppd_cond__FP14sqlnr_progressP10sqlnr_qrwaPi + 0x270 
0x090000005D8ADE30 
sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres 
s + 0x270 
0x090000005D8B55CC 
sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass + 
0x16C 
0x090000005D8B5A0C sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress 
+ 0x16C 
0x090000005DB083E8 
sqlnr_magic_detail_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 
0xC8 
0x090000005D8AE014 
sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres 
s + 0x454 
0x090000005D8B55CC 
sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass + 
0x16C 
0x090000005D8B5A0C sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress 
+ 0x16C 
0x090000005DADBB68 
sqlnr_endqtb_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 0xC8 
0x090000005D8AE014 
sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres 
s + 0x454 
0x090000005D8B55CC 
sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass + 
0x16C 
0x090000005D8B5A0C sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress 
+ 0x16C 
0x090000005DADCA44 
sqlnr_start_action__FP10sqlnr_qrwaPiP14sqlnr_progress + 0x324 
0x090000005D8AE014 
sqlnr_comp__FPiiP16sqlnr_rule_stateP10sqlnr_qrwaP14sqlnr_progres 
s + 0x454 
0x090000005D8B55CC 
sqlnr_seq__FPiP10sqlnr_qrwaP14sqlnr_progressP12sqlnr_rclass + 
0x16C 
0x090000005D8B5A0C sqlnr_rcc__FiP10sqlnr_qrwaPiP14sqlnr_progress 
+ 0x16C 
0x090000005CCDD7DC sqlnr_exe__FP9sqlnq_qur + 0x9DC 
0x090000005E7543D0 
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq 
lrr_cmpl_enviT7PP9sqlnq_qur + 0x6C10 
0x090000005E757BA4 
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq 
lrr_cmpl_env + 0x64 
0x09000000600072F4 
sqlrr_cmpl__FP8sqlrr_cbP11sqlrrstrings17sqlnn_compileModeUcP5sql 
daPiPUiPP9sqlri_shdP11compileInfo + 0x394 
0x090000005B7A00B8 
sqlrr_compile__FP14db2UCinterfaceP11compileInfo + 0x6B8 
0x090000006091CA9C 
addStatement__13psmPackageGenFPciT2P18psmPackageVariableT2RUs + 
0x4FC 
0x0900000060912AD0 
makeSection__14psmQueryStringFP19psmPVMCodeGeneratoriUsR12pvmQue 
ryDesc + 0x5F0 
0x09000000608F47AC 
generate__19psmPVMCodeGeneratorFP23psm_DECLARE_CURSOR_node + 
0x3CC 
0x090000005BF54AD8 
generate__23psm_DECLARE_CURSOR_nodeFP16psmCodeGenerator + 0x98 
0x09000000608FCCB4 
generate__19psmPVMCodeGeneratorFP14psm_block_node + 0x2D4 
0x090000005BF81558 
generate__14psm_block_nodeFP16psmCodeGenerator + 0x98 
0x09000000608E4B88 genOffspring__8psm_nodeFP16psmCodeGenerator + 
0x128 
0x09000000608FD160 
generate__19psmPVMCodeGeneratorFP18psm_procedure_node + 0x180 
0x090000005BF81BD8 
generate__18psm_procedure_nodeFP16psmCodeGenerator + 0x98 
0x090000006090EEB4 
genProgram__19psmPVMCodeGeneratorFP10psmProgram + 0x5F4 
0x090000005BFA65AC psm_generate_PVM__8psm_ctrlFv + 0x16C
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* Users who use db2 v9.7                                       * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to db2 v9.7 fixpack 10 or above                      * 
****************************************************************
Local Fix:
db2set DB2COMPOPT=0,16 
Then restart db2
Solution
The problem is first fixed in db2 v9.7 fixpack 10
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.01.2014
11.11.2014
11.11.2014
Problem solved at the following versions (IBM BugInfos)
9.7.FP10
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.10 FixList