The sections that follow describe how to configure specific products to use DB2 as a resource manager. You can use any of the following:
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 http://www.transarc.com/Library/documentation/websphere/WAS-EE/en_US/html/.
Host and AS/400 database servers can participate in CICS-coordinated transactions.
Following are the various APIs 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 through DB2 Connect. TXSeries documentation can be viewed online at http://www.transarc.com/Library/documentation/websphere/WAS-EE/en_US/html/.
Host and AS/400 database servers can participate in Encina-coordinated transactions.
To configure DB2:
DB2 CATALOG DATABASE inventdb AS inventdb AT NODE host1 AUTH SERVER DB2 CATALOG TCPIP NODE host1 REMOTE hostname1 SERVER svcname1
db2 update dbm cfg using tp_mon_name ENCINA
To configure Encina for each resource manager (RM), 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 "db=inventdb,uid=user1,pwd=password1"
There is one resource manager configuration for each DB2 database, and each resource manager 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 user ID 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. For information about DB2's xa_open string, see Setting Up a Database as a Resource Manager.
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 UDB 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, you must use the DB2 Syncpoint Manager. Refer to the DB2 Connect Enterprise Edition for OS/2 and Windows Quick Beginnings manual for configuration instructions.
To reference a DB2 database from an Encina application:
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 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. |
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 Windows operating systems and OS/2, and libdb2 on UNIX based systems).
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 API. 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. Avoid using these transactions, if possible. If you cannot, ensure that SQL work is done in only one member of the Encina transaction family. |
To configure Tuxedo to use DB2 as a resource manager, perform the following steps:
You also require a compiler and the DB2 Application Development Client. Install these if necessary.
# DB2 UDB
UDB_XA:db2xa_switch:-L${DB2DIR} /lib -ldb2
where {TUXDIR} is the directory where you
installed Tuxedo, and {DB2DIR} is the DB2 instance
directory.
# DB2 UDB UDB_XA;db2xa_switch;%DB2DIR%\lib\db2api.libwhere %TUXDIR% is the directory where you installed Tuxedo, and %DB2DIR% is the DB2 instance directory.
${TUXDIR}/bin/buildtms -r UDB_XA -o ${TUXDIR}/bin/TMS_UDB
where {TUXDIR} is the directory where you
installed Tuxedo.
%TUXDIR%\bin\buildtms -r UDB_XA -o %TUXDIR%\bin\TMS_UDB
${TUXDIR}/bin/buildserver -r UDB_XA -f svcfile.o -s SVC1,SVC2
-o UDBserver
where {TUXDIR} is the directory where you
installed Tuxedo.
%TUXDIR%\bin\buildserver -r UDB_XA -f svcfile.o -s SVC1,SVC2
-o UDBserver
where %TUXDIR% is the directory where you installed
Tuxedo.
UDB_GRP LMID=simp GRPNO=3
TMSNAME=TMS_UDB TMSCOUNT=2
OPENINFO="UDB_XA:db=sample,uid=db2_user,pwd=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.
tmboot -yAfter 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.
DB2 UDB V5.2 and later 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.
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.ibm.com/software/data/db2/library/ , and search for a DB2 Universal Database "Technote" with the keyword "MTS". |
MTS support requires the DB2 Client Application Enabler (CAE) Version 5.2, or later, 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
Following is a summary of installation and configuration considerations for MTS. To use DB2's MTS support, you must:
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 applications 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.ibm.com/software/data/db2/library/ , and search for a DB2 Universal Database "Technote" with the keyword "MTS". |
The following servers are supported for multisite update using MTS-coordinated transactions:
You can set the transaction time-out value in the MTS Explorer tool. For more information, refer to the online MTS Administrator Guide.
If a transaction takes longer than the transaction time-out value (default value 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 request, the rollback request is serialized on the connection to guarantee the integrity of the data on the database server.
As a result:
Connection pooling enables an application to use a connection from a pool of connections, so that the connection does not need to be re-established 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 the 32-bit Windows operating system fully supports connection pooling; therefore, this keyword is registered. Version 5.2 clients must install FixPack 3 (WR09024) or later.
The default value of 60 means that the connection will be pooled for 60 seconds before it 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 large amounts of system resource, including system memory and communications stack resources.
In addition, to ensure that the same connection is used between objects in the same transaction in a multiple processor machine, you must turn off "multiple pool per processor" support. To do this, copy the following registry setting into a file called odbcpool.reg, save it as a plain text file, and issue the command odbcpool.reg. The Windows operating system will import these registry settings.
REGEDIT4 [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling] "NumberOfPools"="1"
Without this keyword set to 1, MTS may pool connections in different pools, and hence will not reuse the same connection.
If the MTS COM objects use ADO to access the database, you must turn off the OLEDB resource pooling so that the Microsoft OLEDB provider for ODBC (MSDASQL) will not interfere with ODBC connection pooling. This feature was initialized to OFF in ADO 2.0, but is initialized to ON in ADO 2.1. To turn OLEDB resource polling off, copy the following lines into a file called oledb.reg, save it as a plain text file, and issue the command oledb.reg. The Windows operating system will import this registry setting.
REGEDIT4
[HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}]
@="MSDASQL"
"OLEDB_SERVICES"=dword:fffffffc
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 that there are two COM objects, COM1 and COM2, that connect to the same ODBC data source 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, 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:
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 constraints.
To alleviate this problem, 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, create it. | ||
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:
[ODBC] DRIVER=IBM DB2 ODBC DRIVER UID=your_user_id PWD=your_password DSN=your_database_alias Description=MTS Samples
where:
db2 bind @C:\sqllib\bnd\db2cli.lst blocking all grant public
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&VM). For example:
db2 bind @C:\sqllib\bnd\@ddcsmvs.lst blocking all grant public
Otherwise, bind the db2ubind.lst file:
db2 bind @C:\sqllib\bnd\@db2ubind.lst blocking all grant public
db2 create table account (accountno int, balance int) db2 insert into account values(1, 1)