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. For example, a banking transaction that involves the transfer of money from one account to another in a different database server.
In such a transaction, it is critical that updates which implement debit operations on one account do not get committed unless updates required to process credits 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 updates. This support is available for applications developed using regular SQL as well as applications that use transaction processing monitors (TP monitors) that implement the X/Open XA interface specification. Examples of such TP monitors products include IBM® TxSeries CICS®, IBM Message and Queuing Series, IBM Component Broker Series, IBM San Francisco Project as well as Microsoft® Transaction Server (MTS), BEA Tuxedo and several others. There are different setup requirements depending on whether native SQL multisite update or TP monitor multisite update is used.
Both the native SQL and TP monitor multisite update 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 multisite update, the application must request commit or rollback by using the TP monitor's API, for example CICS SYNCPOINT, MTS SetAbort(). When using native SQL multisite update, the normal SQL COMMIT and ROLLBACK must be used.
TP monitor multisite update can coordinate a transaction that accesses both DB2 and non-DB2 resource managers such as Oracle, Informix® or SQLServer. 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 a distributed unit of work (DUOW). Currently, the following DB2 servers provided DUOW support that enabled them to participate in distributed transactions:
DB2 for Linux®, UNIX® and Windows® Version 8 or later
DB2 UDB for OS/390® and z/OS® Version 7
DB2 for z/OS Version 8
DB2 UDB for iSeries® requires OS/400® Version 5 Release 1 or later
A distributed transaction can update any mix of supported database servers. For example, your application can update several tables in a DB2 database on Windows, a DB2 for OS/390 and z/OS database, and a DB2 UDB for iSeries database, all within a single transaction.
DB2 transaction manager
The DB2 Database for Linux, UNIX, and Windows transaction manager (TM) assigns identifiers to transactions, monitors their progress, and takes responsibility for transaction completion and failure. The DB2 database system and DB2 Connect provide a transaction manager. The DB2 TM stores transaction information in the designated TM database.
The database manager provides transaction manager functions that can be used to coordinate the updating of several databases within a single unit of work. The database client automatically coordinates the unit of work, and uses a transaction manager database to register each transaction and track its completion status.
You can use the DB2 transaction manager with DB2 databases. If you have resources other than DB2 databases that you want to participate in a two-phase commit transaction, you can use an XA-compliant transaction manager.