XA transaction support for commitment control

IBM Navigator for i and XA transactions

IBM Navigator for i supports the management of XA transactions as Global Transactions.

A global transaction might contain changes both outside and within Db2 for i. A global transaction is coordinated by an external transaction manager using the Open Group XA architecture, or using another similar architecture. An application commits or rolls back a global transaction using interfaces provided by the transaction manager. The transaction manager uses commit protocols defined by the XA architecture, or by another architecture, to complete the transaction. Db2 for i acts as an XA resource manager when participating in a global transaction. There are two types of global transactions:

• Transaction-scoped locks: Locks acquired on behalf of the transaction are scoped to the transaction. The transaction can move from one job or thread to another.
• Job-scoped locks: Locks acquired on behalf of the transaction are scoped to the job. The transaction cannot move from the job that started it.

Considerations for XA transactions

The XA APIs for transaction-scoped locks are recommended for new users of the XA transaction support. The XA APIs for job-scoped locks will continue to be supported, but no longer have any advantages over the XA APIs for transaction-scoped locks. The XA APIs for transaction-scoped locks have fewer restrictions and better performance in the following situations:

• If multiple SQL connections are ever used to work on a single XA transaction branch.
• If a single SQL connection is used to work on multiple, concurrent XA transaction branches.

In these situations, a separate job must be started to run XA transaction branches when you use the XA APIs for Job Scoped Locks.

Understand the following considerations and restrictions before using Db2 for i as an RM. The term thread refers to either a job that is not thread capable, or a single thread within a thread-capable job.

The following considerations apply to both transactions with transaction-scoped locks and transactions with job-scoped locks unless noted otherwise.

Db2 for i considerations

• XA transactions against a local database must be performed in jobs that are running in SQL server mode. For such transactions, if the xa_open() or db2xa_open() API is used in a job that is not already running in SQL server mode, SQL server mode is implicitly started. You can refer to XA APIs for restrictions on the supported database interfaces.
• XA transactions against a remote database are required to use SQL server mode when you use the XA APIs for job-scoped locks. However, server mode is optional for XA transactions against a remote database when you use the XA APIs for transaction-scoped locks. Furthermore, changes to DDM files using traditional IBM i database access methods are allowed within XA transactions against a remote database when SQL server mode is not used.
• During the XA API invocations, the XA specification reports any errors that are detected by Db2 for i through return codes. Diagnostic messages are left in the job log when the meaning of the error cannot be determined from the return code alone.

Embedded SQL considerations

• In order to use a Structured Query Language (SQL) connection for XA transactions, you must use the xa_open() or db2xa_open() application programming interface (API) before the SQL connection is made. The relational database that will be connected to must be passed to the xa_open() or db2xa_open() API by the xainfo parameter. The user profile and password to be used in the job that the connection is routed to might be passed to the xa_open() or db2xa_open() API. If it is not passed, the profile uses the one that was specified or used as the default during the connection attempt.
  Note: The following consideration applies only to transactions with job-scoped locks.
• If embedded SQL is used to perform XA transactions, the work performed for each connection is routed to a different job, even if the connections are made in the same thread. This is different than SQL server mode without XA, where work performed for all connections in a single thread is routed to the same job. This is because the XA specification requires a separate prepare, commit or rollback call for each resource manager instance.
  Note: The following consideration applies only to transactions with job-scoped locks.
• If embedded SQL is used to perform XA transactions, only one connection per relational database can be made per thread. Whenever the thread is not actively associated with a transaction branch, work requested over one of the thread's connections will cause the RM to use the TM's ax_reg() exit program to determine whether the work is to start, resume or join a transaction branch.

  If the work is to start a transaction branch, it is performed over that thread's connection to the corresponding relational database.

  If the work is to join a transaction branch, it is rerouted over the connection to the corresponding relational database that was made in the thread that started the transaction branch. Note that the system does not enforce that the user profile for that connection is the same as the one for the connection of the joining thread. The TM is responsible to ensure that this is not a security concern. Typical TMs use the same user profile for all connections. This user profile is authorized to all data that is managed by the TM. Further security of access to this data is managed by the TM or AP instead of using the standard IBM i security techniques.

  Note: The following consideration applies only to transactions with job-scoped locks.
• If the work is to resume a transaction branch, the connection that is used depends on whether the suspended transaction branch association was established by starting or joining the transaction branch.

  Subsequent work is performed over the same connection until the db2xa_end() API is used to suspend or end the thread's association with that transaction branch.

CLI considerations

• If the CLI is used to perform XA transactions, more than one connection might be made in the same thread after the db2xa_open() API is used. The connections can be used in other threads to perform XA transactions, as long as those other threads first use the db2xa_open() API with the same xainfo parameter value.
  Note: The following consideration applies only to transactions with job-scoped locks.
