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. For a description of DB2's xa_open string format, see xa_open and xa_close Strings Usage.

xa_open and xa_close Strings Usage

The database manager xa_open string has two accepted formats. One format is new to DB2 Version 7. The second format is used by earlier versions of DB2, and remains for back-level compatibility. New implementations should use the new format, and older implementations should be migrated to the new format when possible. Future versions of DB2 may not support the older xa_open string format. For information about the original xa_open string format, see xa_open String Format for Earlier Versions of DB2.

When setting up a database as a resource manager, you do not need the xa_close string. If provided, this string will be ignored by the database manager.

New xa_open String Format for DB2 Version 7

The following xa_open string format is new to DB2 Version 7: 

   parm_id1 = <parm value>,parm_id2 = <parm value>, ...

It does not matter in what order these parameters are specified. Valid values for parm_id are described in the following table.

Table 22. Valid Values for parm_id
Parameter Name Value Mandatory? Case Sensitive? Default Value
DB Database alias Yes No None
Database alias used by the application to access the database.
UID User ID No Yes None
User ID that has authority to connect to the database. Required if a password is specified.
PWD Password No Yes None
A password that is associated with the user ID. Required if a user ID is specified.
TPM Transaction processing monitor name No No None
Name of the TP monitor being used. For supported values, see TPM and TP_MON_NAME Values. This parameter can be specified to allow multiple TP monitors to use a single DB2 instance. The specified value will override the value specified in the tp_mon_name database manager configuration parameter.
AXLIB Library that contains the TP monitor's ax_reg and ax_unreg functions. No Yes None
This value is used by DB2 to obtain the addresses of the required ax_reg and ax_unreg functions. It can be used to override assumed values based on the TPM parameter, or it can be used by TP monitors that do not appear on the list for TPM.
CHAIN_END xa_end chaining flag. Valid values are T, F, or no value. No No F
XA_END chaining is an optimization that can be used by DB2 to reduce network flows. If the TP monitor environment is such that it can be guaranteed that xa_prepare will be invoked within the same thread or process immediately following the call to xa_end, and if CHAIN_END is on, the xa_end flag will be chained with the xa_prepare command, thus elimintaing one network flow. A value of T means that CHAIN_END is on; a value of F means that CHAIN_END is off; no specified value means that CHAIN_END is on. This parameter can be used to override the setting derived from a specified TPM value.
SUSPEND_ CURSOR Specifies whether cursors are to be kept when a transaction thread of control is suspended. Valid values are T, F, or no value. No No F
TP monitors that suspend a transaction branch can reuse the suspended thread or process for other transactions. In these situations, cursors must be closed so that the new transaction does not inherit them. When the suspended transaction is resumed, the application must obtain the cursors again. If SUSPEND_CURSOR is on, any open cursors are not closed, but the thread or process cannot be reused for other transactions. Only the resumption of the suspended transaction is permitted. A value of T means that SUSPEND_CURSOR is on; a value of F means that SUSPEND_CURSOR is off; no specified value means that SUSPEND_CURSOR is on. This parameter can be used to override the setting derived from a specified TPM value.
HOLD_CURSOR Specifies whether cursors are held across transaction commits. Valid values are T, F, or no value. No No F
TP monitors typically reuse threads or processes for multiple applications. To ensure that a newly loaded application does not inherit cursors opened by a previous application, cursors are closed after a commit. If HOLD_CURSOR is on, cursors are held across transaction commits. A value of T means that HOLD_CURSOR is on; a value of F means that HOLD_CURSOR is off; no specified value means that HOLD_CURSOR is on. This parameter can be used to override the setting derived from a specified TPM value.

TPM and TP_MON_NAME Values

The xa_open string TPM parameter and the tp_mon_name database manager configuration parameter are used to indicate to DB2 which TP monitor is being used. The tp_mon_name value applies to the entire DB2 instance. The TPM parameter applies only to the specific XA resource manager. The TPM value overrides the tp_mon_name parameter. Valid values for the TPM and tp_mon_name parameters are as follows:

