|
Problem IC83964
|
Status: Closed |
CRASH WHEN TRIGGER BODY HAS A UPDATE/DELETE STATEMENT WHICH HAS AN EXISTENTIAL SUBQUERY THAT REFERENCES THE TABLE BEING MODIFIED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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 Summary: |
****************************************************************
* 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 |
| available fix packs: |
DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in DB2 version 9.7 fixpack 7 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC88323
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.06.2012
31.10.2012
31.10.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.7
|
 |
