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 monitor (TP monitor) products that implement the X/Open XA interface specification. Examples of such TP monitors products include IBM TxSeries (CICS and Encina), 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 programs must be precompiled with the CONNECT 2 SYNCPOINT 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 multisite update, the application must request commit or rollback by using the TP monitor's API, for example CICS SYNCPOINT, Encina Abort(), 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 distributed unit of work. Currently, 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 Universal Database on Windows NT or Windows 2000, 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. When a DB2 instance is created, the DB2 SPM is automatically configured with default settings.
The need for SPM is dictated by the choice of protocol (SNA or TCP/IP) and
use of a TP monitor. The following table provides a summary of
scenarios that require the use of SPM. The table also 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
| 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 |
|
|
| Note: | 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.
For more information about two-phase commit, as well as instructions for setting up for several popular TP monitors, refer to the Administration Guide. You can also access the DB2 Product and Service Technical Library on the World Wide Web:
|
Although DRDA defines database communication protocols, it does not define the programming interfaces, or APIs, that should be used by application programmers. In general, DRDA can be used by an application program to pass any request that a target DRDA server can execute. All of the DRDA servers available today can execute SQL requests forwarded by an application program through DB2 Connect.
IBM provides application programmers with tools to generate SQL requests for Windows, OS/2, and several UNIX platforms. These tools are part of the DB2 Application Development Client. The DB2 Application Development Client supports several API types: embedded SQL, JDBC, SQLJ, and the DB2 Call Level Interface (DB2 CLI). These APIs can be used by programmers to build applications in a variety of programming languages. For more information about these APIs, refer to Application Building Guide.
Application developers can also use APIs provided by other companies. For example, Microsoft ODBC and ADO are used by Windows application programmers to develop database applications. DB2 Connect provides an ODBC driver and an OLE DB Provider that support applications developed using the ODBC and ADO APIs. IBM does not provide tools for developing ODBC applications; these tools are provided by the Microsoft Corporation.
You can use the Control Center to provide multisite updates. The procedure is simple, and is outlined below. For more information about the multisite update configuration process, including how to configure your system manually, refer to the online Connectivity Supplement.
From the Control Center, click the [+] sign to expand the tree view. With the right mouse button, select the instance that you wish to configure. A pop-up menu opens. Select Multisite Update --> Configure menu item.
The Wizard provides a notebook-type interface. Each page of the wizard will prompt you for certain information about your configuration. The pages are shown below in the order in which you will encounter them.
Step 1. | Specify a Transaction Processor (TP) monitor. This field will show the defaults for the TP monitor you have enabled. If you do not want to use a TP monitor, select Do Not Use a TP Monitor. |
Step 2. | Specify the communications protocols you will use. |
Step 3. | Specify a Transaction Manager database. This panel defaults to the first database you connect to (1ST_CONN). You can leave this default or select another catalogued database. |
Step 4. | Specify the types of database servers involved in the update, and also whether or not TCP/IP is to be used exclusively. |
Step 5. | Specify the Syncpoint Manager settings. This page will only appear if the settings on the previous page indicate that you need to use DB2's Syncpoint Manager in a multisite update scenario.
|
Step 1. | Select the instance with the right mouse button and choose the Multisite Update --> Test menu option from the pop-up menu. The Test Multisite Update window opens. |
Step 2. | Select the databases you want to test from the available databases in the Available databases list box. You can use the arrow buttons in the middle to move selections to and from the Selected databases list box. You can also change the selected userid and password by directly editing them in the Selected databases list box. |
Step 3. | When you have finished your selection, click OK at the bottom of the window. The Multisite Update Test Result window opens. |
Step 4. | The Multisite Update Test Result window shows which of the databases you selected succeeded or failed the update test. The window will show SQL codes and error messages for those that failed.
|