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 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.
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.
To configure DB2:
DB2 CATALOG DATABASE inventdb AS inventdb AT NODE host1 AUTH SERVER DB2 CATALOG TCPIP NODE host1 REMOTE hostname1 SERVER svcname1
In Intel environments, this parameter contains the path and name of the DLL in an external transaction manager product containing the functions ax_reg and ax_unreg, and also informs DB2 which TP Monitor is being used.
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.
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 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. |
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. |
| 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:
You also require a compiler and the DB2 Software Developer's Kit. 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.lib
Where %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: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.
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.
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.
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 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
Following is a summary of installation and configuration considerations for MTS. To use DB2's MTS support, the user 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 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". |
The following servers are supported for multi-site update using MTS-coordinated transactions:
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 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.
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:
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. | ||
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 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
DB2 CREATE TABLE ACCOUNT (ACCOUNTNO INT, BALANCE INT) DB2 INSERT INTO ACCOUNT VALUES(1, 1)