Configuring Microsoft SQL Server data sources

There are several requirements and restrictions for using Microsoft SQL Server data sources for federated two-phase commit.

Before you begin

Before you begin

Restrictions:
  • For Microsoft SQL Server data sources, federated two-phase commit is only supported by Federation component installed on Windows.
  • The Db2® isolation level is not propagated to the Microsoft SQL server.
Requirements:
  • For federated two-phase commit to work with Microsoft SQL, an extra server option, XA_OPEN_STRING_OPTIONS, must be added to the server:
    alter server S1 options(add xa_open_string_options 
'RMRecoveryGuid=c200e360-38c5-11ce-ae62-08002b2b79ef');

    where RMRecoveryGuid = resource manager ID.

    The resource manager ID is available in the following location of the Microsoft SQL Server Registry: 
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer]         
	"ResourceMgrID" = "{resource manager ID}"

About this task

Procedure

To configure a Microsoft SQL Server data source:

Procedure

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

    The Microsoft SQL Server wrapper automatically generates the following XA OPEN string for Microsoft SQL Server data sources:

    TM=tmname
  2. Optional: Specify additional XA options in addition to the required RMRRecovery Guid value by using the XA_OPEN_STRING_OPTIONS server option.