IBM Books

Administration Guide


Configuring XA Transaction Managers to Use DB2 UDB

The sections that follow describe how to configure specific products to use DB2 as a resource manager. You can use any of the following:

Configuring IBM TXSeries CICS

For information about how to configure IBM TXSeries CICS to use DB2 as a resource manager, refer to your IBM TXSeries CICS Administration Guide. TXSeries documentation can be viewed online at starting at http://www.transarc.com/dfs/public/www/htdocs/.hosts/external/ Library/index.html

Host and AS/400 database servers can participate in CICS-coordinated transactions.

Configuring IBM TXSeries Encina

The following are the various API and configuration parameters required for the integration of Encina Monitor and DB2 Universal Database servers or DB2 for MVS, DB2 for OS/390, DB2 for AS/400, or DB2 for VSE&VM when accessed via DB2 Connect. TXSeries documentation can be viewed online starting at http://www.transarc.com/dfs/public/www/htdocs/.hosts/external/ Library/index.html

Host and AS/400 database servers can participate in Encina-coordinated transactions.

Configuring DB2

To configure DB2:

  1. Each database name must be defined in the DB2 database directory. If the database is a remote database, then a Node Directory entry must also be defined. You can perform the configuration using the GUI Client Configuration Assistant (CCA), or the DB2 Command Line Processor (CLP). For example:
    DB2 CATALOG DATABASE inventdb AS inventdb AT NODE host1 AUTH SERVER
     
    DB2 CATALOG TCPIP NODE host1 REMOTE hostname1 SERVER svcname1
    
  2. The DB2 client can optimize its internal processing for Encina if it knows that it is dealing with Encina. You can specify this by setting the tp_mon_name database manager configuration parameter to ENCINA. The default is for no special optimization. If tp_mon_name is set, then the application must ensure the thread that performs the unit of work also immediately commits the work after ending it. No other unit of work may be started. If this is not your environment, then ensure that the value for tp_mon_name value is NONE (or via the CLP, the value is set to NULL). The tp_mon_name can be updated by invoking the CCA or by the CLP:

Configuring Encina for Each Resource Manager

To configure Encina for each resource manager, an administrator must define the Open String, Close String, and Thread of Control Agreement for each DB2 database as a resource manager before the resource manager can be registered for transactions in an application. The configuration can be performed using the Enconcole full screen interface, or the Encina command line interface. For example:

   monadmin create rm inventdb  -open "inventdb,user1,password1"

There is one resource manager configuration for each DB2 database, and each resource manager (RM) configuration must have an rm name ("logical RM name"). To simplify the situation, you should make it identical to the database name.

The XA Open String contains information that is required to establish a connection to the database. The content of the string is RM specific. The XA Open String of DB2 UDB contains the alias name of the database to be opened, and optionally a userID and password to be associated with the connection. Note that the database name defined here must also be cataloged into the regular database directory required for all database access. The name can be up to 8 bytes long.

The XA Close String is not used by DB2.

The Thread of Control Agreement determines if an application agent thread can handle more than one transaction at a time. DB2 V5.0 and following supports the default of TMXA_SERIALIZE_ALL_OPERATIONS, where a thread can be reused only after a transaction has completed.

If you are accessing DB2 for OS/390, DB2 for MVS, DB2 for AS/400, or DB2 for VSE&VM, then you must use the DB2 Syncpoint Manager. Please refer to the DB2 Connect Enterprise Edition for OS/2 and Windows NT Quick Beginnings manual for configuration instructions.

Referencing a DB2 Database from an Encina Application

