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.
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, which is called multisite update, or two-phase commit, can be performed within a single unit of work (transaction). See Updating Multiple Databases for another example of a multisite update.
Figure 39. Using Multiple Databases in a Single Transaction
|
Figure 39 shows 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 one shown in Figure 38, except for the number of databases, and the location of the tables.
To set up a funds transfer application for this environment, you must:
If databases are located on a host or AS/400 database server, you require DB2 Connect for connectivity to these servers. For information about setup, refer to one of the DB2 Connect Quick Beginnings books. For information about using DB2 Connect, refer to the DB2 Connect User's Guide.
If your data is distributed across multiple databases, you may want to read and update several databases in a single transaction. This type of database access is called a multisite update.
Figure 40. Updating Multiple Databases in a Single Transaction
 |
Figure 40 shows a database client running a funds transfer application that accesses three database servers: one containing the checking account, another containing the savings account, and the third containing the banking fee schedule.
To set up a funds transfer application for this 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 and track its completion status.
If you are using an XA-compliant transaction manager, such as IBM TXSeries, BEA Tuxedo, or Microsoft Transaction Server, see Chapter 10, Designing for Transaction Managers for integration instructions.
When using DB2 UDB for UNIX based systems, Windows operating systems, or OS/2 to coordinate your transactions, you must fulfill certain configuration requirements. If you use TCP/IP exclusively for communications, and DB2 UDB and DB2 for OS/390 are the only database servers involved in your transactions, configuration is straightforward.
If each of the following statements is true for your environment, the configuration steps for multisite update are straightforward.
The DB2 Connect sync point manager is configured automatically at DB2 instance creation time, and is required when:
This applies to both SNA and TCP/IP connectivity with host or AS/400 database servers. For detailed information, see Chapter 10, Designing for Transaction Managers. If your environment does not require the DB2 Connect sync point manager, you can turn it off by issuing the command db2 update dbm cfg using spm_name NULL at the DB2 Connect server. Then stop and restart DB2.
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. For more information about this configuration parameter, see Chapter 32, Configuring DB2 . 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, reducing 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; that is, if:
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, in your environment:
the configuration steps for multisite update are more involved.
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. For more information about this configuration parameter, see Chapter 32, Configuring DB2 . Consider the following factors when setting this configuration parameter:
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; that is, if:
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.