Table 23. Valid Values for TPM and tp_mon_name
TPM Value TP Monitor Product Internal Settings
CICS IBM TxSeries CICS 
AXLIB=libEncServer (for Windows)
     =/usr/lpp/encina/lib/libEncServer
        (for UNIX based systems)
HOLD_CURSOR=T
CHAIN_END=T
SUSPEND_CURSOR=F


ENCINA IBM TxSeries Encina Monitor 
AXLIB=libEncServer (for Windows)
     =/usr/lpp/encina/lib/libEncServer
        (for UNIX based systems)
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F


MQ IBM MQSeries 
AXLIB=mqmax (for Windows)
     =/usr/mqm/lib/libmqmax.a
        (for AIX)
     =/opt/mqm/lib/libmqmax.a
        (for Solaris)
HOLD_CURSOR=F
CHAIN_END=F
SUSPEND_CURSOR=F


CB IBM Component Broker 
AXLIB=somtrx1i (for Windows)
     =libsomtrx1
        (for UNIX based systems)
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F


SF IBM San Francisco 
AXLIB=ibmsfDB2
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F


TUXEDO BEA Tuxedo 
AXLIB=libtux
HOLD_CURSOR=F
CHAIN_END=F
SUSPEND_CURSOR=F


MTS Microsoft Transaction Server
It is not necessary to configure DB2 for MTS. MTS is automatically detected by DB2's ODBC driver.
JTA Java Transaction API
It is not necessary to configure DB2 for Enterprise Java Servers (EJS) such as IBM WebSphere. DB2's JDBC driver automatically detects this environment.

Examples

  1. You are using IBM TxSeries CICS on WIndows NT. The TxSeries documentation indicates that you need to configure tp_mon_name with a value of libEncServer:C. This is still an acceptable format; however, with DB2 UDB or DB2 Connect Version 7, you have the option of:
  2. You are using IBM MQSeries on Windows NT. The MQSeries documentation indicates that you need to configure tp_mon_name with a value of mqmax. This is still an acceptable format; however, with DB2 UDB or DB2 Connect Version 7, you have the option of:
  3. You are using both IBM TxSeries CICS and IBM MQSeries on WIndows NT. A single DB2 instance is being used. In this scenario, you would configure as follows:
    1. For each database defined to CICS in the Region-> Resources-> Product-> XAD-> Resource manager initialization string, specify: 
         pwd=password,uid=userid,tpm=cics,db=dbalias
    2. For each database defined as a resource in the queue manager properties, specify an XaOpenString as: 
         db=dbalias,uid=userid,pwd=password,tpm=mq
  4. You are developing your own XA-compliant transaction manager (XA TM) on Windows NT, and you want to tell DB2 that library "myaxlib" has the required functions ax_reg and ax_unreg. Library "myaxlib" is in a directory specified in the PATH statement. You have the option of:
  5. You are developing your own XA-compliant transaction manager (XA TM) on Windows NT, and you want to tell DB2 that library "myaxlib" has the required functions ax_reg and ax_unreg. Library "myaxlib" is in a directory specified in the PATH statement. You also want to enable XA END chaining. You have the option of:

xa_open String Format for Earlier Versions of DB2

Earlier versions of DB2 used the xa_open string format described here. This format is still supported for compatibility reasons. Applications should be migrated to the new format (see New xa_open String Format for DB2 Version 7) when possible.

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: 

   "database_alias<,userid,password>"

The database_alias is required to specify the alias name of the database. The alias name is the same as the database name unless you have explicitly cataloged an alias name after you created the database. The user name 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 need the xa_close string. If provided, this string will be ignored by the database manager.

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. To support commit sequences from different processes, the DB2 Connect concentrator must be enabled. To enable the DB2 Connect EE concentrator, set the database manager configuration parameter max_logicagents to a value greater then maxagents. Note that the DB2 Connect EE concentrator requires a DB2 Version 7.1 client to support XA commit sequences from different processes. For information about the SQL statements that are allowed in this environment, refer to the Application Development Guide. For information about the concentrator, refer to the DB2 Connect User's Guide.

