Application Development Guide

X/Open XA Interface Programming Considerations

The X/Open^(R) XA Interface is an open standard for coordinating changes to multiple resources, while ensuring the integrity of these changes. Software products known as transaction processing monitors typically use the XA interface, and since DB2 supports this interface, one or more DB2 databases may be concurrently accessed as resources in such an environment. For information about the concepts and implementation of the XA interface support provided by the database manager, refer to the Administration Guide: Planning. To determine if your platform supports the X/Open XA Interface, refer to the Application Building Guide.

Special consideration is required by DB2 when operating in a Distributed Transaction Processing (DTP) environment which uses the XA interface because a different model is used for transaction processing as compared to applications running independent of a TP monitor. The characteristics of this transaction processing model are:

1. Multiple types of recoverable resources (such as DB2 databases) can be modified within a transaction.
2. Resources are updated using two-phase commit to ensure the integrity of the transactions being executed.
3. Application programs send requests to commit or rollback a transaction to the TP monitor product rather than to the managers of the resources. For example, in a CICS environment an application would issue EXEC CICS SYNCPOINT to commit a transaction, and issuing EXEC SQL COMMIT to DB2 would be invalid and unnecessary.
4. Authorization to run transactions is screened by the TP monitor and related software, so resource managers such as DB2 treat the TP monitor as the single authorized user. For example, any use of a CICS transaction must be authenticated by CICS and the access privilege to the database must be granted to CICS and not to the end user who invokes the CICS application.
5. Multiple programs (transactions) are typically queued and executed on a database server (which appears to DB2 to be a single, long-running application program).

Due to the unique nature of this environment, DB2 has special behavior and requirements for applications coded to run in it:

• Multiple databases can be connected to and updated within a unit of work without consideration of distributed unit of work precompiler options or client settings.
• The DISCONNECT statement is disallowed, and will be rejected with SQLCODE -30090 (SQLSTATE 25000) if attempted.
• The RELEASE statement can be used to specify databases connections to release when a transaction is committed, but this is not recommended. If a connection has been released, subsequent transactions should use the SET CONNECTION statement to connect to the database without requiring authorization.
• COMMIT and ROLLBACK statements are not allowed within stored procedures accessed by a TP monitor transaction.
• When two-phase commit flows are explicitly disabled for a transaction (these are called LOCAL transactions in XA Interface terminology) only one database can be accessed within that transaction. This database cannot be a host or AS/400 database that is accessed using SNA connectivity. Local transactions to DB2 for OS/390 Version 5 using TCP/IP connectivity are supported.
• LOCAL transactions should issue SQL COMMIT or SQL ROLLBACK at the end of each transaction, otherwise the transaction will be considered part of the next transaction which is processed.
• Switching between current database connections is done through the use of either SQL CONNECT or SQL SET CONNECTION. The authorization used for a connection cannot be changed by specifying a user ID or password on the CONNECT statement.
• If a database object such as a table, view, or index is not fully qualified in a dynamic SQL statement, it will be implicitly qualified with the single authentication ID that the TP monitor is executing under, rather than user's ID.
• Any use of DB2 COMMIT or ROLLBACK statements for transactions that are not LOCAL will be rejected. The following codes will be returned:
  • SQLCODE -925 (SQLSTATE 2D521) for static COMMIT
  • SQLCODE -926 (SQLSTATE 2D521) for static ROLLBACK
  • SQLCODE -426 (SQLSTATE 2D528) for dynamic COMMIT
  • SQLCODE -427 (SQLSTATE 2D529) for dynamic ROLLBACK
• CLI requests to COMMIT or ROLLBACK are also rejected.
• Handling database-initiated rollback:

  In a DTP environment, if an RM has initiated a rollback (for instance, due to a system error or deadlock) to terminate its own branch of a global transaction, it must not process any more requests from the same application process until a transaction manager-initiated sync point request occurs. This includes deadlocks that occur within a stored procedure. For the database manager, this means rejecting all subsequent SQL requests with SQLCODE -918 (SQLSTATE 51021) to inform you that you must roll back the global transaction with the transaction manager's sync point service such as using the CICS SYNCPOINT ROLLBACK command in a CICS environment. If for some reason you request the TM to commit the transaction instead, the RM will inform the TM about the rollback and cause the TM to roll back other RMs anyway.

• Cursors declared WITH HOLD:

  Cursors declared WITH HOLD are supported in XA/DTP environments for CICS transaction processing monitors.

  In cases where cursors declared WITH HOLD are not supported, the OPEN statement will be rejected with SQLCODE -30090 (SQLSTATE 25000), reason code 03.

  It is the responsibility of the transactions to ensure that cursors specified to be WITH HOLD are explicitly closed when they are no longer required; otherwise they might be inherited by other transactions, causing conflict or unnecessary use of resources.

• Statements which update or change a database are not allowed against databases which do not support two-phase commit request flows. For example, accessing host or AS/400 database servers in environments in which level 2 of DRDA protocol (DRDA2) is not supportable (see Multisite Update with DB2 Connect).
• Whether a database supports updates in an XA environment can be determined at run-time by issuing a CONNECT statement. The third SQLERRD token will have the value 1 if the database is updatable, and otherwise will have the value 2.
• When updates are restricted, only the following SQL statements will be allowed:

       CONNECT 
       DECLARE 
       DESCRIBE 
       EXECUTE IMMEDIATE (where the first token or keyword is SET but 
                          not SET CONSTRAINTS) 
       OPEN CURSOR 
       FETCH CURSOR 
       CLOSE CURSOR 
       PREPARE (where the first token or keyword that is not blank or 
                left parenthesis is SET (other than SET CONSTRAINTS), 
                SELECT, WITH, or VALUES) 
       SELECT...INTO 
       VALUES...INTO
  

  Any other attempts will be rejected with SQLCODE -30090 (SQLSTATE 25000).

  The PREPARE statement will only be usable to prepare SELECT statements. The EXECUTE IMMEDIATE statement is also allowed to execute SQL SET statements that do not return any output value, such as the SET SQLID statement from DB2 Universal Database for OS/390.

• API Restrictions:

  APIs which internally issue a commit in the database and bypass the two-phase commit process will be rejected with SQLCODE -30090 (SQLSTATE 25000). For a list of these APIs, see Multisite Update Restrictions. These APIs are not supported in a multisite update (Connect Type 2).

• Applications should be single-threaded.

  If you intend to develop a multithreaded application, you should ensure that only one thread uses SQL, or use a multiprocess design instead to avoid interleaving of SQL statements from different threads within the same unit of work. If a transaction manager supports multiple processes or multithreading, you should configure it to serialize the threads so that one thread will execute to a sync point before another one begins. An example is the XASerialize option of all_operation in AIX/CICS. For more details about the AIX/CICS XAD file which contains this information, refer to the Administration Guide: Planning.

Note that the above restrictions apply to applications running in TP monitor environment which uses the XA interface. If DB2 databases are not defined for use with the XA interface, these restrictions do not apply, however it is still necessary to ensure that transactions are coded in a way that will not leave DB2 in a state which will adversely affect the next transaction to be run.

Application Linkage

To produce an executable application, you need to link in the application objects with the language libraries, the operating system libraries, the normal database manager libraries, and the libraries of the TP monitor and transaction manager products.