Troubleshooting federated two-phase commit issues
Troubleshooting federated two-phase commit issues is often specific to the data source that is causing the issue.
Applications must handle error codes that indicate that transactions have timed out, specifically -913 and -918 error codes in addition to checking for -911 error codes.
Oracle data source troubleshooting
Try the following methods to troubleshoot issues with Oracle data sources.
- To log information:
Where C:\temp is the full path to the location where you want the log file created. Logging information can significantly slow performance, so log information only when you are troubleshooting issues.
db2 "alter server ora1 options (add XA_OPEN_STRING_OPTIONS '+LogDir=C:\temp+DbgFl=0x7')
- To display trace information, add the following lines to the Oracle client
You can also set TRACE_LEVEL_CLIENT to a lower number, such as 4 or 8. Displaying trace information can significantly slow performance, so enable trace level information only when you are troubleshooting issues.
- To list pending transactions exist on the Oracle data source:
To list the state of a transaction and the ID of a transaction:
select * from dba_pending_transactions where formatid=1177702467; select * from dba_2pc_pending;
select A.STATE, A.LOCAL_TRAN_ID, A.FAIL_TIME, A.GLOBAL_TRAN_ID, B.FORMATID || '.' || B.GLOBALID || '.' || b.BRANCHID as fmt_xid from dba_2pc_pending A, dba_pending_transactions B where A.GLOBAL_TRAN_ID = B.FORMATID || '.' || B.GLOBALID and STATE='prepared' and B.FORMATID=1177702467;
- To resolve transactions manually:
where '4.31.157818' is the Oracle field A.LOCAL_TRAN_ID that corresponds to the XID in GLOBAL_TRAN_ID.
rollback force '4.31.157818'; commit force '10.24.154537'
Sybase data source troubleshooting
Try the following methods to troubleshoot issues with Sybase data sources.
- Check the Sybase XA log file syb_xa_log located in the $SYBASE directory.
- Use the db2diag tool to check the db2diag.log file.
- To check a transaction on the Sybase server:
If you find an invalid or unnecessary transaction, ask the Sybase administrator to delete the transaction.
$ isql -Uuser_name -Ppassword -Sserver_name 1> sp_transactions 2> go