To reference a DB2 database from an Encina application:

  1. Use the Encina Scheduling Policy API to specify how many application agents can be run from a single TP Monitor application process. For example:
      rc = mon_SetSchedulingPolicy (MON_EXCLUSIVE)
    

    For DB2 (DB2 Universal Database, host, or AS/400 database servers), you should use the default setting of MON_EXCLUSIVE. This ensures that:

    Note:If you are using the ODBC or DB2 Call Level Interface, you must disable the multithread support. You can do this by setting the CLI configuration parameter DISABLEMULTITHREAD = 1 (disables multithreading). The default for DB2 Universal Database is DISABLEMULTITHREAD = 0 (enables multithreading). Refer to the CLI Guide and Reference for more information.
  2. Use the Encina RM Registration API to provide the XA switch and the logical RM name to be used by Encina when referencing the RM in an application process. For example:
     rc = mon_RegisterRmi ( &db2xa_switch, /* xa switch */
                              "inventdb", /* logical RM name */
                            &rmiId  ); /* internal RM id */
    

    The XA Switch contains the addresses of the XA routines in the RM that the TM can call, and it also specifies the functionality that is provided by the RM. The XA Switch of DB2 Universal Database is db2xa_switch, and it resides in the DB2 client library (db2app.dll on INTEL platforms and libdb2 on UNIX-based platforms).

    The logical RM name is the one used by Encina, and is not the actual database name that is used by the SQL application that runs under Encina. The actual database name is specified in the XA Open String in the Encina RM Registration. To simplify the situation, the logical RM name is set to be the same as the database name in this example.

    The third parameter returns an internal identifier or handle that is used by the TM to reference this connection.

Note:When using Encina for transaction processing with DB2 through the TM-XA interface, note that Encina nested transactions are not currently supported by the DB2 XA interface. If possible, avoid using these transactions. If you cannot, ensure that SQL work is done in only one member of the Encina transaction family.

Configuring BEA Tuxedo

Note:Applications that access host or AS/400 database servers in a Tuxedo environment are limited to read-only access to these servers.