If you will be updating host or AS/400 database servers, you will require DB2 Connect with the DB2 sync point manager (SPM) configured. Refer to one of the Quick Beginnings books for instructions.

Database Connection Considerations

The following topics are covered in this section:

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

In a partitioned database environment, user data may be distributed 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 transactions against a database in a partitioned database 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 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, described in Understanding the Two-Phase Commit Process. The principal 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 indoubt transactions, there are actions you can take to manually resolve them. This manual process is sometimes referred to as "making a heuristic decision".

The LIST INDOUBT TRANSACTIONS command (using the WITH PROMPTING option), or the 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. To obtain indoubt transaction information from DB2 UDB on UNIX based systems, the Windows operating system, or OS/2, connect to the database and issue the LIST INDOUBT TRANSACTIONS WITH PROMPTING command, or the equivalent API. For information about this command or the related administrative APIs, refer to the Command Reference or the Administrative API Reference.

For indoubt transaction information with respect to host or AS/400 database servers, you have two choices:

Use these commands (or related APIs) with extreme caution, and only as a last resort. The best strategy 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 against another participating database. Recovering from data integrity problems requires you to understand the application logic, to identify the data that was changed or rolled back, and then to perform a point-in-time recovery of the database, or manually undo or reapply the 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, heuristic operations are necessary. This situation could occur if the transaction manager will not be available for an extended period of time to perform 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 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.

Although there is no foolproof way to perform heuristic operations, the following provides some general guidelines:

  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 the 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, indicated in output from the LIST INDOUBT TRANSACTIONS command. The heuristic forget function releases the log space occupied by an 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 for the transaction in this resource manager. In general a "missing" log record implies that the resource manager has rolled back the transaction.

Security Considerations

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 TXSeries CICS region is started, it is associated with the AIX user name under which it is defined. All the CICS Application Server processes are also being run under this TXSeries CICS "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. 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 appear to the RM as a single program with many units of work from the same "cics" ID. Optionally, you can 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.

There is not much impact on static SQL statements, 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 ID, and not to the end user ID.

For dynamic statements, which have their access authentication done at run time, access privileges to the database objects must be granted to the server 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, the CICS log) to determine who has run these transactions or programs, and when.

Configuration Considerations

You should consider 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, with the following exceptions:

For information about xa_open and xa_close strings usage, see xa_open and xa_close Strings Usage.

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 various XA functions, the following fields are returned:

Field
Value

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

flags
TMREGISTER | TMNOMIGRATE

Explicitly states that DB2 Universal Database uses dynamic registration, and that the TM should not use association migration. Implicitly states that asynchronous operation is not supported.

version
Must be zero.

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 RM's xa_ routines. An RM 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.

UNIX Based Systems and OS/2

DB2 UDB's switch can be obtained through either of the following two ways:

With either method, you must link your application with libdb2 (on UNIX based system) or db2api.lib (on OS/2).

Windows NT

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, you must link your application with db2api.lib.

Example C Code

The following code illustrates the different ways in which the db2xa_switch can be accessed via a C program on any DB2 UDB platform. Be sure to link your application with the appropriate library. 

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


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

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 effect. For more information about the First Failure Service Log, refer to the Troubleshooting Guide.

You should also consult the console message, TM error file, or other product-specific information about the external transaction processing software that you are using.

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. Following are some of the more common errors:

Following is an example of an error log for an xa_open error (due to a missing xa_open string) generated on AIX: 

   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
   processing.
   String Title : XA Interface SQLCA  pid(83378)
   SQLCODE = -998  REASON CODE: 4  SUBCODE: 1
   Dump File : /u/toop/diagnostics/83378.dmp Data : SQLCA

[ Top of Page | Previous Page | Next Page ]