Oracle configuration

Do the following:

• Check Oracle level and apply patches if you have not already done so.
• Check environment variable settings.
• Enable Oracle XA support.
• Create the Oracle switch load file.
• Add resource manager configuration information.
• Change the Oracle configuration parameters, if necessary.

Minimum supported levels for Oracle and applying patches

You need to install Oracle patches 437448 and 441647. The minimum supported levels of Oracle on each of the platforms are:

Windows systems

8.1.5

AIX

7.3.2.1

HP-UX

7.3.2.3

Solaris

7.3.2.3

Checking the environment variable settings

Ensure that your Oracle environment variables are set for queue manager processes as well as in your application processes. In particular, always set the following environment variables before starting the queue manager:

ORACLE_HOME

The Oracle home directory

ORACLE_SID

The Oracle SID being used

Enabling Oracle XA support on Windows systems

Here we tell you how to set up Oracle version 8.1.5 as an XA transaction manager on Windows systems. The process is:

1. Create the Oracle switch load file
2. Add resource manager configuration information
3. Set up the database
4. Start the queue manager

The following files are used:

• oraswit.c
• oraswitd.c
• oraswitd.def
• xaswit.mak for Microsoft Visual C++, or xaswiti.mak for IBM VisualAge for C++

Creating the Oracle switch load file

1. Edit the xaswit.mak or xaswiti.mak file (as appropriate to the compiler you are using), and replace the path c:\oracle\ora81 with the path in which your Oracle installation is located. This path is used to locate the Oracle XA switch DLL.
2. Copy the files from C:\Program Files\IBM\WebSphere MQ\tools\c\samples\xatm to a temporary directory.
3. Change directory to this temporary directory.
4. Make the Oracle switch load file:
   • If you are using Microsoft Visual C++, type:

     nmake /f xaswit.mak oraswit.dll
     

   • If you are using IBM VisualAge for C++, type:

     nmake /f xaswiti.mak oraswit.dll
     

   You can store the oraswit.dll file anywhere on your system, as long as it is accessible to users in the mqm group and the user under which the application program runs.

Adding resource manager configuration information for Oracle

The next step is to modify the configuration information for the queue manager to define Oracle as a participant in global units of work. Use the WebSphere MQ Services snap-in, as follows:

1. Right-click the queue manager you want to use as an XA Transaction Manager.
2. Select Properties and, in the properties notebook, select the Resources tab.
3. In the SwitchFile box, enter the full path to the DLL you have just built.
4. Fill in the other text boxes in the Resources tab as described below.

Name=name (mandatory)

A suitable name for this participant. You might include the name of the database being updated.

SwitchFile=name (mandatory)

The fully-qualified name of the Oracle switch load file.

XAOpenString=string

This has the following settings:

Acc= (mandatory)

User access information.

SesTm= (mandatory)

The maximum amount of time that a transaction can be inactive before the system automatically deletes it. The unit of time is in seconds.

DB=

The database name. This field is required only when applications explicitly specify the database name (that is, use an AT clause in their SQL statements).

GPwd=

The server security password. Server security groups provide an extra level of protection for different applications running against the same Oracle instance. The default is an Oracle-defined server security group.

LogDir=

The directory on a local machine where the Oracle XA library error and tracing information can be logged. If a value is not specified, the current directory is assumed. Make sure that users in the mqm group have write access to this directory.

MaxCur=

The number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option, maxopencursors.

SqlNet=

The SQL*Net connect string used to log on to the system. The connect string can be either an SQL*Net V1 string, SQL*Net V2 string, or SQL*Net V2 alias. This field is required when you are setting up Oracle on a machine separate from the queue manager.

See the Oracle7 Server Distributed Systems, Volume 1: Distributed Data book (Part Number A32543-1) or the Oracle8 Server Application Developer's Guide (Part Number A54642-01) for more information.

XACloseString=string

Oracle does not require an XA close string.

ThreadOfControl=THREAD|PROCESS

Used by the queue manager for serialization when it needs to call the resource manager from one of its own multithreaded processes. If you set this to THREAD, you must add Threads=TRUE to the XAOpen string.

For fuller descriptions of each of these attributes, see "XA resource managers".

Setting up the database

You need to grant certain privileges to the user you have created in the database, such as the CREATE SESSION privilege. See the Oracle 8i Application Developer's Guide books.

Starting the queue manager

Start your queue manager, and check the AMQERR01.LOG file for error messages. To do this look in C:\Program Files\IBM\WebSphere MQ\qmname\errors directory. If there are no error messages, your queue manager has made successful contact with your Oracle server.

Enabling Oracle XA support on UNIX systems

You need to ensure that Oracle XA support is enabled. In particular, there must be an Oracle shared library; this is usually created when you install the Oracle XA library. On Oracle7, you might be prompted with:

  Some TP Monitors require a shared version of the ORACLE7 libraries.
  Do you want to install a shared version of the libraries?

Make sure you answer Yes to this prompt. This creates a shared library called libclntsh in the $ORACLE_HOME/lib directory.

