IBM Books

Administration Guide

Setting Up a Database as a Resource Manager

Each database is defined as a separate resource manager (RM) to the transaction manager (TM), and the database must be identified with an XA open string that has the following syntax:


The database_alias is required to specify the database alias name of the database. This alias name is the same as the database name unless you have explicitly cataloged an alias name after you created the database. The username and password are optional, and, depending on the authentication method, are used to provide authentication information to the database.

When setting up a database as a resource manager, you do not require the XA close string. This string will be ignored by the database manager if it is provided.

A program can access different databases using the SQL CONNECT statement. Each transaction can access one or more databases as described in Chapter 8. "Distributed Databases". Every database to be accessed in the transaction must be defined as a resource manager using an XA open string. If a database is not defined as a resource manager, that database cannot be used within a transaction controlled by an XA-compliant transaction manager.

The database manager allows both non-XA and global transactions to access local and remote instances of the database manager. If all the databases reside on machines separated from the TP Monitor machine, the TP Monitor machine uses the database client to forward the XA and SQL requests to the databases. You must have, at a minimum, the DB2 Client Application Enabler installed on the same machine as the XA Transaction Manager. Database servers that are accessed by applications controlled by the XA Transaction Manager can be either local or remote.

Updating Host or AS/400 Database Servers

Host and AS/400 database servers may be updatable depending upon the architecture of the XA Transaction Manager. If the work and the commit sequence occur within the same DB2 context (typically the same operating system thread), and the work is committed before starting a new transaction, then host and AS/400 database servers can participate in the transaction. See the Embedded SQL Programming Guide for information about the SQL statements that are allowed in this environment.

If you will be updating host or AS/400 database servers, you will require DB2 Connect with the DB2 Syncpoint Manager configured. Refer to the DB2 Connect Enterprise Edition Quick Beginnings manual for instructions.

Database Connection Considerations

The sections that follow describe the database connection considerations:

RELEASE Statement

If a RELEASE statement is used to release a connection to a database, a CONNECT statement, rather than SET CONNECTION, should be used to reconnect to that database.

Transactions Accessing Partitioned Databases (DB2 UDB EEE)

In a partitioned database environment, user data may be partitioned across database partitions. An application accessing the database connects and sends requests to one of the database partitions (the coordinator node). Different applications can connect to different database partitions, and the same application can choose different database partitions for different connections.

For a given transaction executing against a database in a partitioned environment, all access must be through the same database partition. That is, the same database partition must be used from the start of the transaction until (and including) the time that the transaction is committed.

Any transaction executing against the partitioned database must be committed before disconnecting.

Making a Heuristic Decision

An XA-compliant transaction manager (Transaction Processing Monitor) uses a two-phase commit process similar to that used by the DB2 transaction manager as described in "Understanding the Two-Phase Commit Process". The primary difference between the two environments is that the TP Monitor provides the function of logging and controlling the transaction, instead of the DB2 transaction manager and the transaction manager database.

Errors similar to those discussed for the DB2 transaction manager (see "Recovering from Problems During Two-Phase Commit") can occur when using an XA-compliant transaction manager. Similar to the DB2 transaction manager, an XA-compliant transaction manager will attempt to resynchronize indoubt transactions.

If, for some reason, you cannot wait for the transaction manager to automatically resolve the indoubt transaction, there are some actions you can take to manually resolve the states of indoubt transactions. This manual process is sometimes referred to as "making a heuristic decision".

The LIST INDOUBT TRANSACTIONS command using the WITH PROMPTING option (or the use of a related set of APIs) allows you to query, commit, and roll back indoubt transactions. In addition, it also allows you to "forget" transactions that have been heuristically committed or rolled back by removing the log records and releasing the log space. For information about the command and APIs, see the Command Reference and the API Reference manuals.
Note:The LIST INDOUBT TRANSACTIONS command (and APIs) can only be used for Universal Database versions of DB2. Other types of resource managers, including those controlled by DRDA2-compliant database managers may have other ways to query indoubt transactions and to make heuristic decisions for their resources.

