Understanding federated system transaction support

Knowledge of transaction processing concepts in a distributed environment will help you understand federated system transactions.

To understand federated system transaction processing, you should be familiar with the following distributed transaction processing concepts:
  • Unit of work (UOW)
  • Remote unit of work (RUOW)
  • Distributed unit of work (DUOW)
  • Multisite update
  • Transaction manager (TM)
  • Resource manager (RM)
  • Type 1 connection
  • Type 2 connection
  • One-phase commit
  • Two-phase commit

These concepts work identically in both federated and non-federated database systems. However, the scope of each concept changes in a federated system.

For example, a unit of work implicitly begins when any data in a database is read or written. For a unit of work in a federated system, the database can be a federated database or a data source database. For a distributed unit of work in a federated system, you can access both a federated database and a data source database.

An application must end a unit of work by issuing either a COMMIT or a ROLLBACK statement, regardless of the number of databases that are accessed. The COMMIT statement makes all changes within a unit of work permanent. The ROLLBACK statement removes these changes from a database. Changes made by a unit of work become visible to other applications after a successful commit.

Recommendation: Always explicitly commit or roll back units of work in your applications.

In a distributed unit of work that involves updates of multiple databases on multiple sites, data must be consistent. The multisite update or two-phase commit protocol is commonly used to ensure data consistency across multiple databases within a distributed unit of work.

Federated transactions support both one-phase commit protocol and two-phase commit protocol. The DB2_TWO_PHASE_COMMIT server option enables two-phase commit support for the following data sources:
  • Db2® family data sources, in fenced mode and trusted mode
  • Informix®, in trusted mode
  • Oracle, in fenced mode and trusted mode
  • Sybase, in fenced mode
  • MS SQL Server, in trusted mode

When a data source is declared as a federated two-phase commit data source, that is, the DB2_TWO_PHASE_COMMIT server option is set to Y, a commit against this data source uses two-phase commit protocol, even if it is a single site update transaction or a multi site update transaction.

When a data source is declared as a federated one-phase commit data source (the default), and it is a single site update transaction, a commit against this data source uses one-phase commit protocol.

In the following example of a one-phase commit operation, Oracle is defined as a one-phase commit data source: 
SELECT * FROM oracle_nickname
UPDATE oracle_nickname
COMMIT
In the following example of a two-phase commit operation, Oracle and DRDA are defined as two-phase commit data sources: 
SELECT * FROM oracle_nickname
UPDATE oracle_nickname

SELECT * FROM drda_nickname
UPDATE drda_nickname
COMMIT