The transaction scenario described in Connecting to One or More Data Sources portrays an application which interacts with only one database server in a transaction. Even though concurrent connections allow for concurrent transactions, the different transactions are not coordinated.
With multisite update, also known as Distributed Unit of Work (DUOW), two phase commit (2PC), and Coordinated Distributed Transactions, an application is able to update data in multiple remote database servers with guaranteed integrity.
A typical banking transaction is a good example of a multisite update. Consider the transfer of money from one account to another in a different database server. In such a transaction it is critical that the updates that implement the debit operation on one account do not get committed unless the updates required to process the credit to the other account are committed as well. The multisite update considerations apply when data representing these accounts is managed by two different database servers
Some multisite updates involve the use of a Transaction Manager to coordinate two-phase commit among multiple databases. For detailed description of multisite updates, refer to the Administration Guide. This section describes how DB2 CLI applications can be written to use various transaction managers:
DB2 CLI/ODBC applications can use DB2 itself as the Transaction Manager (DB2 TM) to coordinate distributed transactions against all IBM database servers. Please see the Administration Guide for more details about the requirements and capabilities of using DB2 as the transaction manager.
The DB2 Transaction Manager must be set up according to the information in the Administration Guide.
To use DB2 as the transaction manager in CLI/ODBC applications, the following CLI/ODBC configuration keywords must be set as follows:
[COMMON] DISABLEMULTITHREAD = 1 CONNECTTYPE=2 SYNCPOINT=2
For more information about setting the CLI/ODBC configuration keywords, see Platform Specific Details for CLI/ODBC Access.
Two of the above configuration keywords can also be set using the following environment attributes:
See SQLSetEnvAttr() for more details. There is no appropriate environment attribute for the DISABLEMULTITHREAD keyword which must therefore still be set to 1 in the [COMMON] section of the db2cli.ini file.
Because the DISABLEMULTITHREAD keyword must appear in the [COMMON] section of the db2cli.ini file it impacts all connections to all datasources from that client instance. This means that a DB2 client instance can only support process-based CLI applications or thread-based CLI applications, but not both
You must set the DB2 CLI/ODBC configuration keyword DISABLEMULTITHREAD to 1. This indicates that the DB2 CLI/ODBC driver will use a single DB2 context for all connections made by the application process. All database requests will serialized at the process level.
The environment attribute SQL_ATTR_CONNECTTYPE controls whether the application is to operate in a coordinated or uncoordinated distributed environment. The two possible values for this attribute are:
All connections within an application must have the same SQL_ATTR_CONNECTTYPE setting. It is recommended that the application set this environment attribute, if necessary, as soon as the environment handle has been created with a call to SQLAllocHandle() (with a HandleType of SQL_HANDLE_ENV). Since ODBC applications cannot access SQLSetEnvAttr(), they must set this using SQLSetConnectAttr() before any connection has been established.
A coordinated transaction means that commits or rollbacks among multiple database connections are coordinated. The SQL_COORDINATED_TRANS setting of the SQL_ATTR_CONNECTTYPE attribute corresponds to the Type 2 CONNECT in IBM embedded SQL and must be considered in conjunction with the SQL_ATTR_SYNC_POINT attribute, which has the following two possible settings:
Similar to SQL_ATTR_CONNECTTYPE, it is recommended that the application set this environment attribute, if necessary, as soon as the environment handle has been created with a call to SQLAllocHandle() (with a HandleType of SQL_HANDLE_ENV). ODBC applications must use SQLSetConnectAttr() to set this for each connection handle under the environment before any connections have been established.
All the connections within an application must have the same SQL_ATTR_CONNECTTYPE and SQL_ATTR_SYNC_POINT settings. After the first connection has been established, all subsequent connect types must be the same as the first. Coordinated connections default to manual-commit mode (for discussion on auto-commit mode, see Commit or Rollback).
The function SQLEndTran() must be used in a multisite update environment when DB2 is acting as the transaction manager.
Figure 5 shows the logical flow of an application executing statements on two SQL_CONCURRENT_TRANS connections ('A' and 'B'), and indicates the scope of the transactions.
Figure 6 shows the same statements being executed on two SQL_COORDINATED_TRANS connections ('A' and 'B'), and the scope of a coordinated distributed transaction.
Figure 5. Multiple Connections with Concurrent Transactions
|
Figure 6. Multiple Connections with Coordinated Transactions
 |
