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:
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 37. Using Multiple Databases in a Single Transaction
|
Figure 37 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:
To set up the above environment, you must:
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:
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 38. Updating Multiple Databases
 |
Figure 38 shows an example similar to Figure 37, 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".
To set up the above environment, you must:
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 refer to Chapter 9. "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.
If all the following are true in your environment:
The DB2 Syncpoint Manager is required when:
This applies to both SNA and TCP/IP connectivity with host or AS/400 database servers. For detailed information, see Chapter 9. "Using DB2 with an XA-Compliant Transaction Manager".
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 (see "Transaction Manager Database Name (tm_database)"). Consider the following factors when setting this configuration parameter:
This is the recommended database server to use as the transaction manager database. OS/390 systems are, generally, more secure than workstation servers. This reduces the possibility of accidental power downs, reboots, and so on. Therefore the recovery logs, used in the event of resynchronization, are more secure.
Care must be taken when using 1ST_CONN. You should only use this configuration if it is easy to ensure that all involved databases are cataloged correctly, for example, in the following situations:
Note that if your application attempts to disconnect from the database being used as the transaction manager database, you will receive a warning message and the connection will be held until the unit of work is committed.
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 (see "Transaction Manager Database Name (tm_database)"). Consider the following factors when setting this configuration parameter:
This is the recommended database.
CONNECT TO DB2TRMGR
The result of this command should connect you to the same database, on the same node from every participating instance, as well as the database client.
| Note: | The transaction manager database must not be cataloged using the alias option to specify an alternative name. |
Care must be taken when using 1ST_CONN. You should only use this configuration if it is easy to ensure that all involved databases are cataloged correctly, for example, in the following situations:
Note that if your application attempts to disconnect from the database being used as the transaction manager database, you will receive a warning message and the connection will be held until the unit of work is committed.
The above rules regarding cataloging of aliases affect your ability to recover from problems (see "Recovering from Problems During Two-Phase Commit").