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
- 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
You can include both DB2_TWO_PHASE_COMMIT and XA_OPEN_STRING_OPTIONS in a CREATE SERVER, SET SERVER, or ALTER SERVER statement.
- Run the CREATE SERVER, ALTER SERVER, or SET SERVER statement with the DB2_TWO_PHASE_COMMIT option set to Y.
- 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;