Configuring databases for coordinated message flows with Oracle

If your message flow interacts with an Oracle 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:

Using Oracle with a 32 bit broker

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 /var/mqm/exits.

  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 Message Broker. When you add the XAResourceManager configuration information for Oracle, specify:
    • UKor8dtc20.so as the switchfile on AIX, Solaris and Linux x86 platform.
    • UKor8dtc20.sl as the switchfile on HP-UX
    • UKor8dtc20.dll as the switchfile on Windows
  3. Create the following symbolic links:
    On AIX:
    ln –s <Your install directory>/merant/lib/libUKicu20.a /var/mqm/exits/libUKicu20.a
    ln –s $ORACLE_HOME/lib/libclntsh.a /var/mqm/exits/libclntsh.a
    On Solaris and Linux x86 platform:
    ln –s <Your install directory>/merant/lib/libUKicu20.so /var/mqm/exits/libUKicu20.so
    ln –s $ORACLE_HOME/lib/libclntsh.so /var/mqm/exits/libclntsh.so
    On HP-UX:
    ln –s <Your install directory>/merant/lib/libUKicu20.sl /var/mqm/exits/libUKicu20.sl
    ln –s $ORACLE_HOME/lib/libclntsh.sl /var/mqm/exits/libclntsh.sl
  • For coordination by WebSphere MQ V5:

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

    • On AIX:
      XAResourceManager:
      Name=OracleXA
      SwitchFile=<Your install directory>/merant/lib/UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On HP-UX:
      XAResourceManager:
      Name=OracleXA
      SwitchFile=<Your install directory>/merant/lib/UKor8dtc20.sl
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On Linux (x86 platform):
      XAResourceManager:
      Name=OracleXA
      SwitchFile=<install_dir>/merant/lib/UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On Solaris:
      XAResourceManager:  
      Name=OracleXA
      SwitchFile=<install_dir>/merant/lib/UKor8dtc20.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 Message Broker in the directory C:\WMQI:
      SwitchFile: C:\WMQI\BIN\UKor8dtc20.dll
      XAOpenString: ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      ThreadOfControl: THREAD
  • For coordination by WebSphere MQ V6:

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

    • On AIX:
      XAResourceManager:
      Name=OracleXA
      SwitchFile=UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On HP-UX:
      XAResourceManager:
      Name=OracleXA
      SwitchFile=UKor8dtc20.sl
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On Linux (x86 platform):
      XAResourceManager:
      Name=OracleXA
      SwitchFile=UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      XACloseString=
      ThreadOfControl=THREAD
    • On Solaris:
      XAResourceManager:  
      Name=OracleXA
      SwitchFile=UKor8dtc20.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 Message Broker in the directory C:\WMQI:
      SwitchFile: C:\WMQI\BIN\UKor8dtc20.dll
      XAOpenString: ORACLE_XA+SQLNET=myserver+ACC=P/scott/tiger+sestm=0+
          threads=TRUE+DB=ORACLEDB
      ThreadOfControl: THREAD

Using Oracle with a 64 bit broker

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.

  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 Message Broker. When you add the XAResourceManager configuration information for Oracle, specify:
    • UKoradtc20.so as the switchfile on AIX and Solaris.
    • UKoradtc20.sl as the switchfile on HP-UX.
  3. Specify the hostname of the machine for the Oracle server, the port number on which it is listening, the Oracle Service ID (SID), the username and password that is to be used to access the database, and the name of the database to be coordinated.
  4. Create the following symbolic link.
    On AIX:
    ln –s <Your install directory>/DD64/lib/libUKicu20.a /var/mqm/exits64/libUKicu20.a
    On Solaris:
    ln –s <Your install directory>/DD64/lib/libUKicu20.so /var/mqm/exits64/libUKicu20.so
    On HP-UX:
    ln –s <Your install directory>/DD64/lib/libUKicu20.sl /var/mqm/exits64/libUKicu20.sl

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=OracleXA
    SwitchFile=UKoradtc20.so
    XAOpenString=ORACLE_XA+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/myuid/mypasswd+SesTM=100+DataSource=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
  • On HP-UX:
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKoradtc20.sl
    XAOpenString=ORACLE_XA+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/myuid/mypasswd+SesTM=100+DataSource=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
  • On Solaris:
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKoradtc20.so
    XAOpenString=ORACLE_XA+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/myuid/mypasswd+SesTM=100+DataSource=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
Related concepts
Message flows overview