IBM Books

Administration Guide

Using Multiple Databases in a Single Transaction

When using multiple databases in a single transaction, the requirements for setting up and administering your environment are different, depending on the number of databases that are being updated in the transaction. For more information, see:

Updating a Single Database

If your data is distributed across multiple databases, you may wish to update one database while reading from one or more other databases. This type of access can be performed within a single unit of work (transaction). This type of database access is called multisite update or two-phase commit. See Updating Multiple Databases for another example of a multisite update.

Figure 46. Using Multiple Databases in a Single Transaction


sqld0dw1


Figure 46 shows an example of a database client running a funds transfer application that accesses two database servers: one containing the checking and savings accounts and another containing the banking fee schedule. This example is similar to the example provided in Using a Single Database in a Transaction, except for the number of databases and the location of the tables. As discussed previously, the application performing the transfer includes the following steps:

  1. Accept the amount to transfer from the user interface

  2. Subtract the amount from the savings account and determine the new balance

  3. Read the fee schedule to determine the transaction fee for a savings account with the given balance

  4. Subtract the transaction fee from the savings account

  5. Add the amount of the transfer to the checking account

  6. Commit the transaction (unit of work).

To set up the above environment, you must:

  1. Create the necessary tables in the appropriate databases (Chapter 4, Implementing Your Design)

  2. (If physically remote...) Set up the database servers to use the appropriate communications protocols, as described in the Quick Beginnings manuals

  3. (If physically remote...) Catalog the nodes and databases to identify the databases on the above database servers, as described in the Quick Beginnings manuals

  4. Pre-compile your application program, as described in the Application Development Guide to specify:
    1. A type 2 connection, that is, specify CONNECT(2) on the PREP command
    2. One-phase commit, that is SYNCPOINT(ONEPHASE) on the PREP command.

Performance Tip: You should note that, unlike the scenario described in Updating Multiple Databases, updating a single database while reading multiple databases only requires a one-phase commit (SYNCPOINT(ONEPHASE) on PREP command). Using a one-phase commit process requires less overhead than a two-phase commit process. Therefore, performance is better when using SYNCPOINT(ONEPHASE) rather than SYNCPOINT(TWOPHASE) for applications that only update a single database within a unit of work.

Host and AS/400 Server Additional Information:

Federated Server and Pass-Through Session Additional Information:

Updating Multiple Databases

If your data is distributed across multiple databases, you may also wish to read and update several databases in a single transaction. This type of database access is called a multisite update. This type of environment is more complex than that described in Updating a Single Database. As a result, additional topics will be introduced below.

Figure 47. Updating Multiple Databases


sqld0dw2


Figure 47 shows an example similar to Figure 46, except the checking and savings accounts are located in different databases. The application performing the transfer includes the same steps as described in Updating a Single Database.

  1. Accept the amount to transfer from the user interface

  2. Subtract the amount from the savings account and determine the new balance

  3. Read the fee schedule to determine the transaction fee for a savings account with the given balance

  4. Subtract the transaction fee from the savings account

  5. Add the amount of the transfer to the checking account

  6. Commit the transaction (unit of work).

To set up the above environment, you must:

  1. Create the necessary tables in the appropriate databases (Chapter 4, Implementing Your Design)

  2. (If physically remote...) Set up the database servers to use the appropriate communications protocols, as described in the Quick Beginnings manuals

  3. (If physically remote...) Catalog the nodes and databases to identify the databases on the above database servers, as described in the Quick Beginnings manuals

  4. Pre-compile your application program, as described in the Application Development Guide to specify:
    1. A type 2 connection, that is, specify CONNECT(2) on the PREP command
    2. Two-phase commit, that is SYNCPOINT(TWOPHASE) on the PREP command.

  5. Configure the DB2 transaction manager (TM), as described in Using the DB2 Transaction Manager. This section also provides information about how the two-phase commit process works.

Using the DB2 Transaction Manager

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 (unit of work) and to track the completion status of that transaction.

If you are using an XA-compliant transaction manager such as IBM TXSeries, BEA Tuxedo, or Microsoft Transaction Series, please see Chapter 11, Using DB2 with an XA-Compliant Transaction Manager for integration instructions.

When using DB2 UDB to coordinate your transactions you need to meet certain configuration requirements. If you use TCP/IP exclusively for communications and DB2 UDB and DB2 for OS/390 are the only database server involved in your transactions then configuration is simplified over environments that do not meet these criteria.

DB2 UDB and DB2 for OS/390 Using TCP/IP Connectivity:  

If all the following are true in your environment:

then the configuration steps for multisite update are simplified. There is no need to catalog the Transaction Manager Database at each remote database server. Nor is there a need to catalog each remote database server at the Transaction Manager database instance. This information is exchanged between the DB2 client, the designated transaction manager database instance, and the DB2 UDB and/or DB2 for OS/390 database servers automatically without manual database configuration.

The database that will be used as the transaction manager database is determined at the database client by the database manager configuration parameter tm_database. Refer to "Configuring DB2" in Administration Guide, Performance for more information on this configuration parameter. Consider the following factors when setting this configuration parameter:

Other Environments:  

If your transactions involve any of the following situations:

then the configuration steps for multisite update are more involved than the preceding discussion.

The database that will be used as the transaction manager database is determined at the database client by the database manager configuration parameter tm_database. Refer to "Configuring DB2" in Administration Guide, Performance for more information on this configuration parameter. Consider the following factors when setting this configuration parameter:

The above rules regarding cataloging of aliases affect your ability to recover from problems (see Recovering from Problems During Two-Phase Commit).

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]