While installing Oracle8, the library is built automatically. You are not prompted as described for Oracle7 above.

If you need to rebuild the library, enter the following while you are logged on as an Oracle administrator:

• For Oracle7:

  cd $ORACLE_HOME/rdbms/lib
  make -f clntsh.mk libclntsh
  

• For Oracle8:

  cd $ORACLE_HOME/rdbms/lib
  make -f ins_rdbms.mk client_sharedlib
  

For more information, refer to:

• The Oracle7 Administrator's Reference for UNIX book.
• The Oracle8 Administrator's Reference book appropriate to your platform.

The queue manager loads the XA switch when it starts up. The platform-specific environment variables (LIBPATH for AIX, LD_LIBRARY_PATH for Solaris, and SHLIB_PATH for HP-UX) are not passed to the queue manager processes from the shell in which strmqm is called. Use another method so that the shared objects can be located when the queue manager starts up. You can do this either by:

• Imbedding the path to libclntsh in the XA switch load file when it is built. This is the recommended method.
• Providing a symbolic link to libclntsh from /usr/lib. (/usr/lib is, by default, searched for shared objects if none are found in the paths included by the first method.)

make -f xaswit.mak oraswit

The generated switch file is placed in your current directory.

Adding resource manager configuration information for Oracle

The next step is to modify the configuration information for the queue manager to define Oracle as a participant in global units of work. Use the XAResourceManager stanza of the qm.ini configuration file.

Name=name (mandatory)

A suitable name for this participant. You could include the name of the database being updated.

SwitchFile=name (mandatory)

The fully-qualified name of the Oracle switch load file.

XAOpenString=string

The XA open string for Oracle has the following format:

     Oracle_XA+Acc=P//|P/userName/passWord
              +SesTm=sessionTimeLimit
              [+DB=dataBaseName]
              [+GPwd=P/groupPassWord]
              [+LogDir=logDir]
              [+MaxCur=maximumOpenCursors]
              [+SqlNet=connectString]

where:

Acc= (mandatory)

User access information. P// indicates that no explicit user or password information is provided and that the ops$login form is to be used. P/userName/passWord indicates a valid Oracle user ID and the corresponding password.

SesTm= (mandatory)

The maximum amount of time that a transaction can be inactive before the system automatically deletes it. The unit of time is in seconds.

DB=

The database name, where dataBaseName is the name Oracle precompilers use to identify the database. This field is required only when applications explicitly specify the database name (that is, use an AT clause in their SQL statements).

GPwd=

The server security password, where P/groupPassWord is the server security group password name. Server security groups provide an extra level of protection for different applications running against the same Oracle instance. The default is an Oracle-defined server security group.

LogDir=

The directory on a local machine where the Oracle XA library error and tracing information can be logged. If a value is not specified, the current directory is assumed. Make sure that user mqm has write access to this directory.

MaxCur=

The number of cursors to be allocated when the database is opened. It serves the same purpose as the precompiler option, maxopencursors.

SqlNet=

The SQL*Net connect string used to log on to the system. The connect string can be either an SQL*Net V1 string, SQL*Net V2 string, or SQL*Net V2 alias. This field is required when you are setting up Oracle on a machine separate from the queue manager.

See the Oracle7 Server Distributed Systems, Volume 1: Distributed Data book (Part Number A32543-1) or the Oracle8 Server Application Developer's Guide (Part Number A54642-01) for more information.

XACloseString=string

Oracle does not require an XA close string.

ThreadOfControl=THREAD|PROCESS

Oracle is thread-aware, so specify THREAD.

For fuller descriptions of each of these attributes, see "XA resource managers".

In Figure 18, the database to be updated is called MQBankDB. We recommend that you add a LogDir to the XA open string so that all error and tracing information is logged to the same place. It is assumed that the Oracle switch load file was copied to the /usr/bin directory after being created.

Figure 18. Sample XAResourceManager entry for Oracle on UNIX platforms

XAResourceManager:
  Name=Oracle MQBankDB
  SwitchFile=/usr/bin/oraswit
  XAOpenString=Oracle_XA+Acc=P/scott/tiger+SesTm=35+LogDir=/tmp/log+DB=MQBankDB

Changing Oracle configuration parameters

The queue manager and user applications use the user ID specified in the XA open string when they connect to Oracle.

• Database privileges (Oracle7 only)
  V$XATRANS$ view.

  The necessary privilege can be given using the following command, where userID is the user ID for which access is being given.

  grant select on V$XATRANS$ to userID;
  

  See Security considerations for more information about security.

• Database privileges (Oracle8 only)
  access the DBA_PENDING_TRANSACTIONS view.

  The necessary privilege can be given using the following command, where userID is the user ID for which access is being given.

  grant select on DBA_PENDING_TRANSACTIONS to userID;
  

  See Security considerations for more information about security.

• Additional database connections (Oracle7 and later)
  to take into account the additional connections required by processes belonging to the queue manager. See Database connections for details about the database connections that the queue manager needs for itself.