DB2 - Problembeschreibung
Problem IC73653 | Status: Geschlossen |
Unintended Internal commit in CLI stored procedure in federated 2 phase commit environment | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
When a stored procedure uses DB2 CLI interface to make connection to the database in a federated 2 phase commit environment, the DB2 CLI driver may incorrectly issue an implicit COMMIT. (A federated 2 phase commit environment involves a federated server with option "DB2_TWO_PHASE_COMMIT 'Y'"). This results in a premature ending of the transaction. Also this can impact the execution of an Oracle PL/SQL statement in a database with Oracle compatibility enabled even without the explicit call to a procedure that uses DB2 CLI connections. This is due to the fact that internally DB2 uses an internal CLI stored procedure to process the Oracle PL/SQL statement. In the case of the Oracle PL/SQL statement in a federated two phase commit environment, this can result in a number of symptoms: - a transaction that is not complete will be committed - internal package cache locks (internal V) will be released resulting in possible -901 sqlcodes and possible traps. When it results in traps, the stack traces at run time might be different each time. For example, the stack traces can have but not limited to the following two types: sqlribno sqlriSectInvoke sqlrr_process_execute_request sqlrr_execute.fdpr.clone sqljs_ddm_excsqlstt sqljsParseRdbAccessed sqljsParse.fdpr.clone sqljsSqlam sqljsDriveRequests sqljsDrdaAsInnerDriver sqljsDrdaAsDriver RunEDU EDUDriver sqloEDUEntry and run pvm_entry sqloInvokeFnArgs sqloInvokeFnArgs sqlriInvokerTrusted sqlriInvokeInvoker sqlricall sqlriSectInvoke sqlrr_process_execute_request sqlrr_execute.fdpr.clone sqljs_ddm_excsqlstt.fdpr.clone sqljsParseRdbAccessed sqljsParse.fdpr.clone sqljsSqlam sqljsDriveRequests sqljsDrdaAsInnerDriver sqljsDrdaAsDriver RunEDU EDUDriver sqloEDUEntry And before the trap occurs, you might see the following db2diag.log messages in regards to -901 sqlcode and lost of the internal V lock: 2010-11-28-17.35.46.070787+540 I147280A1304 LEVEL: Severe PID : 10220174 TID : 9768 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : DB1 APPHDL : 0-29 APPID: 127.0.0.1.101128081210 AUTHID : DB2INST1 EDUID : 9768 EDUNAME: db2agent (DB1) 0 FUNCTION: DB2 UDB, lock manager, sqlplhld, probe:430 MESSAGE : ZRC=0x82100001=-2112880639=SQLP_NONSEVERE_PRGERR "DPS detects non-severe programming error" DIA8532C An internal processing error has occurred. DATA #1 : String, 24 bytes lock not owned by caller DATA #2 : String, 8 bytes SQLPLHLD CALLSTCK: [0] 0x090000000510D9E8 pdLog + 0xEC [1] 0x090000000666CCAC sqlzSetAndLog901 + 0x1A8 [2] 0x090000000460B1CC sqlzSetAndLog901@glueD33 + 0xC4 [3] 0x0900000005368D24 sqlplhld__FP8sqeAgentP14SQLP_LOCK_INFO + 0x44 [4] 0x09000000052C2CE8 sqlplhld__FP8sqeAgentP14SQLP_LOCK_INFO@glueC55 + 0x74 [5] 0x0900000004BAC970 sqlra_close_sections__FP8sqlrr_cb + 0x458 [6] 0x0900000005041B80 sqlra_close_sections__FP8sqlrr_cbUiT2 + 0x104 [7] 0x090000000503FC90 sqlrr_cleanup_tran_before_DPS__FP8sqlrr_cbiN62PiT9b + 0x204 [8] 0x0900000004E91684 sqlrrcom__FP8sqlrr_cbiT2 + 0x11C [9] 0x09000000050438FC sqlrr_commit__FP14db2UCinterface + 0xB0 2010-11-28-17.35.46.071364+540 I148585A808 LEVEL: Error PID : 10220174 TID : 9768 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : DB1 APPHDL : 0-29 APPID: 127.0.0.1.101128081210 AUTHID : DB2INST1 EDUID : 9768 EDUNAME: db2agent (DB1) 0 FUNCTION: DB2 UDB, lock manager, sqlplhld, probe:430 DATA #1 : SQLP_LOCK_INFO, PD_TYPE_SQLP_LOCK_INFO, 96 bytes lockname 0000008A0000000A000130A056 SQLP_VARIATION pLRB 70000003f3f2b00 prevIntent NON curIntent ..S intent ..S duration 1 rlInFlags 0x00000001 rlOutFlags 0x00000000 cursorBitmap 0x40000000 rrIIDin 0 rrIIDout 0 rlUserDataOut.UNKNOWN 0000 0000 0000 0000 ........ rlUserDataIn.UNKNOWN 0700 0001 D5E9 0080 ........ <messages snippet> 2010-11-28-17.35.46.071857+540 I151104A865 LEVEL: Error PID : 10220174 TID : 9768 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : DB1 APPHDL : 0-29 APPID: 127.0.0.1.101128081210 AUTHID : DB2INST1 EDUID : 9768 EDUNAME: db2agent (DB1) 0 FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 24 sqlerrmc: lock not owned by caller sqlerrp : SQLPL00F sqlerrd : (1) 0x82100001 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFE52 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * Users running a CLI stored procedure in a 2 phase commit * * federated environment( i.e a federated server with option * * "DB2_TWO_PHASE_COMMIT 'Y'"). This issue has been known to * * impact the execution of an Oracle PL/SQL statement in a * * database with Oracle compatibility enabled. * **************************************************************** * PROBLEM DESCRIPTION: * * See above Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 4 * **************************************************************** | |
Local-Fix: | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in Version 9.7 Fix Pack 4 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 04.01.2011 12.05.2011 12.05.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP4 | |
Problem behoben lt. FixList in der Version | |
9.7.0.4 |