Question & Answer
Question
What is the XA (2 phase commit) protocol? What do these messages mean?
Cause
It is used in distributed transaction processing to updates to multiple databases inside one transaction. XA is an open standard and consists of a Transaction Manager (TM) to co-ordinate transactions across multiple databases. Each database is also known as a Resource Manager (RM).
Below is a list of errors and possible causes:
Scenario #1: XA_PROTO /XAER_PROTO
May be caused by an error with the RM.
Possible Resolution:
Check DB2 diagnostic logs to determine if there were any problems reported in the database server.
Default locations listed below.
Windows: C:\ProgramData\IBM\DB2\DB2COPY1\db2dump\db2diag.log
UNIX/Linux: /home/db2inst1/sqllib/db2dump/db2diag.log
Note: On Windows C:\ProgramData is a hidden directory.
Scenario #2 (Windows only): Microsoft DTC 0x8004D00E, 0x8004D01C
These errors originates from the Microsoft Distributed Transaction Coordinator running on the Windows operating system and can be seen in the client side db2diag.log.
0x8004D00E = XACT_E_NOTRANSACTION (The transaction has already been
implicitly or explicitly committed or aborted)
http://msdn.microsoft.com/en-us/library/windows/desktop/dd542644%28v=vs.
85%29.aspx
Client
2014-01-01-08.10.30.748000-420 I2507305H551 LEVEL: Severe
PID : 5384 TID : 4164 PROC : w3wp.exe
INSTANCE: DB2 NODE : 000
APPID : 192.168.22.28.44808.14102212551
FUNCTION: DB2 UDB, XA DTP Support, MicrosoftEnlistWithRM, probe:421
DATA #1 : String, 130 bytes
HRESULT: 8004D00E
HRESULT Message: The transaction has already been implicitly or
explicitly committed or aborted
w3wp.exe is Microsoft Information Server process.
2014-10-22-10.33.15.635000-420 I2547586H419 LEVEL: Severe
PID : 544 TID : 7500 PROC : w3wp.exe
INSTANCE: DB2 NODE : 000
APPID : 192.168.22.28.10765.14102217275
FUNCTION: DB2 UDB, XA DTP Support, MicrosoftXARMCreate, probe:363
DATA #1 : String, 103 bytes
HRESULT: 8004D01C
HRESULT Message: A connection with the transaction manager was lost.
DB2 Server
2014-01-01-08.10.32.382996-420 I298960E3190 LEVEL: Severe
PID : 24294 TID : 140712897144576PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-6345 APPID: 192.168.1.100.34310.14102213062
db2inst1 : CTIAPPP
EDUID : 1436 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rollback, probe:200
MESSAGE : XA Loosely Coupled Transaction: Termination still in XA environment
DATA #1 : Hexdump, 520 bytes
Possible Resolution:
Ensure Network DTC Access enabled
http://technet.microsoft.com/en-us/library/cc753510.aspx
How to troubleshoot MSDTC communication failure (I)
http://blogs.msdn.com/b/asiatech/archive/2011/03/01/how-to-troubleshoot-
msdtc-communication-failure-1.aspx
Scenario #3: XA_NOTA / XAER_NOTA
XA_NOTA means the TM tried to use a XID (XA identifier which identifies global transaction) which was not valid.
db2diag.log will return similar message:
MESSAGE : ZRC=0x80100024=-2146435036=SQLP_NOTA "Transaction was not found"
DIA8036C XA error with request type of "". Transaction was not found.
Possible Resolutions:
If DB2 is the RM, check the db2diag.log to see if there are any messages with timestamps that correlate to the XA_NOTA on TM side. A SQL0911 rc=68 (Rollback due to lock timeout) would cause this issue since the XID becomes invalid once the transaction is rolled back.
Some Java applications or application servers will be configured to use a multi-transport model (multiple connections) in which XA.Start() and XA.End() will execute in Connection A, while XA.Commit() is executed in Connection B for the same XID (XA transaction id). This would return XAER_NOTA when trying to commit the XA transaction using a XID which did not start on the same connection.
Must set JDBC driver property xaNetworkOptimization=false for applications using the multi-transport model. If xaNetworkOptimization=false continues to return errors, also set supportsAsynchronousXARollback=1
Scenario #4: XAER_NOTA and XAER_PROTO causes DB2 server hang
Server deadlatch caused by JDBC XA
Related Information
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Programming Interface - JDBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.5;9.1;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21679037