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:

Start of changeThis topic describes how to use Oracle with a 32-bit broker, a 64-bit broker, and with either WebSphere MQ V5 or WebSphere MQ V6 and also describes the XAOpenString parameters that you need: End of change

Using Oracle with WebSphere MQ V5 as the transaction coordinator

If you want to use Oracle in coordinated transactions:

  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 install_dir/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 install_dir/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 install_dir/merant/lib/libUKicu20.sl /var/mqm/exits/libUKicu20.sl
    ln –s $ORACLE_HOME/lib/libclntsh.sl /var/mqm/exits/libclntsh.sl

Refer to the information provided for the version of WebSphere MQ that you have installed:

  • 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: Start of change
      XAResourceManager:
      Name=OracleXA
      SwitchFile=install_dir/merant/lib/UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
      +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
      XACloseString=
      ThreadOfControl=THREAD
      End of change
    • On HP-UX: Start of change
      XAResourceManager:
      Name=OracleXA
      SwitchFile=install_dir/merant/lib/UKor8dtc20.sl
      XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
      +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
      XACloseString=
      ThreadOfControl=THREAD
      End of change
    • On Linux (x86 platform): Start of change
      XAResourceManager:
      Name=OracleXA
      SwitchFile=install_dir/merant/lib/UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
      +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
      XACloseString=
      ThreadOfControl=THREAD
      End of change
    • On Solaris: Start of change
      XAResourceManager:  
      Name=OracleXA
      SwitchFile=install_dir/merant/lib/UKor8dtc20.so
      XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
      +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
      XACloseString=
      ThreadOfControl=THREAD
      End of change
    • 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: Start of change
      SwitchFile: C:\WMQI\BIN\UKor8dtc20.dll
      XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
      +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
      ThreadOfControl: THREAD
      End of change

Using Oracle with WebSphere MQ V6 as the transaction coordinator

If you want to use Oracle in coordinated transactions:

  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 and Solaris.
    • UKor8dtc20.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 install_dir/merant/lib/libUKicu20.a /var/mqm/exits/libUKicu20.a
    ln –s $ORACLE_HOME/lib/libclntsh.a /var/mqm/exits/libclntsh.a
    ln –s install_dir/DD64/lib/libUKicu20.a /var/mqm/exits64/libUKicu20.a
    Start of changeln –s install_dir/DD64/lib/UKoradtc20.so /var/mqm/exits64/UKor8dtc20.soEnd of change
    On HP-UX:
    ln –s install_dir/merant/lib/libUKicu20.sl /var/mqm/exits/libUKicu20.sl
    ln –s $ORACLE_HOME/lib/libclntsh.sl /var/mqm/exits/libclntsh.sl
    ln –s <Your install directory>/DD64/lib/libUKicu20.sl /var/mqm/exits64/libUKicu20.sl
    Start of changeln –s install_dir/DD64/lib/UKoradtc20.sl /var/mqm/exits64/UKor8dtc20.slEnd of change
    Start of changeOn Linux (x86 platform):
    ln –s install_dir/merant/lib/libUKicu20.so /var/mqm/exits/libUKicu20.so
    ln –s $ORACLE_HOME/lib/libclntsh.so /var/mqm/exits/libclntsh.so
    End of change
    On Solaris:
    ln –s install_dir/merant/lib/libUKicu20.so /var/mqm/exits/libUKicu20.so
    ln –s $ORACLE_HOME/lib/libclntsh.so /var/mqm/exits/libclntsh.so
    ln –s install_dir/DD64/lib/libUKicu20.so /var/mqm/exits64/libUKicu20.so
    Start of changeln –s install_dir/DD64/lib/UKoradtc20.so /var/mqm/exits64/UKor8dtc20.soEnd of change

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: Start of change
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKor8dtc20.so
    XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
    End of change
  • On HP-UX: Start of change
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKor8dtc20.sl
    XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
    End of change
  • On Linux (x86 platform): Start of change
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKor8dtc20.so
    XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
    End of change
  • On Solaris: Start of change
    XAResourceManager:
    Name=OracleXA
    SwitchFile=UKor8dtc20.so
    XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
    XACloseString=
    ThreadOfControl=THREAD
    End of change
  • 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: Start of change
    SwitchFile: UKor8dtc20.dll
    XAOpenString=ORACLE_XA+SQLNET=myserver+HostName=myhostname+PortNumber=myportnumber+Sid=mySID
    +ACC=P/uid/passwd+sestm=100+threads=TRUE+DataSource=mydatasourcename+DB=mydatasourcename+K=2+
    ThreadOfControl: THREAD
    End of change
Start of change

XAOpenString parameters

Here is a list of the XAOpenString parameters that you need to include in the XAResourceManager stanza in the qm.ini file:
DataSource
The ODBC data source name for the database.
DB
The ODBC data source name for the database.
HostName
The name of the TCPIP host on which the Oracle database resides.
PortNumber
The TCPIP port on which the Oracle database is listening.
Sid
The Oracle System Identifier (SID) of the database.
SQLNET
The Oracle "Service name" that resolves to a "Connect Descriptor", for example through a mapping in the TSNAMES.ORA file.
Related concepts
Message flows overview
End of change