• If the CLI is used to perform XA transactions, the connection that is used to start a transaction branch must be used for all work on that transaction branch. If another thread is to join the transaction branch, the connection handle for the connection used to start the transaction branch must be passed to the joining thread so that it can perform work over that same connection. Likewise, if a thread is to resume the transaction branch, the same connection must be used.

  Because CLI connection handles cannot be used in a different job, the join function is limited to threads running in the same job that started the transaction branch when the CLI is used.

Remote relational database considerations

• XA connections to a remote relational database are supported only if the relational database resides on a system that supports Distributed Unit of Work (DUW) DRDA connections. This includes IBM i products that run Distributed Relational Database Architecture (DRDA) over SNA LU 6.2 conversations, or that use V5R1 or later when running DRDA using TCP/IP connections. This also includes other platforms that support DRDA over SNA LU 6.2 or that support the XA protocol using DRDA over TCP/IP.
• Before using the XA join function, the db2xa_open() API must be used in the joining thread. The same relational database name and RMID must be specified on the db2xa_open() API in both the thread that started the transaction branch and the joining thread. If the transaction branch is active when a join is attempted, the joining thread is blocked. The joining thread remains blocked until the active thread suspends or ends its association with the transaction branch.

Recovery considerations

• The manual heuristic commit and rollback support that is provided for all commitment definitions can be used if it becomes necessary to force a transaction branch to commit or roll back while it is in a prepared state.
• The manual heuristic rollback support is also allowed for transaction branches that are in an active or idle state. This support is especially important when a client connection fails after the xa_end API has been used to move the transaction branch to the idle state, but before xa_commit or xa_rollback has been used to complete the transaction. If the client transaction manager does not come back to complete the idle transaction branch after the connection failure, the idle transaction branch is orphaned and will remain pending until the system is restarted or a manual heuristic rollback is performed.
• There is also a manual option to forget transaction branches that are in a heuristically completed state. If a transaction manager does not follow the XA protocol to issue the xa_forget API after receiving a heuristic decision return code, the transaction branch is orphaned and will remain in the heuristically completed state, even through a restart of the system. The transaction branch does not hold any pending changes or locks in this state, but it does consume system storage that is freed when the forget option is exercised.

Transaction branch considerations

• Information about XA transaction branches is shown as part of the commitment control information displayed by IBM Navigator for i and by the Work with Job (WRKJOB), Display Job (DSPJOB), and Work with Commitment Definition (WRKCMTDFN) commands. The TM name, transaction branch state, transaction identifier, and branch qualifier are all shown. The commitment definitions related to all currently active XA transactions can be displayed by using the command WRKCMTDFN JOB(*ALL) STATUS(*XOPEN) or by displaying the Global Transactions folder in IBM Navigator for i.
  Note: The following item applies only to transactions with job-scoped locks.
• If an association between a thread and an existing transaction branch is suspended or ended using the db2xa_end() API, the thread might start a new transaction branch. If the connection used to start the new transaction branch was used earlier to start a different transaction branch and the thread's association with that transaction branch has been ended or suspended by the db2xa_end() API, a new SQL server job might be started. A new SQL server job is needed only if the first transaction branch has not yet been completed by the db2xa_commit() or db2xa_rollback() API. In this case, another completion message SQL7908 is sent to the job log identifying the new SQL server job, just as the connection's original SQL server job was identified when the connection was established. All SQL requests for the new transaction branch are routed to the new SQL server job. When the transaction branch is completed by the db2xa_commit() or db2xa_rollback() API, the new SQL server job is recycled and returned to the prestart job pool.
• A transaction branch is marked Rollback Only in the following situations only for the XA transactions for job-scoped locks:
  • A thread ends when it is still associated with the transaction branch. This includes a thread ending as the result of process termination.
  • The system fails.
• With XA transactions for transaction-scoped locks, a transaction branch is rolled back by the system if any threads are still associated with it when any of the following situations occur:
  • The connection that is related to the transaction branch is ended.
  • The job that started the transaction branch is ended.
  • The system fails.
  Note: The following consideration applies only to transactions with job-scoped locks.
• There is one situation where a transaction branch will be rolled back by the system, regardless of whether there are still associated threads. This occurs when the SQL server job that the connection's work is being routed to is ended. This can only happen when the End Job (ENDJOB) CL command is used against that job.
• A transaction branch is not affected if no threads have an active association with it when any of the following situations occur. The TM can commit or roll back the transaction branch from any thread that has used the xa_open() or db2xa_open() API with the same xainfo parameter value that was specified in the thread that started the transaction branch.
  • The connection that is related to the transaction branch is ended.
  • A thread or job that performed work for the transaction branch uses the xa_close() or db2xa_close() API.
  • The system fails. In this case, the transaction branch is not affected only if it is in prepared state. If it is in idle state, the system rolls it back.
• When the transaction identifier (XID) of two XA transaction branches have the same global transaction identifier (GTRID), but different branch qualifiers (BQUALs), they are said to be loosely coupled. By default, loosely coupled transaction branches do not share locks. However, when using the XA APIs for transaction-scoped locks, there is an option that allows loosely coupled transactions to share locks.