The following example illustrates the steps of the example transaction
(described in "Updating Multiple Databases") and the participants in the transaction. If an error occurs during
the two-phase commit process, understanding how a transaction is managed will
help you to resolve the problem.
Figure 40. Updating Multiple Databases
- The application is prepared for two-phase commit. This can be
accomplished through precompilation options (refer to the Embedded SQL Programming Guide for details). This can also be accomplished through the
DB2 CLI configuration (refer to the CLI Guide and
Reference for details).
- When the database client wants to connect to SAVINGS_DB, it first
internally connects to the Transaction Manager (TM) database. The TM
database returns an acknowledgment to the database client.
- The connection to the SAVINGS_DB takes place and is acknowledged.
- The database client begins the update to the SAVINGS_ACCOUNT table.
This begins the unit of work. The TM database responds to the database
client providing a transaction ID for the unit of work. Note that the
registration of a unit of work occurs when the first SQL statement in the unit
of work is run, not necessarily during connect time.
- After receiving the transaction ID, the database client registers the unit
of work with the database containing the SAVINGS_ACCOUNT table. A
response is sent back to the client to indicate that the unit of work has been
- SQL statements issued against the SAVINGS_DB are handled in the normal
manner. The response to each statement is returned in the SQLCA when
working with SQL statements embedded in a program. (The SQLCA is
described in the Embedded SQL Programming Guide and the SQL Reference.)
- The transaction ID is registered at the FEE_DB database containing the
TRANSACTION_FEE table, during the first access to that database within the
unit of work.
- Any SQL statements against the FEE_DB database are handled in the normal
- Additional SQL statements can be executed against the SAVINGS_DB by
setting the connection as appropriate. Since the unit of work has
already been registered with the SAVINGS_DB (4), the database client
does not need to perform the registration step again.
- Connecting to and using the CHECKING_DB follows the same rules as
described by (6) and (7).
- When the database client requests that the unit of work be committed, a
prepare message is sent to all databases participating in the unit
of work. Each database writes a "PREPARED" record to their log
files and replies to the database client.
- After the database client receives a positive response from all of the
databases, it sends a message to the transaction manager database to inform it
that the unit of work is now ready to be committed (PREPARED). The
transaction manager database writes a "PREPARED" record to its log file
and sends a reply to inform the client that the second phase of the commit
process can be started.
- During the second phase of the commit process, the database client sends a
message to all participating databases to tell them to commit. Each
database writes a "COMMITTED" record to its log file and releases the
locks that were held for this unit of work. When the database has
completed committing the changes, it sends a reply to client.
- After the database client receives a positive response from all
participating databases, it sends a request to the transaction manager
database to inform it that the unit of work has been completed. The
transaction manager database then:
- Writes a "COMMITTED" record to its log file, to indicate that the
unit of work is complete
- Replies to the client to indicate it has finished.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
[ DB2 List of Books |
Search the DB2 Books ]