You should use this command (or APIs) with extreme caution and as a last resort. The best solution is to wait for the transaction manager to drive the resynchronization process. You could experience data integrity problems if you manually commit or roll back a transaction in one of the participating databases, and the opposite action is taken for another of the databases. Recovering from data integrity problems requires you to understand the application logic, the data changed or rolled back, and then to perform a point-in-time recovery of the database, or manually undo/redo the database changes.

If you cannot wait for the transaction manager to initiate the resynchronization process and you must release the resources tied up by an indoubt transaction, then heuristic operations are necessary. This situation could occur if the transaction manager will not be available for an extended period of time to do the resynchronization and the indoubt transaction is tying up resources that are urgently needed. An indoubt transaction ties up the resources that were associated with this transaction before the transaction manager or resource managers became unavailable. For the database manager, these resources include things such as the locks on tables and indexes, log space, and storage taken up by the transaction. Each indoubt transaction also decreases (by one) the maximum number of concurrent transactions that can be handled by the database.

There are no foolproof ways to perform heuristic operations. You can use the following steps as a guideline:

  1. Connect to the database for which you require all transactions to be complete.
  2. Use the LIST INDOUBT TRANSACTIONS command to display the indoubt transactions. The xid represents the global transaction ID and is identical to the xid used by the transaction manager and by other resource managers participating in this transaction.
  3. For each indoubt transaction, use your knowledge about the application and the operating environment to determine the other participating resource managers.
  4. Determine if the transaction manager is available:

Do not perform the heuristic forget function unless a heuristically committed or rolled back transaction causes a log full condition, as indicated by the Logfull condition in the output of the LIST INDOUBT TRANSACTIONS command. The heuristic forget function releases the log space occupied by this indoubt transaction. The implication is that if a transaction manager eventually performs a resynchronization operation for this indoubt transaction, it could potentially make the wrong decision to commit or roll back other resource managers because there is no log record found for the transaction in this resource manager. In general a "missing" log record implies that the resource manager had rolled back the transaction.

Security Considerations

As mentioned in "Application Program (AP)", the TP monitor pre-allocates a set of server processes and runs the transactions from different users under the IDs of the server processes. To the database, each server process appears as a big application that has many units of work, all being run under the same ID associated with the server process.

For example, in an AIX environment using CICS, when a CICS for AIX region is started up, it is associated with the AIX username with which it is defined. All the CICS Application Server processes are also being run under this CICS for AIX "master" ID, which is usually defined as "cics". CICS users can invoke CICS transactions under their DCE login ID, and while in CICS, they can also change their ID using the CESN signon transaction.(1) In either case, the end user's ID is not available to the RM. Consequently a CICS Application Process might be running transactions on behalf of many users, but they all appear to the RM as if it is a single program with many units of work from the same "cics" ID. Optionally, you may specify a user ID and password on the XA Open string, and that user ID will be used instead of the "cics" ID to connect to the database.

For static SQL statements, there is not much impact because the binder's privileges, not the end user's privileges, are used to access the database. This does mean, however, that the EXECUTE privilege of the database packages must be granted to the server's ID and not the end user's.

For dynamic statements, which have their access authentication done at run-time, this means that the access privileges of the database objects must be granted to the server's ID and not to the actual user of those objects. Instead of relying on the database to control the access of specific users, you must rely on the TP Monitor system to determine which users can run which programs. The server ID must be granted all privileges that its SQL users require.

To determine who has accessed a database table or view, you can perform the following steps:

  1. From the SYSCAT.PACKAGEDEP catalog view, obtain a list of all packages that depend on the table or view.
  2. Determine the names of the server programs (for example, CICS programs) that correspond to these packages through the naming convention used in your installation.
  3. Determine the client programs (for example, CICS transaction IDs) that could invoke these programs, and then use the TP Monitor's log (for example, CICS log) to determine who had run these transactions or programs and at what times.

Configuration Considerations

You should consider the values of the following configuration parameters when you are setting up your TP Monitor environment:

XA Function Supported

DB2 Universal Database supports the XA91 specification defined in X/Open CAE Specification Distributed Transaction Processing: The XA Specification manual, with the following exceptions:

XA Switch Usage and Location

