Enabling two-phase commit for federated transactions

To use federated two-phase commit for specific data sources, you must enable the associated federated servers. The enablement process involves preparing the federated server and modifying the data source server definition.

Before you begin

  • When you enable federated two-phase commit for a data source, you increase the number of records that are written to both the federated server database log and the data source database log. Consider the impact this has on the administration and maintenance of these log files in order to ensure that they comply with your local policies.
  • The data source that you want to use must be a supported federated two-phase commit data source.
  • Federated two-phase commit is not supported in the massively parallel processing (MPP) environment.
  • Federated two-phase commit is supported in the fenced environment for the following data sources:
    • Db2® family data sources support fenced mode and trusted mode.
    • Informix® supports trusted mode.
    • Oracle supports fenced mode and trusted mode.
    • Sybase supports fenced mode.
    • MS SQL Server supports trusted mode.
  • Federated two-phase commit is supported in the Db2 pureScale environment for the following data sources:
    • Db2 for z/OS®
    • Db2 for IBM® i
    • Db2
    You can perform federated two-phase commit transactions for all participant servers. The following restrictions apply to this support:
    • Only type 1 inbound connections are supported. Connections to Federation Server through the XA interface are not supported.
    • Indoubt transactions cannot be resynchronized or resolved by the owner member when that member is in restart light mode. The indoubt transactions can be resolved, automatically or manually, only after the owner member falls back to its home host.
  • For Db2 for IBM i,version 5.3, and earlier and Db2 for z/OS data sources, ensure that the configuration parameter SPM_NAME is set to the default value, the server host name. SPM_NAME defaults to a variant of the first seven characters of the TCP/IP host name. Db2 for IBM i, version 5.4, and later does not require that you set SPM_NAME.

About this task

About this task

The DB2_TWO_PHASE_COMMIT server option enables two-phase commit for data sources. You register a data source server definition by using the CREATE SERVER statement. The value you set for DB2_TWO_PHASE_COMMIT persists for all connections that are established under that server definition. You can change the value at any time by using the ALTER SERVER statement. After the CREATE SERVER or ALTER SERVER statement is successfully committed, the new setting is available for use on subsequent outbound connection requests.

Clients and application programs can use the SET SERVER OPTION to temporarily override the current value of the DB2_TWO_PHASE_COMMIT server option. The SET SERVER OPTION statement must be run immediately after the connection to the federated server database and before any connections are established to the remote data sources. The command is in effect only for the duration of the connection to the federated database. You cannot change the DB2_TWO_PHASE_COMMIT server option once the federated server has established a connection to the remote data source.

When you include the XA_OPEN_STRING_OPTIONS option in a CREATE SERVER statement, you can embed specialized information in the default XA_OPEN string. This embedded information can be any of the following kinds of information:

  • Unique IDs for transactions in addition to what Federation component provides
  • User-defined parameters about how transactions are handled
  • A user-defined string to append to the XA_OPEN request
When an XA_OPEN call is made, usually at the beginning of the first transaction to a remote data source that uses two-phase commit, the wrapper appends the value of the user-defined string onto the default XA_OPEN string for the XA_OPEN call.

You can include both DB2_TWO_PHASE_COMMIT and XA_OPEN_STRING_OPTIONS in a CREATE SERVER, SET SERVER, or ALTER SERVER statement.

Procedure

  1. Run the CREATE SERVER, ALTER SERVER, or SET SERVER statement with the DB2_TWO_PHASE_COMMIT option set to Y.
  2. Optional: Run the CREATE SERVER, ALTER SERVER, or SET SERVER statement with the XA_OPEN_STRING_OPTIONS option.

Server option examples

This example shows how to set two-phase commit by using the CREATE SERVER statement:

CREATE  SERVER  Net8_Server  TYPE  ORACLE VERSION  8.1.7 WRAPPER  NET8
OPTIONS (DB2_TWO_PHASE_COMMIT  'Y');

This example shows how to disable two-phase commit by using the ALTER SERVER statement:

ALTER  SERVER  Net8_Server OPTIONS  (SET  DB2_TWO_PHASE_COMMIT  'N');

This example shows how to set an XA trace file to D:\Temp\sybase_xa.log for the Sybase wrapper using the ALTER SERVER statement and the XA_OPEN_STRING_OPTIONS server option:

 ALTER SERVER Ctlib_Server OPTIONS (ADD XA_OPEN_STRING_OPTIONS 
'-LD:\Temp\sybase_xa.log');

This example shows how to temporarily disable two-phase commit by using the SET SERVER OPTION statement:

SET  SERVER  OPTION  DB2_TWO_PHASE_COMMIT TO 'N'  FOR SERVER  Net8_Server;