Configuring XA with Websphere MQ v5.3 for AIX 5.2(IBM RISC System/6000) and Sybase 12.5.2

Anyone having a hard time configuring XA to work with Sybase 12.5.2 + Embedded SQL/C and Websphere MQ 5.3 can go through the below document and see if it helps. I really had a hard time figuring this out with no proper documentation or help page given in the IBM or Sybase website and finally had it working.

The real problem was in the sample switch provided by IBM in /usr/mqm/samp/xatm folder. You have to make a few changes to make it work.

1) First, In the sybswit.c change the sybase_TUX_xa_switch to sybase_TXA_xa_switch. This is the structure we need to use for multithreaded applications.(Ref: http://www2.roguewave.com/support/docs/sourcepro/edition8/html/dbxaug/5-3.html)

2) Next, In the make file change the SYBLIBS line to SYBLIBS=-lintl_r.so -lct_r.so -lcs_r.so -lcomn_r.so -ltcl_r.so -lxadtm. The libraries linked should be shareable versions of CS-Library which ends with .so.(Ref: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/xainterf/@Generic__BookTextView/2131;pt=991/*)  Also make sure you use the proper version of the library. The below command in AIX can be used to check it.

strings intl_r.so | grep Sybase

3) If you are using C/Embedded SQL make sure the -V flag for cpre(ESQL precompiler) is set to CS_VERSION_125. MQ uses this version of the global context to store the connection. IMHO it depends on the version of the xadtm library you use it to link with the switch file or you can use the -V of XAOpenString  in the qm.ini file to emulate  older versions. The only option for -V is -V11 which emulates CS_VERSION_110.

If you mess up with any of the above three steps you will end up with the “connection does not exist” error in the “EXEC SQL SET CONNECTION connection01″ connection statement of the C/Embedded SQL and with no clues in the XA logs, the system logs or any logs for that matter. Below is the error,

** SQLCODE=(-33620271)
** SQL Server Error
** cs_objects: cslib user api layer: external error: 08003 Connection exception — Connection does not exist.

Note: Although the reference I had provided is for different products(One for CICS and the other for DB XA module), IMO the statements hold for MQ too(Atleast It works for me!!) although no documentation is provided.

Below I am giving the detailed step by step procedure for configuring the XA connection with MQ and sybase + C/Embedded SQL, assuming that you already have the programming experience in Embedded SQL and MQ but don’t know how to setup and use XA connections in code. We had the below system configuration. Make sure it matches yours.

$ uname
AIX
$ oslevel
5.2.0.0
$ getconf -a | grep KERN
KERNEL_BITMODE:                         32
$ dspmqver
Name:        WebSphere MQ
Version:     530.11  CSD11
CMVC level:  p530-11-L050802
BuildType:   IKAP - (Production)
$ isql -U****** -P****** -S****** -w1000
1> select @@version
2> go

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/12.5.2/EBF 12056 ESD#2/P/RS6000/AIX 5.1/ase1252/1844/64-bit/FBO/Thu Aug 12 02:44:26 2004

Below are the steps:

1) The database settings should have the below properties for XA DTM to work.

1> select license_enabled('ASE_DTM')
2> go

-----------
1

(1 row affected)

1> sp_displaylogin
2> go
Suid: 3
Loginame: *******
Fullname:
Default Database: ******
Default Language:
Auto Login Script:
Configured Authorization:
sybase_ts_role (default ON)
dtm_tm_role (default ON)
Locked: NO
Date of Last Password Change: Jan 24 2007  7:12PM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)

1> sp_configure "enable DTM"
2> go
Parameter Name                 Default     Memory Used Config Value Run Value   Unit                 Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
enable DTM                               0           0           1            1 switch               static

(1 row affected)
(return status = 0)
1> sp_configure "enable xact coordination"
2> go
Parameter Name                 Default     Memory Used Config Value Run Value   Unit                 Type
------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
enable xact coordination                 1           0           1            1 switch               static

(1 row affected)
(return status = 0)

2) Build the sybase switch for MQ using the steps I had discussed above.

3) Stop the queue manager using endmqm and add the Resource Manager stanza to the qm.ini of the queue manager (found in /var/mqm/qmgrs/) as below

XAResourceManager:
Name=mysybase
SwitchFile=/var/mqm/qmgrs/TEST!QM2/sybswit
XAOpenString=-U****** -P****** -Nconnection01 -L/tmp/sybaseQM2.log -Txa
ThreadOfControl=THREAD

4) The “connection01″ specified in the XAOpenString above is the LRM(Logical Resource Manager) name which should be added in the sybase XA config file found in (/usr/sybase/xa_config) as shown.

[xa]
lrm=connection01
server=*****

The path of the xa_config file is determined by the XACONFIGFILE environment variable.

$ echo $XACONFIGFILE
/usr/sybase/xa_config

5) Use the MQBEGIN API in the code to establish the connection with the database. It will be done internally. While running the code monitor the xa logs for errors.

6) Instead of “EXEC SQL CONNECT :username IDENTIFIED BY :password;” in the code use “EXEC SQL SET CONNECTION connection01;” to use the connection already established by the MQBEGIN API. The connection01 is the LRM name we specified in the XAOpenString in the qm.ini and the sybase xa_config file.

7) You need not disconnect the connection using “EXEC SQL DISCONNECT”. The connection will be closed automatically by the MQCMIT and MQBACK API.

8 ) Build your C/Embedded SQL application using the same libraries used for making the switch file.

9) Start the queue manager and make sure no errors are logged in the xa trace file which we specified in the XAOpenString of the qm.ini

I think the above steps help to get started.

~ by nvenkatesh on October 4, 2008.