Database coordination

When the queue manager coordinates global units of work itself, you can integrate database updates within WebSphere MQ units of work. That is, you can write a mixed MQI and SQL application, and use the MQCMIT and MQBACK verbs to commit or roll back the changes to the queues and databases together.

The queue manager achieves this using a two-phase commit protocol. When a unit of work is to be committed, the queue manager first asks each participating database manager whether it is prepared to commit its updates. Only if all the participants, including the queue manager itself, are prepared to commit, are all the queue and database updates committed. If any participant cannot prepare its updates, the unit of work is backed out instead.

Full recovery support is provided if the queue manager loses contact with any of the database managers during the commit protocol. If a database manager becomes unavailable while it is in doubt, that is, it has been called to prepare but has yet to receive a commit or back out decision, the queue manager remembers the outcome of the unit of work until it has been successfully delivered. Similarly, if the queue manager ends with incomplete commit operations outstanding, these are remembered over queue manager restart.

Use the MQI verb, MQBEGIN, to denote units of work that are also to involve database updates. The WebSphere MQ Application Programming Guide identifies sample programs that make WebSphere MQ and database updates within the same unit of work.

The queue manager communicates with the database managers using the XA interface as described in X/Open Distributed Transaction Processing: The XA Specification (ISBN 1 872630 24 3). This means that the queue manager can communicate with database managers that also adhere to this standard. Such database managers are known as XA-compliant database managers.

Table 14 identifies XA-compliant database managers that WebSphere MQ supports.

Table 14. XA-compliant relational databases

Restrictions

The following restrictions apply to the database coordination support:

• The ability to coordinate database updates within WebSphere MQ units of work is not supported in an MQI client application.
• The MQI updates and database updates must be made on the same queue manager server machine.
• The database server can reside on a different machine from the queue manager server. In this case, the database needs to be accessed using an XA-compliant client feature provided by the database manager itself.
• Although the queue manager itself is XA-compliant, it is not possible to configure another queue manager as a participant in global units of work. This is because only one connection at a time can be supported.

Database connections

An application that establishes a standard connection to the queue manager is associated with a thread in a separate local queue manager agent process. When the application issues MQBEGIN, both it and the agent process need to connect to the databases involved in the unit of work. The database connections are maintained while the application remains connected to the queue manager. This is an important consideration if the database supports only a limited number of users or connections.

One method of reducing the number of connections is for the application to use the MQCONNX call to request a fastpath binding. In this case the application and the local queue manager agent become the same process and can share a single database connection. Before you do this, consult the WebSphere MQ Application Programming Guide for a list of restrictions that apply to fastpath applications.

Configuring database managers

There are several tasks that you must perform before a database manager can participate in global units of works coordinated by the queue manager:

1. Create an XA switch load file for the database manager. An XA switch load file is a dynamically-loaded object that enables the queue manager and the database manager to communicate with each other.
2. Define the database manager in the queue manager's configuration information. This information includes the name of the switch load file.

Creating switch load files

WebSphere MQ comes with a sample makefile, used to build switch load files for the supported database managers. This makefile, together with all the associated files required to build the switch load files, is installed in the following directories:

• For WebSphere MQ for Windows, in the C:\Program Files\IBM\WebSphere MQ\tools\c\samples\xatm\ directory
• For WebSphere MQ for UNIX systems, in the /opt/mqm/samp/xatm/ directory (/usr/mqm/samp/xatm on AIX)

Refer to your WebSphere MQ installation documentation for more information about the installation procedure.

The sample source modules used to produce the switch load files all contain a single function called MQStart. When the switch load file is loaded, the queue manager calls this function, which returns the address of a structure called an XA switch. The switch load file is linked to a library provided by the database manager that enables WebSphere MQ to call that database manager.

The sample source modules used to build the switch load files are:

• For DB2, db2swit.c
• For Oracle, oraswit.c
• For Sybase, sybswit.c

Defining database managers

When you have created a switch load file for your database manager, you must specify its location to your queue manager. This is done in the queue manager's configuration information. On Windows systems, use the Resources page of the properties for the queue manager (accessed from the WebSphere MQ Services snap-in). On UNIX systems, use the XAResources stanza of the qm.ini configuration file.

You need to add this information for each database manager that your queue manager is going to coordinate. More complicated configurations involving multiple databases, or different database managers, are discussed in Multiple database configurations.

The resource manager attributes are as follows:

Name=name

A string that identifies the database manager instance.

The name is mandatory and can be up to 31 characters in length. It must be unique. It could simply be the name of the database manager, although to maintain its uniqueness in more complicated configurations it could, for example, also include the name of the database being updated.

The name that you choose should be meaningful, because the queue manager uses it to refer to this database manager instance both in messages and in output when the dspmqtrn command is used.

Once you have chosen a name, do not change this attribute. Information about changing configuration information is given in Changing configuration information.

SwitchFile=name

The fully-qualified name of the database manager's XA switch load file. This is a mandatory attribute.

XAOpenString=string

A string of data that is passed in calls to the database manager's xa_open entry point. The format for this string depends on the particular database manager, but it should usually identify the name of the database that is to be updated.

This is an optional attribute; if it is omitted a blank string is assumed.

XACloseString=string

A string of data that is passed in calls to the database manager's xa_close entry point. The format for this string depends on the particular database manager.

This is an optional attribute; if it is omitted a blank string is assumed.

ThreadOfControl=THREAD|PROCESS

The queue manager uses this attribute for serialization.

If the database manager is thread-safe, specify THREAD as the value for ThreadOfControl. The queue manager can call the database manager from multiple threads at the same time.

If the database manager is not thread-safe, specify PROCESS as the value for ThreadOfControl. The queue manager serializes all calls to the database manager so that only one call at a time is made from within a process.

See "XA resource managers" for fuller descriptions of these attributes.

DB2 configuration, Oracle configuration, and Sybase configuration give more information about the specific tasks you need to perform to configure WebSphere MQ with each supported database manager.