DB2 - Problembeschreibung
Problem IC83964 | Status: Geschlossen |
CRASH WHEN TRIGGER BODY HAS A UPDATE/DELETE STATEMENT WHICH HAS AN EXISTENTIAL SUBQUERY THAT REFERENCES THE TABLE BEING MODIFIED | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
Conditions for hitting the issue: - DPF environment only - trigger body has a UPDATE/DELETE statement which has an existential subquery that references the table being modified, i.e: CREATE TRIGGER schema.triggername AFTER UPDATE OF col1name ON schema.tablename REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE schema.tablename SET col1name = NEW_ROW.col1name + 1 WHERE col2name IN (SELECT col2name FROM schema.tablename WHERE col3name = NEW_ROW.col2name) ; END DB2 FODC may capture 2 types of events: - FODC_Trap - FODC_AppErr FODC_Trap stacks similar to the following: 0x0900000028C522C4 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x14A4 0x090000002932F460 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x544 ... 0x090000002932F460 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x544 0x0900000028C57BA4 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators@glue1352 + 0xB8 0x0900000028C5226C sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x144C 0x090000002932F460 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x544 ... 0x090000002932F460 sqlno_post_pass__FP13sqlno_globalsP19sqlno_plan_operatorPP19sqln o_plan_operatorPifiP21sqlno_mpp_postpass_cbT613SQLNN_BOOLEANT4P9 sqlno_setPUiP18sqlno_join_contextP34sqlnoDiagnosticsExpensiveOpe rators + 0x544 0x090000002932EC60 sqlno_final_phase__FP13sqlno_globals + 0x1F4 0x0900000029348B2C sqlno_exe__FP9sqlnq_qur + 0x630 0x0900000029348448 sqlno_exe__FP9sqlnq_qur@glue17B0 + 0x88 0x09000000293472EC sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq lrr_cmpl_enviT7PP9sqlnq_qur + 0x544 0x09000000291FC5C8 sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq lrr_cmpl_env + 0x40 0x0900000029322E1C sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_ LOCK_INFOP16sqlra_cached_varPiPUl + 0x7F4 0x09000000293205B8 sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14 SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0x5E0 0x090000002931D1B0 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x708 0x090000002931A470 sqlrr_execute_immediate__FP8sqlrr_cbi + 0x140 0x090000002931A210 sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0x25C 0x0900000029319EBC sqljs_ddm_excsqlimm__FP14db2UCinterfaceP13sqljDDMObject + 0x850 0x09000000293195FC sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC interface + 0x1C 0x090000002911F71C .sqljsParse.fdpr.clone.77__FP13sqljsDrdaAsCbP14db2UCinterfaceP8s qeAgentb + 0x240 0x0900000029107D44 @63@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xAA8 0x0900000028EFC658 @63@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA0 0x0900000028EFC2CC @63@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x260 0x0900000028EFBD94 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T + 0x1EC 0x0900000028FDA3D8 RunEDU__8sqeAgentFv + 0x298 0x0900000028FCFD58 EDUDriver__9sqzEDUObjFv + 0xE4 0x0900000028FD7CFC sqloEDUEntry + 0x260 FODC_AppErr stacks similar to the following: 0x0900000000A9E6B0 pthread_kill + 0xB0 0x0900000025603890 sqloDumpEDU + 0x34 0x090000002509CAB8 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2 + 0xFC 0x0900000025B6D1B8 sqldDumpContext__FP9sqeBsuEduiN42PCcPvT2@glue5A7 + 0x98 0x0900000025BDAEDC sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x36C 0x0900000029284010 sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x50 0x0900000025E0AAC4 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x238 0x09000000264D053C sqlnn_erds__FiN41e + 0x33C 0x0900000026F17508 sqlnn_erds__FiN41e@glue6FE + 0xC4 0x0900000027936CD0 sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq lno_apcbCUiPP19sqlno_plan_operator + 0x1F4 0x09000000263A8D08 sqlno_copy_plan_map__FP13sqlno_globalsP9sqlno_qtbP20sqlno_qun2pl an_arrayP10sqlno_apcbCUiPP20sqlno_qun2plan_array + 0x1CC 0x09000000263AA5F8 copy_plan_args__22sqlno_plan_args_filterFP13sqlno_globalsP9sqlno _qtbP10sqlno_apcbCUiP22sqlno_plan_args_filter + 0x98 0x0900000024FD85EC sqlno_copy_function_args__FCP13sqlno_globalsCP9sqlno_qtbCP10sqln o_apcbCUiP19sqlno_plan_functionT5 + 0xA8 0x090000002933AB34 sqlno_copy_plan__FCP13sqlno_globalsCP19sqlno_plan_operatorCP10sq lno_apcbCUiPP19sqlno_plan_operator + 0x190 0x0900000029339DB8 sqlno_crule_save_plans__FP13sqlno_globalsP13sqlno_context9sqlno_ setP14sqlno_planlistP10sqlno_hint + 0x56C 0x09000000257D9E44 sqlno_crule_save_plans__FP13sqlno_globalsP13sqlno_context9sqlno_ setP14sqlno_planlistP10sqlno_hint@glue1366 + 0x78 0x0900000026FE2450 sqlno_crule_pipe__FP13sqlno_globalsP9sqlno_qtbP9sqlnq_qun9sqlno_ set + 0x110 0x0900000029335110 sqlno_plan_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq_q un + 0x1EC 0x0900000029343D1C sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o pr + 0x808 0x090000002934339C sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq_q un + 0xE8 0x0900000029343838 sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o pr + 0x324 ... 0x090000002934339C sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq_q un + 0xE8 0x0900000029343838 sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o pr + 0x324 0x0900000029342760 sqlno_top_qtb__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurP9sqlnq_qt b + 0xD4 0x0900000029342300 sqlno_each_qur__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurT3 + 0x3E8 0x0900000029341BD8 sqlno_scan_qgm__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qur + 0x214 0x0900000028F6DD18 sqlno_planning_scan__FP13sqlno_globalsP9sqlnq_quri + 0xA8 0x0900000029349634 sqlno_planning_phase__FP13sqlno_globalsP9sqlnq_quriT3 + 0x26C 0x0900000029348ACC sqlno_exe__FP9sqlnq_qur + 0x5D0 0x0900000029348448 sqlno_exe__FP9sqlnq_qur@glue17B0 + 0x88 0x09000000293472EC sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq lrr_cmpl_enviT7PP9sqlnq_qur + 0x544 0x09000000291FC5C8 sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq lrr_cmpl_env + 0x40 0x0900000029322E1C sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_ LOCK_INFOP16sqlra_cached_varPiPUl + 0x7F4 0x09000000293205B8 sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14 SQLP_LOCK_INFOPP16sqlra_cached_varT12_PUlb + 0x5E0 0x090000002931D1B0 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x708 0x090000002931A470 sqlrr_execute_immediate__FP8sqlrr_cbi + 0x140 0x090000002931A210 sqlrr_execimmd__FP14db2UCinterfaceP16db2UCprepareInfo + 0x25C 0x0900000029319EBC sqljs_ddm_excsqlimm__FP14db2UCinterfaceP13sqljDDMObject + 0x850 0x09000000293195FC sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC interface + 0x1C 0x090000002911F71C .sqljsParse.fdpr.clone.77__FP13sqljsDrdaAsCbP14db2UCinterfaceP8s qeAgentb + 0x240 0x0900000029107D44 @63@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xAA8 0x0900000028EFC658 @63@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xA0 0x0900000028EFC2CC @63@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x260 0x0900000028EFBD94 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T + 0x1EC 0x0900000028FDA3D8 RunEDU__8sqeAgentFv + 0x298 0x0900000028FCFD58 EDUDriver__9sqzEDUObjFv + 0xE4 0x0900000028FD7CFC sqloEDUEntry + 0x260 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 9.7 fixpack 7 * **************************************************************** | |
Local-Fix: | |
The workaround would be to inject an always-true predicate (that references a non-deterministic function) of the subquery in the UPDATE statement of the trigger body: CREATE TRIGGER schema.triggername AFTER UPDATE OF col1name ON schema.tablename REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE schema.tablename SET col1name = NEW_ROW.col1name + 1 WHERE col2name IN (SELECT col2name FROM schema.tablename WHERE col3name = NEW_ROW.col2name and rand() > -1) ; END | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in DB2 version 9.7 fixpack 7 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC88323 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 06.06.2012 31.10.2012 31.10.2012 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP7 | |
Problem behoben lt. FixList in der Version | |
9.7.0.7 |