An application can establish coordinated transaction connections by calling the SQLSetEnvAttr() function, or by setting the CONNECTTYPE and SYNCPOINT keywords in the DB2 CLI initialization file or in the connection string for SQLDriverConnect(). The initialization file is intended for existing applications that do not use the SQLSetConnectAttr() function. For information about the keywords, refer to DB2 CLI/ODBC Configuration Keyword Listing.
An application cannot have a mixture of concurrent and coordinated connections, the type of the first connection will determine the type of all subsequent connections. SQLSetEnvAttr() will return an error if an application attempts to change the connect type while there is an active connection.
Restrictions
Mixing Embedded SQL and CLI/ODBC calls in a multisite update environment is supported, but all the same restrictions of writing mixed applications are imposed. Please see Mixing Embedded SQL and DB2 CLI for more details.
The following example connects to two data sources using a SQL_ATTR_CONNECTTYPE set to SQL_COORDINATED_TRANS and SQL_ATTR_SYNC_POINT set to SQL_ONEPHASE.
/* ... */
/* main */
int main( int argc, char * argv[] ) {
SQLHANDLE henv, hdbc[MAX_CONNECTIONS] ;
SQLRETURN rc ;
/* ... */
/* allocate an environment handle */
SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
/*
Before allocating any connection handles, set Environment wide
Connect Options
Set to Connect Type 2, Syncpoint 1
*/
if ( SQLSetEnvAttr( henv,
SQL_CONNECTTYPE,
( SQLPOINTER ) SQL_COORDINATED_TRANS,
0
) != SQL_SUCCESS ) {
printf( ">---ERROR while setting Connect Type 2 -------------\n" ) ;
return( SQL_ERROR ) ;
}
/* ... */
if ( SQLSetEnvAttr( henv,
SQL_SYNC_POINT,
( SQLPOINTER ) SQL_ONEPHASE,
0
) != SQL_SUCCESS ) {
printf( ">---ERROR while setting Syncpoint One Phase -------------\n" ) ;
return( SQL_ERROR ) ;
}
/* ... */
/* Connect to first data source */
prompted_connect( henv, &hdbc[0] ) ;
/* Connect to second data source */
DBconnect( henv, &hdbc[1] ) ;
/********* Start Processing Step *************************/
/* allocate statement handle, execute statement, etc. */
/********* End Processing Step ***************************/
/* Disconnect, free handles and exit */
Applications running under Microsoft Transaction Server (MTS) on Windows NT, Windows 95, and Windows 98 operating systems can use MTS to coordinate two-phase commit with multiple DB2 UDB, host, and AS/400 database servers and other MTS-compliant resource managers.
A new connection attribute has been created in SQLSetConnectAttr() to support the Microsoft Transaction Server (MTS). See the information on SQL_ATTR_ENLIST_IN_DTC in SQLSetConnectAttr - Set Connection Attributes.
MTS Software Prerequisites
MTS support requires a Version 5.2, or higher DB2 client, and MTS must be at Version 2.0 with Hotfix 0772 or later.
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)
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 values set for the following attributes:
DB2 CLI/ODBC applications written to make use of MTS must use ODBC functions, not CLI functions, to establish a connection.
Restrictions
Mixing Embedded SQL and CLI/ODBC calls in a multisite update environment is supported, but all the same restrictions of writing mixed applications are imposed. Please see Mixing Embedded SQL and DB2 CLI for more details.
Process-based XA TPs, such as CICS and Encina, start up one application server per process. In each application-server process, the connections are already established using the XA API (xa_open). This section describes the environment configurations and how to write DB2 CLI/ODBC appliciations to be run under this environment.
The XA Transaction Manager must be set up according to the information in the Administration Guide.
The setting of the CLI/ODBC configuration keywords for process-based XA TMs are exactly the same as when DB2 is used as the Transaction Manager. Please see Configuration - DB2 as Transaction Monitor for more inforamtion.
DB2 CLI/ODBC applications written for this environment must complete the following steps:
Restrictions
Mixing Embedded SQL and CLI/ODBC calls in a multisite update environment is supported, but all the same restrictions of writing mixed applications are imposed. Please see Mixing Embedded SQL and DB2 CLI for more details.
Multisite updates are also supported when a DB2 UDB client is connecting to host or AS/400 DB2 database servers using DB2 Connect.
There is no specific DB2 CLI/ODBC client configuration required when connecting to a host or AS/400 database server.
The machine running DB2 Connect may require certain configuration settings to enable running in multisite update mode against the host. For more information please see the DB2 Connect Quick Beginnings manual for your platform.