DB2 Version 10.1 for Linux, UNIX, and Windows

Indoubt transaction management APIs

There might be times where it is useful to be able to query, commit, and roll back indoubt transactions without waiting for the Transaction Manager (TM) to perform a re-sync action. This situation might arise, for example, if the communication line is broken, and an indoubt transaction is tying up needed resources.

A set of APIs is provided for tool writers to perform heuristic functions on indoubt transactions when the resource owner (such as the database administrator) cannot wait for the TM to perform the re-sync operation. For the database manager, these resources include locks on tables and indexes, log space, and storage used by the transaction. Each indoubt transaction also decreases by one the maximum number of concurrent transactions that could be processed by the database manager.

The heuristic APIs have the capability to query, commit, and roll back indoubt transactions, and to cancel transactions that have been heuristically committed or rolled back, by removing the log records and releasing log pages.

Attention: The heuristic APIs should be used with caution and only as a last resort. The TM should drive the re-sync events. If the TM has an operator command to start the re-sync action, it should be used. If the user cannot wait for a TM-initiated re-sync, heuristic actions are necessary.

Although there is no set way to perform these actions, the following guidelines may be helpful:

Use the db2XaListIndTrans function to display the indoubt transactions. They have a status = 'P' (prepared), and are not connected. The gtrid portion of an xid is the global transaction ID that is identical to that in other resource managers (RM) that participate in the global transaction.
Use knowledge of the application and the operating environment to identify the other participating RMs.
If the transaction manager is CICS®, and the only RM is a CICS resource, perform a heuristic rollback.
If the transaction manager is not CICS, use it to determine the status of the transaction that has the same gtrid as does the indoubt transaction.
If at least one RM has committed or rolled back, perform a heuristic commit or a rollback.
If they are all in the prepared state, perform a heuristic rollback.
If at least one RM is not available, perform a heuristic rollback.

If the transaction manager is available, and the indoubt transaction is due to the RM not being available in the second phase, or in an earlier re-sync, the DBA should determine from the TM's log what action has been taken against the other RMs, and then do the same. The gtrid is the matching key between the TM and the RMs.

Do not execute sqlxhfrg unless a heuristically committed or rolled back transaction happens to cause a log full condition. The forget function releases the log space occupied by this indoubt transaction. If a transaction manager eventually performs a re-sync action for this indoubt transaction, the TM could make the wrong decision to commit or to roll back other RMs, because no record was found in this RM. In general, a missing record implies that the RM has rolled back.