The XA APIs for Job Scoped Locks are:
The following exit functions must be provided by a transaction manager for use by the XA resource manager when the XA APIs for Job Scoped Locks are used:
When using the XA APIs for Job Scoped Locks, the XA thread of control is always considered to be the IBM® i thread from which transactional work is requested, regardless of what SQL connection is used to perform that work.
The following example shows the interactions between the application program, transaction manager, and the XA resource manager during a typical transaction when the XA APIs for Job Scoped Locks are used. The actual interactions that occur during a transaction will vary depending on factors such as the following:
See the X/Open XA Specification for details.
HLL XA XA
Application Transaction Resource
Program Manager Manager
1. tx_open ----------> db2xa_open ---------->
<---------- <-----------
2. tx_begin --------->
<----------
3. <SQL work> ------------------------------>
4. <----------- Call ax_reg
XID xxx
----------->
<---------------------------------
.
5. .
.
6. tx_commit --------> db2xa_end ----------->
<-----------
7. db2xa_prepare ------->
<-----------
8. db2xa_commit -------->
<---------- <-----------
When using the XA APIs for Job Scoped Locks, an application that uses the CLI SQL interfaces must use a single connection to perform all work for a transaction branch. This means that if the XA join function is used so that multiple threads work on a single transaction branch, all the joining threads must use the same CLI connection for that work. Since CLI connection handles cannot be shared across jobs, this means that the XA join function can be used only by threads within a single job when using the CLI. This restriction does not apply when the application uses embedded SQL, or when the XA APIs for Transaction Scoped Locks are used. The application must not switch connections while a thread is associated with a transaction branch. If the connection is disconnected while a thread is associated with a transaction branch, the transaction branch will implicitly roll back.
When used with the XA APIs for Job Scoped Locks, some aspects of SQL Server Mode behavior are affected. Traditional SQL Server Mode usage within an application makes a one to one correlation between a connection to the database in the application and to a QSQSRVR prestart job in the QSYSWRK subsystem. All SQL requests made in the application using that connection are executed in the correlated QSQSRVR job. When the connection is closed, the job is recycled and returned to the prestart job pool.
With XA, an application has the ability to start and use separate transaction branches over a single database connection.When the XA APIs for Job Scoped Locks are used to start a new transaction branch using a connection that was earlier used for a different transaction branch that has not yet been completed (committed or rolled back), the new transaction branch is assigned its own QSQSRVR job. This means a single connection can be related to multiple QSQSRVR jobs. When a transaction branch that requires a new QSQSRVR job completes, that QSQSRVR job is dissociated from the connection, recycled and returned to the prestart job pool.
If embedded SQL is used and the native DB2 for i security mechanisms are used, the transaction manager must ensure that all work on a transaction branch is performed by jobs or threads using the same user profile. In other words, if the XA join function is used, every joining thread or job must use the same user profile as the thread or job that started the transaction branch; otherwise, a security exposure will exist. This security consideration does not exist when using the XA APIs for Transaction Scoped Locks because the one to one correlation between the connection and the QSQSRVR job is always maintained, regardless of what transaction branch is being worked on.
While this model works well for isolating transactions, the environment may provide some extra work on behalf of the application. Since separate and distinct jobs are in use for each transaction branch, any job/process-scoped resources setup while under one transaction branch will be unavailable once the application has switched to a different transaction branch. A list of the known limitations and restrictions when using this support is included below. This list is not guaranteed to be comprehensive.
The following example demonstrates a scenario where these restrictions may be encountered.
When an application prepares an SQL statement, the resulting statement is stored in a job-scoped system space. This means that, for the example above, statements prepared while working on transaction branch XID1 are not available while working on transaction branch XID2, because the SQL work for the two transaction branches is done in separate QSQSRVR jobs. If the application attempts to use a prepared statement that is not available, the failure symptom would be SQLCODE = -518. (SQL0518 - Prepared statement &1 not found.)
SQL cursors are thread-scoped resources, so they also are not available to the application after switching to a new transaction branch. If an application opens an SQL cursor and changes transaction branches, the cursor may remain open in the QSQSRVR job related to the previous transaction branch depending on how that branch was ended (see SQLHOLD Values). However, the cursor will not be available while working on the new transaction branch. If and when the original transaction branch is resumed, open cursors related to that transaction branch would again become available. Attempting to reference a cursor while executing under a transaction branch other than the one under which the cursor was opened, will result in a failure of SQLCODE = -501. (SQL0501 - Cursor &1 not open.)
When calling a stored procedure that returns result set(s), the application needs to take care to fully process the result sets before changing to a different transaction branch. SQL CLI services that return information about the status of a result set, could return incorrect information if not used in this manner. Examples of SQL CLI APIs that return information based on interim results are SQLNumResultCols(), SQLDescribeCol(), SQLColAttributes() and SQLDescribeParam().
SQL CLI APIs like SQLFetch() and SQLFetchScroll(), which deal directly with the SQL result set cursor, would fail with SQLCODE = -502. (SQL0502 - Cursor &1 already open.)
The SET PATH SQL statement allows the application to designate a path to use for unqualified library access to SQL stored procedures, SQL triggers and SQL UDFs within a dynamic statement. The path is a job-scoped resource, and therefore not available after changing transaction branches. The application should repeat any SET PATH statements after a transaction branch change, if the path will still be needed.
Applications should not change transaction branches while running within an SQL Stored Procedure, an SQL User Defined Function (UDF) or an SQL Trigger program. Results would be unpredictable and no anticipated failure information is available.
Embedded SQL applications that use the QSQCHGDC() system API or SET SCHEMA SQL statement to set up the Dynamic Default Connection will not function correctly because they will not affect the SQL Server Mode job. This has always been a restriction of the SQL Server Mode environment. If encountered, the failure symptom seen by the application would be SQLCODE = -204. (&1 in &2 type *&3 not found.)
Note that SQL CLI users that set the default library using the SQLSetConnectAttr() API with the SQL_ATTR_DBC_DEFAULT_LIB connection attribute will continue to work. SQL CLI connection attributes are still in place after moving to a different transacation branch.