Understanding federated system transaction support

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.

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.

SELECT * FROM oracle_nickname
UPDATE oracle_nickname
COMMIT

SELECT * FROM oracle_nickname
UPDATE oracle_nickname

SELECT * FROM drda_nickname
UPDATE drda_nickname
COMMIT