As required by the XA interface, the database manager provides a db2xa_switch external C variable of type xa_switch_t to return the XA switch structure to the TM. Other than the addresses of the various XA functions, the following fields are returned:


The product name of the database manager: for example, DB2 for AIX


Explicitly states that DB2 UDB uses dynamic registration and the TM should not use association migration. Also implicitly states that asynchronous operation is not supported.

Must be zero.

XA Open and Close Strings Usage

The database manager open string has the following syntax:


The database_alias is required to specify the database alias name of the database. This alias name is the same as the database name unless you have explicitly cataloged an alias name after you created the database. The username and password are optional, and are used to provide authentication information to the database if the database is set up with authentication=SERVER.

The database manager does not use the XA close string and its content will be ignored.

Using the DB2 Universal Database XA Switch

The XA architecture requires that a Resource Manager (RM) provide a switch that gives the XA Transaction Manager (TM) access to the resource manager's xa_ routines. An RM's switch uses a structure called xa_switch_t. The switch contains the RM's name, non-null pointers to the RM's xa entry points, a flag, and a version number.

See the following sections for information on how to use the switch on different platforms:

For a C sample program, see "Example C Code".

UNIX Platforms

DB2 UDB's switch can be obtained in any of the following ways:

With either method, you must link your application with libdb2.

OS/2 Platform

DB2 UDB's switch can be obtained in any of the following ways:

With either method, you must link your application with db2app.lib.

Windows NT Platform

The interface to the db2xa_switch data structure is different for DB2 UDB for Windows NT because of operating system differences.

The pointer to the xa_switch structure, db2xa_switch, is exported as DLL data. This implies that a Windows NT application using this structure must reference it in one of three ways:

With any of these methods, ensure that you link with db2app.lib.

Example C Code

The following code illustrates the different ways the db2xa_switch can be accessed via a C program: on any UDB platform. Be sure to link with the appropriate library previously specified.

#include <stdio.h>
#include <xa.h>
struct xa_switch_t * SQL_API_FN  db2xacic( );
extern __declspec(dllimport) struct xa_switch_t db2xa_switch;
#define db2xa_switch (*db2xa_switch)
extern struct xa_switch_t db2xa_switch;

main( )
   struct xa_switch_t *foo;
   printf ("%s \n", );
   foo = db2xacic();
   printf ( "%s \n", foo->name );
   return ;

Making the Transaction Manager Known to DB2 Universal Database

DB2 must resolve the entry points to ax_reg and ax_unreg with the TM in order to be able to dynamically register a transaction:

XA Interface Problem Determination

When an error is detected during an XA request from the TM, the application program may not be able to get the error code from the TM. If your program abends or gets a cryptic return code from the TP Monitor or the TM, you should check the First Failure Service Log, which reports XA error information when diagnostic level 3 or greater is in use.

For more information about the First Failure Service Log, see the Troubleshooting Guide manual. In addition to this source of information for problem determination, you should also consult the console message, TM error file or other product-specific information provided by the external transaction processing software being used. Refer to the documentation of your transaction processing product for more details in this area.

The database manager writes all XA specific errors to the First Failure Service Log with SQLCODE -998 (transaction or heuristic errors) and the appropriate reason codes. The following are some of the more common reasons for errors:

The following example displays an XA open error generated on an AIX platform due to a missing XA open string.

Figure 41. Error Log for XA Open Error

Tue Apr  4 15:59:08 1995
toop pid(83378) process (xatest) XA DTP Support      sqlxa_open Probe:101
DIA4701E Database "" could not be opened for distributed transaction
String Title : XA Interface SQLCA  pid(83378)
Dump File : /u/toop/diagnostics/83378.dmp Data : SQLCA



Note that CICS for AIX can also interface with an external security manager to verify the signon ID and password. An administrator can also define which users can run specific CICS programs through the control of the Transaction Definition (TD). (TD in CICS for AIX is equivalent to the combination of Program Control Table (PCT) and Transaction List Table (XLT) in the other CICS family members.)

Several security measures can be used to restrict the usage of CICS by AIX users. A user must first be allowed to run the cicsh command to gain access to the CICS region. A user who is not defined in the CICS User Definition (UD) with specific security and transaction level keys can only have public level access.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]