Fixes are available
DB2 Version 9.5 Fix Pack 3b for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 4a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
For EXPLAIN statement, we support DECLARE CURSOR FOR. <declare_cursor> grammer is <SQL_query_stmt> | <stmt_name>. We should not allow <stmt_name> for EXPLAIN statement as it is not supported. When stmt_name is used, db2 is crashing. db2diag.log output: 2007-04-04-16.54.44.143858+060 I17361A586 LEVEL: Error PID : 2932 TID : 1 PROC : db2agent (FK1XAT06) 0 INSTANCE: fk1xat06 NODE : 000 DB : FK1XAT06 APPHDL : 0-1004 APPID: J547886A.C852.070404153608 FUNCTION: DB2 UDB, base sys utilities, sqleagnt_sigsegvh, probe:10 MESSAGE : Error in agent servicing application with APPLICATION NAME: DATA #1 : Hexdump, 18 bytes 0x00000002001AA6D2 : 6462 326A 6363 5F61 7070 6C69 6361 7469 db2jcc_applicati 0x00000002001AA6E2 : 6F6E on -- skipped -- 2007-04-04-16.54.44.226778+060 I24023A1063 LEVEL: Severe PID : 2932 TID : 1 PROC : db2agent (FK1XAT06) 0 INSTANCE: fk1xat06 NODE : 000 DB : FK1XAT06 APPHDL : 0-1004 APPID: J547886A.C852.070404153608 MESSAGE : RDS UCINTFC: pStatementText = DATA #1 : Hexdump, 134 bytes 4558 504C 4149 4E20 504C 414E 2053 4554 EXPLAIN PLAN SET 2051 5545 5259 4E4F 203D 2031 3520 5345 QUERYNO = 15 SE 5420 5155 4552 5954 4147 203D 2027 6130 T QUERYTAG = 'a0 6561 3038 3638 6663 3166 3335 6364 3331 ea0868fc1f35cd31 3438 2720 464F 5220 6465 636C 6172 6520 48' FOR declare 635F 746D 7063 6F6D 6D61 6E64 2063 7572 c_tmpcommand cur 736F 7220 2077 6974 6820 7265 7475 726E sor with return 2074 6F20 6361 6C6C 6572 2066 6F72 2073 to caller for s 5F73 746D 6E74 _stmnt -- skipped -- 2007-04-04-16.54.44.604513+060 E82799A1012 LEVEL: Severe PID : 16521 TID : 1 PROC : db2ipccm 0 INSTANCE: fk1xat06 NODE : 000 FUNCTION: DB2 UDB, oper system services, sqloEDUSIGCHLDHandler, probe:50 DATA #1 : <preformatted> Detected the death of an EDU with process id 2932 The signal number that terminated this process was 11 Look for trap files (t2932.*) in the dump directory CALLSTCK: [0] 0x00000001013D1B68 sqloSetNonCriticalEDUFlag + 0x558 [1] 0xFFFFFFFF7BCCD9BC _thr_slot_offset + 0x5B0 [2] 0xFFFFFFFF7BCC1D6C _sema_post + 0x628 [3] 0xFFFFFFFF7BCB88CC _msgrcv + 0x78 [4] 0xFFFFFFFF7BCC0464 msgrcv + 0x6C [5] 0x0000000101409370 __1cQsqlorqueInternal6FpnNsqlo_que_desc_pnMSQdD LO_MSG_HDR_ii_i_ + 0x14C [6] 0x00000001014098EC sqlorque2 + 0x104 [7] 0x000000010048069C __1cVsqlccipcconnmgr_child6FpcI_h_ + 0x3A4 [8] 0x00000001013D31FC __1cNsqloCreateEDU6FpFpcI_v0LpnNSQdDLO_EDU_INFO _pi_i_ + 0x29C [9] 0x00000001013D1F04 sqloSpawnEDU + 0x1DC Trap output: <StackTrace> __1cWsqlnq_qur_end_colnames6FpnDloc__i_(0x102d10f60,0x102bcd800, 0x0,0x4e __1cJsqlnq_sem6FppnIstknode__inKactiontype_pCpnDloc__i_(0x102d31 1d0,0x5, __1cWsqlnq_sem_explain_stmt6FppnIstknode__inKactiontype_pCpnDloc __i_(0x1 __1cMsqlnp_smactn6FpnIsqlnp_cb_i_i_(0x102d31080,0x0,0x0,0x0,0x0, 0x8000) __1cMsqlnp_parser6FpnIsqlnp_cb__i_(0x102d31080,0x102d3dc80,0x1,0 x3,0x102 __1cKsqlnp_main6FpnMsqlnq_string_hppnJsqlnq_qur__i_(0xffffffff7f ff0238,0 __1cKsqlnn_cmpl6FpnUsqle_agent_privatecb_pnLsqlrrstring_hnRsqlnn _compile __1cKsqlnn_cmpl6FpnUsqle_agent_privatecb_pnLsqlrrstring_hnRsqlnn _compile __1cRsqlra_compile_var6FpnIsqlrr_cb_pnOsqlra_cmpl_env_pCiHiiiiip nQsqlra __1cOsqlra_find_var6FpnIsqlrr_cb_pnRsqlra_cached_stmt_nNsqlra_st mt_id_HI __1cNsqlra_get_var6FpnIsqlrr_cb_ipiib2_i_(0x102c67e80,0xffffffff 7fff19c0 __1cXsqlrr_execute_immediate6FpnIsqlrr_cb_i_i_(0x102c67e80,0x0,0 x102c899 __1cOsqlrr_execimmd6FpnHUCintfc__i_(0x102c6f9a0,0x0,0x2b07000,0x 0,0x0,0x The following will trap: "EXPLAIN PLAN SET QUERYNO = 15 SET QUERYTAG = 'a0ea0868fc1f35cd3148' FOR declare c_tmpcommand cursor with return to caller for s_stmnt;"
Local fix
Remove and do not use stmt_name in EXPLAIN statement.
Problem summary
For EXPLAIN statement, we support DECLARE CURSOR FOR. <declare_cursor> grammer is <SQL_query_stmt> | <stmt_name>. We should not allow <stmt_name> for EXPLAIN statement as it is not supported. When stmt_name is used, db2 is crashing. db2diag.log output: 2007-04-04-16.54.44.143858+060 I17361A586 LEVEL: Error PID : 2932 TID : 1 PROC : db2agent (FK1XAT06) 0 INSTANCE: fk1xat06 NODE : 000 DB : FK1XAT06 APPHDL : 0-1004 APPID: J547886A.C852.070404153608 FUNCTION: DB2 UDB, base sys utilities, sqleagnt_sigsegvh, probe:10 MESSAGE : Error in agent servicing application with APPLICATION NAME: DATA #1 : Hexdump, 18 bytes 0x00000002001AA6D2 : 6462 326A 6363 5F61 7070 6C69 6361 7469 db2jcc_applicati 0x00000002001AA6E2 : 6F6E on -- skipped -- 2007-04-04-16.54.44.226778+060 I24023A1063 LEVEL: Severe PID : 2932 TID : 1 PROC : db2agent (FK1XAT06) 0 INSTANCE: fk1xat06 NODE : 000 DB : FK1XAT06 APPHDL : 0-1004 APPID: J547886A.C852.070404153608 MESSAGE : RDS UCINTFC: pStatementText = DATA #1 : Hexdump, 134 bytes 4558 504C 4149 4E20 504C 414E 2053 4554 EXPLAIN PLAN SET 2051 5545 5259 4E4F 203D 2031 3520 5345 QUERYNO = 15 SE 5420 5155 4552 5954 4147 203D 2027 6130 T QUERYTAG = 'a0 6561 3038 3638 6663 3166 3335 6364 3331 ea0868fc1f35cd31 3438 2720 464F 5220 6465 636C 6172 6520 48' FOR declare 635F 746D 7063 6F6D 6D61 6E64 2063 7572 c_tmpcommand cur 736F 7220 2077 6974 6820 7265 7475 726E sor with return 2074 6F20 6361 6C6C 6572 2066 6F72 2073 to caller for s 5F73 746D 6E74 _stmnt -- skipped -- 2007-04-04-16.54.44.604513+060 E82799A1012 LEVEL: Severe PID : 16521 TID : 1 PROC : db2ipccm 0 INSTANCE: fk1xat06 NODE : 000 FUNCTION: DB2 UDB, oper system services, sqloEDUSIGCHLDHandler, probe:50 DATA #1 : <preformatted> Detected the death of an EDU with process id 2932 The signal number that terminated this process was 11 Look for trap files (t2932.*) in the dump directory CALLSTCK: [0] 0x00000001013D1B68 sqloSetNonCriticalEDUFlag + 0x558 [1] 0xFFFFFFFF7BCCD9BC _thr_slot_offset + 0x5B0 [2] 0xFFFFFFFF7BCC1D6C _sema_post + 0x628 [3] 0xFFFFFFFF7BCB88CC _msgrcv + 0x78 [4] 0xFFFFFFFF7BCC0464 msgrcv + 0x6C [5] 0x0000000101409370 __1cQsqlorqueInternal6FpnNsqlo_que_desc_pnMSQdD LO_MSG_HDR_ii_i_ + 0x14C [6] 0x00000001014098EC sqlorque2 + 0x104 [7] 0x000000010048069C __1cVsqlccipcconnmgr_child6FpcI_h_ + 0x3A4 [8] 0x00000001013D31FC __1cNsqloCreateEDU6FpFpcI_v0LpnNSQdDLO_EDU_INFO _pi_i_ + 0x29C [9] 0x00000001013D1F04 sqloSpawnEDU + 0x1DC Trap output: <StackTrace> __1cWsqlnq_qur_end_colnames6FpnDloc__i_(0x102d10f60,0x102bcd800, 0x0,0x4e __1cJsqlnq_sem6FppnIstknode__inKactiontype_pCpnDloc__i_(0x102d31 1d0,0x5, __1cWsqlnq_sem_explain_stmt6FppnIstknode__inKactiontype_pCpnDloc __i_(0x1 __1cMsqlnp_smactn6FpnIsqlnp_cb_i_i_(0x102d31080,0x0,0x0,0x0,0x0, 0x8000) __1cMsqlnp_parser6FpnIsqlnp_cb__i_(0x102d31080,0x102d3dc80,0x1,0 x3,0x102 __1cKsqlnp_main6FpnMsqlnq_string_hppnJsqlnq_qur__i_(0xffffffff7f ff0238,0 __1cKsqlnn_cmpl6FpnUsqle_agent_privatecb_pnLsqlrrstring_hnRsqlnn _compile __1cKsqlnn_cmpl6FpnUsqle_agent_privatecb_pnLsqlrrstring_hnRsqlnn _compile __1cRsqlra_compile_var6FpnIsqlrr_cb_pnOsqlra_cmpl_env_pCiHiiiiip nQsqlra __1cOsqlra_find_var6FpnIsqlrr_cb_pnRsqlra_cached_stmt_nNsqlra_st mt_id_HI __1cNsqlra_get_var6FpnIsqlrr_cb_ipiib2_i_(0x102c67e80,0xffffffff 7fff19c0 __1cXsqlrr_execute_immediate6FpnIsqlrr_cb_i_i_(0x102c67e80,0x0,0 x102c899 __1cOsqlrr_execimmd6FpnHUCintfc__i_(0x102c6f9a0,0x0,0x2b07000,0x 0,0x0,0x The following will trap: "EXPLAIN PLAN SET QUERYNO = 15 SET QUERYTAG = 'a0ea0868fc1f35cd3148' FOR declare c_tmpcommand cursor with return to caller for s_stmnt;"
Problem conclusion
First fixed in DB2 UDB Version 9.5, FixPak 1
Temporary fix
Remove and do not use stmt_name in EXPLAIN statement.
Comments
APAR Information
APAR number
IZ15264
Reported component name
DB2 UDB ESE SOL
Reported component ID
5765F4102
Reported release
950
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2008-02-10
Closed date
2008-05-12
Last modified date
2008-05-12
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
DB2 UDB ESE SOL
Fixed component ID
5765F4102
Applicable component levels
R950 PSN
UP
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950"}]
Document Information
Modified date:
03 October 2021