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 distributed unit of work. See "Updating Multiple Databases" for another example of distributed unit of work.

Figure 34. Using Multiple Databases in a Single Transaction


* Figure SQLD0DW1 not displayed.


Figure 34 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 3. "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 Embedded SQL Programming 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.

DRDA Application Server 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 distributed unit of work. 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 35. Updating Multiple Databases


* Figure SQLD0DW2 not displayed.


Figure 35 shows an example similar to Figure 34, 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 3. "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 Embedded SQL Programming 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 updating 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.

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 (see "Transaction Manager Database Name (tm_database)"). 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 ]