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:
```
db2 "alter server ora1 options (add XA_OPEN_STRING_OPTIONS 
'+LogDir=C:\temp+DbgFl=0x7')
```
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.
To display trace information, add the following lines to the Oracle client sqlnet.ora file:
```
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=C:\temp
```
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:

select * from dba_pending_transactions where formatid=1177702467;
select * from dba_2pc_pending;

To list the state of a transaction and the ID of a transaction:

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:
```
rollback force '4.31.157818';
commit force '10.24.154537'
```
where '4.31.157818' is the Oracle field A.LOCAL_TRAN_ID that corresponds to the XID in GLOBAL_TRAN_ID.

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:
```
$ isql -Uuser_name -Ppassword -Sserver_name 
1> sp_transactions
2> go
```
If you find an invalid or unnecessary transaction, ask the Sybase administrator to delete the transaction.