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 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 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.