To configure Tuxedo to use DB2 as a resource manager, perform the following steps:

  1. Install Tuxedo as specified in the documentation for that product. Ensure that you perform all basic Tuxedo configuration, including the log files and environment variables.

    You also require a compiler and the DB2 Software Developer's Kit. Install these if necessary.

  2. At the Tuxedo server ID, set the DB2INSTANCE environment variable to reference the instance that contains the databases that you want Tuxedo to use. Also set the PATH variable to include the DB2 program directories. Then confirm that the Tuxedo server ID can connect to the DB2 databases.

  3. For Windows NT only. Update the tp_mon_name database manager configuration parameter with the name of the DLL that contains the ax_reg and ax_unreg routines. In Tuxedo, this DLL is called libtux.

  4. Add a definition for DB2 to the Tuxedo resource manager definition file. In the examples that follow, UDB_XA is the locally defined Tuxedo resource manager name for DB2, and db2xa_switch is the DB2-defined name for a structure of type xa_switch_t:

  5. Build the Tuxedo transaction monitor server program for DB2:

  6. Build the application servers. In the examples that follow, the -r option specifies the resource manager name, the -f option (used one or more times) specifies the files that contain the application services, the -s option specifies the application service names for this server, and the -o option specifies the output server file name:

  7. Set up the Tuxedo configuration file to reference the DB2 server. In the *GROUPS section of the UDBCONFIG file, add an entry similar to:
       UDB_GRP   LMID=simp GRPNO=3
         TMSNAME=TMS_UDB TMSCOUNT=2
         OPENINFO="UDB_XA:SAMPLE,db2_user,,db2_user_pwd"
    

    Where the TMSNAME parameter specifies the transaction monitor server program that you built previously, and the OPENINFO parameter specifies the resource manager name. This is followed by the database name and the DB2 user and password, which are used for authentication.

    The application servers that you built previously are referenced in the *SERVERS section of the Tuxedo configuration file.

  8. Start Tuxedo:
       tmboot -y
    

    After the command completes, Tuxedo messages should indicate that the servers are started. In addition, if you issue the DB2 command LIST APPLICATIONS ALL, you should see two connections (in this situation, specified by the TMSCOUNT parameter in the UDB group in the Tuxedo configuration file, UDBCONFIG.

Configuring Microsoft Transaction Server

DB2 UDB V5.2 and following can be fully integrated with Microsoft Transaction Server (MTS) Version 2.0. Applications running under MTS on Windows 32-bit operating systems can use MTS to coordinate two-phase commit with multiple DB2 UDB, host, and AS/400 database servers, as well as with other MTS-compliant resource managers.

Enabling MTS Support in DB2

Microsoft Transaction Server support is automatically enabled. While you can set the tp_mon_name database manager configuration parameter to "MTS", it is not necessary and will be ignored.
Note:Additional technical information may be provided on the IBM web site to assist you with installation and configuration of DB2 MTS support. Set your URL to "http://www.software.ibm.com/data/db2/library", and search for a DB2 Universal Database "Technote" with the keyword "MTS".

MTS Software Prerequisites

MTS support requires the DB2 Client Application Enabler (CAE) Version 5.2, or higher, and MTS must be at Version 2.0 with Hotfix 0772 or later releases.

The installation of the DB2 ODBC driver on Windows 32-bit operating systems will automatically add a new keyword into the registry:

  HKEY_LOCAL_MACHINE\software\ODBC\odbcinit.ini\IBM DB2 ODBC Driver:
  Keyword Value Name: CPTimeout
  Data Type: REG_SZ
  Value: 60

Installation and Configuration

Following is a summary of installation and configuration considerations for MTS. To use DB2's MTS support, the user must:

  1. Install MTS and the DB2 client on the same machine where the MTS application runs.

  2. If host or AS/400 database servers are to be involved in a multisite update:

    1. Install DB2 Connect Enterprise Edition either on your local machine or on a remote machine. DB2 Connect Enterprise Edition allows host or AS/400 database servers to participate in a multisite update transaction.

    2. Ensure your DB2 Connect Enterprise Edition Server is enabled for multisite update. For information on enabling DB2 Connect for multisite updates please see the DB2 Connect Enterprise Edition Quick Beginnings manual for your platform.

When running DB2 CLI/ODBC applications the following configuration keywords (as set in the db2cli.ini file) must not be changed from their default values:

DB2 CLI applications written to make use of MTS support must not change the attribute values corresponding to the above keywords. In addition, the application must not change the default values of the following attributes:

Note:Additional technical information may be provided on the IBM web site to assist you with installation and configuration of DB2 MTS support. Set your URL to "http://www.software.ibm.com/data/db2/library", and search for a DB2 Universal Database "Technote" with the keyword "MTS".

Verifying the Installation

  1. Configure DB2 client and DB2 Connect EE to access your DB2 UDB, host, or AS/400 server.

  2. Verify the connection from the DB2 CAE machine to the DB2 UDB database servers.

  3. Verify the connection from the DB2 Connect machine to your host or AS/400 database server with DB2 CLP and issue a few queries.

  4. Verify the connection from the DB2 CAE machine via the DB2 Connect gateway to your host or AS/400 database server and issue a few queries.

Supported DB2 Database Servers

The following servers are supported for multi-site update using MTS-coordinated transactions:

MTS Transaction Time-Out and DB2 Connection Behavior

You can set the transaction time-out value in the MTS Explorer tool. Please refer to the on-line MTS Administrator Guide for more details.

If a transaction takes longer than the transaction time-out value (default is 60 seconds), MTS will asynchronously issue an abort to all Resource Managers involved, and the whole transaction is aborted.

For the connection to a DB2 server, the abort is translated into a DB2 rollback request. Like any other database requests, the rollback request will be serialized on the connection to guarantee the integrity of the data on the database server.

As a result:

Connection Pooling

Connection pooling enables an application to use a connection from a pool of connections, so that the connection does not need to be reestablished for each use. Once a connection has been created and placed in a pool, an application can reuse that connection without performing a complete connection process. The connection is pooled when the application disconnects from the ODBC data source, and will be given to a new connection whose attributes are the same.

Connection pooling has been a feature of ODBC driver Manager 2.x. With the latest ODBC driver manager (version 3.5) that was shipped with MTS, connection pooling has some configuration changes and new behavior for ODBC connections of transactional MTS COM objects (see Reusing ODBC Connections Between COM Objects Participating in the Same Transaction).

ODBC driver Manager 3.5 requires that the ODBC driver register a new keyword in the registry before it allows connection pooling to be activated. The keyword is:

  Key Name: SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER
  Name: CPTimeout
  Type: REG_SZ
  Data: 60

The DB2 ODBC driver version 6 and later for 32-bit Windows operating system fully supports connection pooling and therefore this keyword is registered. Version 5.2 clients must install Fix Pack 3 (WR09024) or later.

The default value (60) means the connection will be pooled for 60 seconds before it actually is disconnected.

In a busy environment, it is better to increase the CPTimeout value to a large number (Microsoft sometimes suggests 10 minutes for certain environments) to prevent too many physical connects and disconnects, because these consume a large amount of system resources, including system memory and communications stack resource.

Reusing ODBC Connections Between COM Objects Participating in the Same Transaction

ODBC connections in MTS COM objects have connection pooling turned on automatically (whether or not the COM object is transactional) .

For multiple MTS COM objects participating in the same transaction, the connection can be reused between two or more COM objects in the following manner.

Suppose there are two COM objects, COM1 and COM2 that connect to the same ODBC datasource and participate in the same transaction.

After COM1 connects and does its work, it disconnects and the connection is pooled. However, this connection will be reserved for the use of other COM objects of the same transaction. It will be available to other transactions only after the current transaction ends.

When COM2 is invoked in the same transaction, it is given the pooled connection. MTS will ensure that the connection can only be given to the COM objects that are participating in the same transaction.

On the other hand, if COM1 does not explicitly disconnect, then it will tie up the connection until the transaction ends. When COM2 is invoked in the same transaction, a separate connection will be acquired. Subsequently, this transaction ties up two connections instead of one.

This reuse of connection feature for COM objects participating in the same transaction is preferable for the following reasons:

Tuning TCP/IP Communications

If a small CPTimeout value is used in a high-workload environment where too many physical connects and disconnects occur at the same time, the TCP/IP stack may encounter resource problems.

To alleviate this problem, you should use the TCP/IP Registry Entries. These are described in the Windows NT Resource Guide, Volume 1. The registry key values are located in HKEY_LOCAL_MACHINE-> SYSTEM-> CurrentControlSet-> Services-> TCPIP-> Parameters.

The default values and suggested settings are as follows:
Name Default Value Suggested Value
KeepAlive time 7200000 (2 hours) Same
KeepAlive interval 1000 (1 second) 10000 (10 seconds)
TcpKeepCnt 120 (2 minutes) 240 (4 minutes)
TcpKeepTries 20 (20 re-tries) Same
TcpMaxConnectAttempts 3 6
TcpMaxConnectRetransmission 3 6
TcpMaxDataRetransmission 5 8
TcpMaxRetransmissionAttempts 7 10
If the registry value is not defined, then create it.

Testing DB2 With The MTS "BANK" Sample Application

You can use the "BANK" sample program that is shipped with MTS to test the setup of the client products and MTS.

Follow these steps:

  1. Change the file \Program Files\Common Files\ODBC\Data Sources\ MTSSamples.dsn so that it looks like this:
       [ODBC]
       DRIVER=IBM DB2 ODBC DRIVER
       UID=your_user_id
       PWD=your_password
       DSN=your_database_alias
       Description=MTS Samples 
    

    where:

  2. Go to ODBC Administration in the Control Panel, click on System DSN tab and add the data source:

    1. Choose IBM ODBC Driver and click on Finish.

    2. When presented with the list of database aliases, choose the one that was specified previously.

    3. Click on OK

  3. Use DB2 CLP to connect to a DB2 database under the ID your_user_id, as above.

    1. Bind the db2cli.lst:
         db2 bind @C:\sqllib\bnd\db2cli.lst blocking all grant public
      

    2. Bind the utilities.

      If the server is a DRDA host server, bind ddcsmvs.lst, ddcs400.lst, or ddcsvm.lst, depending on the host that you are connecting to (OS/390, AS/400, or VSE or VM). For example:

         db2 bind @C:\sqllib\bnd\@ddcsmvs.lst blocking all grant public
      

      Otherwise, bind the db2ubind.lst:

       db2 bind @C:\sqllib\bnd\@db2ubind.lst blocking all grant public
      

    3. Then create the sample table and data for the MTS sample application as follows:
         DB2 CREATE TABLE ACCOUNT (ACCOUNTNO INT, BALANCE INT)
         DB2 INSERT INTO ACCOUNT VALUES(1, 1)
      

  4. On the DB2 client, ensure that the database manager configuration parameter tp_mon_name is set to "MTS".

  5. Run the "BANK" application. Select the Account button and the Visual C++ option, then submit the request. Other options may use SQL that is specific to SQL Server, and may not work.


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

[ DB2 List of Books | Search the DB2 Books ]