Configuring databases for coordinated message flows

If your message flow interacts with a database, and you want to coordinate the updates made to the database with other actions within the message flow, configure your broker to manage these updates.

Before you start:

To complete this task, you must have completed the following task:

Update the broker queue manager information by defining an XAResourceManager stanza for each of the databases that will participate in globally coordinated transactions when updated by message flows on that broker.

If your message flows reference message dictionaries, or contain Publication nodes, you must also define an XAResourceManager stanza for the broker internal database using the same method.

Using DB2

If you want to use DB2 in coordinated transactions:

  • If you use DB2 Version 7, use the supplied switch file; a file is provided for each supported platform. These files are installed in directory install_dir/sample/xatm.
  • If you use DB2 Version 8, download the fix for APAR IY46106. This provides a tar file that contains the switch files for all supported platforms.

If you prefer to build your own files, follow the instructions in the section called "DB2 configuration" in the WebSphere MQ System Administration Guide. You can also find details of how to configure XA resource definitions in this book.

Using Oracle

If you want to use Oracle in coordinated transactions, follow the general instructions in the section called "Oracle configuration" in the WebSphere MQ System Administration book, and use these with the instructions given here. In particular, you must provide a symbolic link to the 32-bit Oracle client library libclntsh from /usr/lib.

  1. Ensure that the user ID that is used to access the database and specified in the XAOpenString has the necessary Oracle privileges to access the DBA_PENDING_TRANSACTIONS view. You can grant the required access using the following Oracle SQLPLUS command:
    	grant select on DBA_PENDING_TRANSACTIONS to <userid>;
  2. Use the switchfile supplied by WebSphere Business Integration Message Broker. When you add the XAResourceManager configuration information for Oracle, specify:
    • UKor8dtc18.so as the switchfile on AIX and Solaris
    • libSwitchOracle.sl as the switchfile on HP-UX
    • UKor8dtc18.dll as the switchfile on Windows

The following examples show what you must include in the XAResourceManager stanza in the qm.ini file on UNIX systems, and the equivalent information for Windows:

  • On AIX:
    XAResourceManager:
    Name=Oracle8
    SwitchFile=/usr/opt/wmqi/merant/lib/UKor8dtc18.so
    XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
        threads=TRUE+DB=ORACLEDB
    XACloseString=
    ThreadOfControl=THREAD
  • On HP-UX:
    XAResourceManager:
    Name=Oracle8
    SwitchFile=/opt/wmqi/merant/lib/libSwitchOracle.sl
    XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
        threads=TRUE+DB=ORACLEDB
    XACloseString=
    ThreadOfControl=THREAD

    You must run the following command (including the initial period and the space that follows it) before you start a queue manager that you have configured in this way:

    . /opt/wmqi/sample/profiles/profile.hpux
  • On Solaris:
    XAResourceManager:  
    Name=Oracle8
    SwitchFile=/opt/wmqi/merant/lib/UKor8dtc18.so
    XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
        threads=TRUE+DB=ORACLEDB
    XACloseString=
    ThreadOfControl=THREAD
  • On Windows, set the following values on the Resources page of the properties dialog for your WebSphere MQ queue manager (accessible from WebSphere MQ Services). This example assumes that you have installed WebSphere Business Integration Message Broker in the directory C:\WMQI:
    SwitchFile: C:\WMQI\BIN\UKor8dtc18.dll
    XAOpenString: ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
        threads=TRUE+DB=ORACLEDB
    ThreadOfControl: THREAD

Using Sybase

If you want to use Sybase in coordinated transactions, follow the general instructions in the section called "Sybase configuration" in the WebSphere MQ System Administration book, and use these with the instructions given here. WebSphere Business Integration Message Broker supports only Sybase Version 12.

  1. Define each ODBC Data Source Name (DSN) as a Logical Resource Manager (LRM) in the Sybase XA configuration file $SYBASE/OCS-12_5/xa_config.
  2. Ensure that the DataSourceUserid specified when the broker is created is a user ID that has been granted the Sybase role of dtm_tm_role.
  3. Use the switchfile supplied by WebSphere Business Integration Message Broker. When you add the XAResourceManager configuration information for Sybase, specify:
    • UKase18.so as the switchfile on AIX and Solaris
    • libSwitchSybase.sl as the switchfile on HP-UX
    • UKase18.dll as the switchfile on Windows
  4. Specify the ODBC DSN name of your database in the -N parameter of the XAOpenString, and the network address of your database in the -A parameter.

The following examples show what you must include in the XAResourceManager stanza in the qm.ini file on UNIX systems, and the equivalent Windows information. In each example, uid represents the user login and pwd the user ID's password.

  • On AIX:
    XAResourceManager:
        Name=SYBASEXA
        SwitchFile=/usr/opt/wmqi/merant/lib/UKase18.so
        XAOpenString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        XACloseString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        ThreadOfControl=THREAD
  • On HP-UX:
    XAResourceManager:
        Name=SYBASEXA
        SwitchFile=/opt/wmqi/merant/lib/libSwitchSybase.sl
        XAOpenString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        XACloseString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        ThreadOfControl=THREAD

    You must run the following command (including the initial period and the space that follows it) before you start a queue manager that you have configured in this way:

    . /opt/wmqi/sample/profiles/profile.hpux
  • On Solaris:
    XAResourceManager:
        Name=SYBASEXA
        SwitchFile=/opt/wmqi/merant/lib/UKase18.so
        XAOpenString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        XACloseString=-NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
        ThreadOfControl=THREAD
  • On Windows, set the following values on the Resources page of the properties dialog for your WebSphere MQ queue manager (accessible from WebSphere MQ Services). This example assumes you have installed WebSphere Business Integration Message Broker in the directory C:\WMQI:
    SwitchFile: C:\WMQI\BIN\UKase18.dll
    XAOpenString: -NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
    XACloseString: -NSYBASEDB -A10.30.14.72,5000 -WWinsock -Uuid -Ppwd -K2
    ThreadOfControl: THREAD

Related concepts
Message flows

Related tasks
Accessing databases from message flows
Configuring coordinated message flows
Configuring message flow nodes for coordinated message flows
Configuring WebSphere MQ and RRS for coordinated message flows

Related reference
Supported databases
Built-in nodes

WebSphere MQ library Web page