Multisite update, also known as Distributed Unit of Work (DUOW) and Two-Phase commit, is a function that enables your applications to update data in multiple remote database servers with guaranteed integrity. A good example of a multisite update is a banking transaction that involves transfer of money from one account to another in a different database server. In such a transaction it is critical that updates that implement debit operation on one account do not get committed unless updates required to process 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.
DB2 products provide comprehensive support for multisite update. This support is available for applications developed using regular SQL as well as applications that utilize Transaction Monitor products that implement X/Open XA interface specification. Examples of such Transaction Monitor products include IBM TxSeries (CICS and Encina), Message and Queuing Series, Component Broker Series, San Francisco Project as well as Microsoft Transaction Server (MTS), BEA Tuxedo, NCR TopEnd and several others. There are different setup requirements depending on whether native SQL multisite update or TP Monitor multisite upate is used.
Both the native SQL and TP Monitor multi-site update programs must be precompiled with the CONNECT 2 SYCNCPOINT TWOPHASE options. Both can use the SQL Connect statement to indicate which database they want to be used for the SQL statements that follow. If there is no TP Monitor to tell DB2 it is going to coordinate the transaction (as indicated by DB2 receiving the xa_open calls from the TP monitor to establish a database connection), then the DB2 software will be used to coordinate the transaction.
When using TP monitor multi-site update, the application must request commit or rollback by using the TP monitor's API, e.g. CICS SYNCPOINT, Encina Abort(), MTS SetAbort(). When using native SQL muilti-update, the normal SQL COMMIT and ROLLBACK must be used.
TP Monitor multi-site update can coordinate a transaction that accesses both DB2 and non-DB2 resource managers such as Oracle, Informix, SQLServer, etc. Native SQL multisite update is used with DB2 servers only.
For a multisite update transaction to work each of the databases participating in a distributed transaction must be capable of supporting Distributed Unit of Work. At the time of this writing the following DB2 servers provided DUOW support that enabled them to participate in distributed transactions:
A distributed transaction can update any mix of supported database servers. For example, your application can update several tables in DB2 UDB on Windows NT, a DB2 for OS/390 database and a DB2/400 database all within a single transaction.
Host and AS/400 database servers require DB2 Connect to participate in a
distributed transaction originating from PC, UNIX, and web
applications. In addition many of the multisite update scenarios that
involve host and AS/400 database servers require that the Syncpoint Manager
(SPM) component be configured. The need for SPM is dictated by the
choice of protocol (SNA vs. TCP/IP) and use of a TP monitor. See
Table 1 for a summary of scenarios that require use of the
SPM. The table shows that DB2 Connect is required for any access to the
host or AS/400 from Intel or UNIX machines. In addition, for multisite
updates, the SPM component of DB2 Connect is required if the access is via SNA
or uses a TP monitor.
Table 1. Host and AS/400 Multisite Update Scenarios that Require SPM
| Host and AS/400 multisite update scenarios that require SPM. | ||||||
| TP Monitor Used? | Protocol | SPM Needed? | Product Required (choose One) | Host and AS/400 Database Supported | ||
| Yes | TCP/IP | Yes |
|
| ||
| Yes | SNA | Yes |
|
| ||
| No | TCP/IP | No |
|
| ||
| No | SNA | Yes |
|
| ||
For more information about two-phase commit, see:
For more information about Transaction Monitors, and setting up a TP monitor environment, see: