MQ: Database coordination samples

Database coordination samples

Two samples are provided which demonstrate how WebSphere MQ can coordinate both WebSphere MQ updates and database updates within the same unit of work:

AMQSXAS0 (in C) or AMQ0XAS0 (in COBOL), which updates a single database within a WebSphere MQ unit of work.
AMQSXAG0 (in C) or AMQ0XAG0 (in COBOL), AMQSXAB0 (in C) or AMQ0XAB0 (in COBOL), and AMQSXAF0 (in C) or AMQ0XAF0 (in COBOL), which together update two databases within a WebSphere MQ unit of work, showing how multiple databases can be accessed. These samples are provided to show the use of the MQBEGIN call, mixed SQL and WebSphere MQ calls, and where and when to connect to a database.

The programs read a message from a queue (under syncpoint), then, using the information in the message, obtain the relevant information from the database and update it. The new status of the database is then printed.
The program logic is as follows:

Use name of input queue from program argument
Connect to default queue manager (or optionally supplied name in C) using MQCONN
Open queue (using MQOPEN) for input while no failures
Start a unit of work using MQBEGIN
Get next message (using MQGET) from queue under syncpoint
Get information from databases
Update information from databases
Commit changes using MQCMIT
Print updated information (no message available counts as failure, and loop ends)
Close queue using MQCLOSE
Disconnect from queue using MQDISC

SQL cursors are used in the samples, so that reads from the databases (that is, multiple instances) are locked whilst a message is being processed, thus multiple instances of these programs can be run simultaneously. The cursors are explicitly opened, but implicitly closed by the MQCMIT call.
The single database sample (AMQSXAS0 or AMQ0XAS0) has no SQL CONNECT statements and the connection to the database is implicitly made by WebSphere MQ with the MQBEGIN call. The multiple database sample (AMQSXAG0 or AMQ0XAG0, AMQSXAB0 or AMQ0XAB0, and AMQSXAF0 or AMQ0XAF0) has SQL CONNECT statements, as some database products allow only one active connection. If this is not the case for your database product, or if you are accessing a single database in multiple database products, the SQL CONNECT statements can be removed.
The samples are prepared with the IBM DB2 database product, so they may need some modification to work with other database products.
The SQL error checking uses routines in UTIL.C and CHECKERR.CBL supplied by DB2. These must be compiled or replaced before compiling and linking.

Note:

If you are using the Micro Focus COBOL source CHECKERR.MFC for SQL error checking, change the program ID to uppercase, that is CHECKERR, for AMQ0XAS0 to link correctly.

Creating the databases and tables

The databases and tables must be created before the samples can be compiled. To create the databases, use the normal method for your database product, for example:
  DB2 CREATE DB MQBankDB
  DB2 CREATE DB MQFeeDB
Create the tables using SQL statements as follows:
In C:
EXEC SQL CREATE TABLE MQBankT(Name         VARCHAR(40) NOT NULL,
                              Account      INTEGER     NOT NULL,
                              Balance      INTEGER     NOT NULL,
                              PRIMARY KEY (Account));
 
EXEC SQL CREATE TABLE MQBankTB(Name         VARCHAR(40) NOT NULL,
                               Account      INTEGER     NOT NULL,
                               Balance      INTEGER     NOT NULL,
                               Transactions INTEGER,
                               PRIMARY KEY (Account));
 
EXEC SQL CREATE TABLE MQFeeTB(Account      INTEGER     NOT NULL,
                              FeeDue       INTEGER     NOT NULL,
                              TranFee      INTEGER     NOT NULL,
                              Transactions INTEGER,
                              PRIMARY KEY (Account));
In COBOL:
 EXEC SQL CREATE TABLE
   MQBankT(Name      VARCHAR(40) NOT NULL,
           Account   INTEGER     NOT NULL,
           Balance   INTEGER     NOT NULL,
           PRIMARY KEY (Account))
   END-EXEC.
 
EXEC SQL CREATE TABLE
  MQBankTB(Name      VARCHAR(40) NOT NULL,
           Account   INTEGER     NOT NULL,
           Balance   INTEGER     NOT NULL,
           Transactions INTEGER,
           PRIMARY KEY (Account))
  END-EXEC.
 
EXEC SQL CREATE TABLE
  MQFeeTB(Account      INTEGER     NOT NULL,
          FeeDue       INTEGER     NOT NULL,
          TranFee      INTEGER     NOT NULL,
          Transactions INTEGER,
          PRIMARY KEY (Account))
  END-EXEC.
Fill in the tables using SQL statements as follows:
EXEC SQL INSERT INTO MQBankT VALUES ('Mr Fred Bloggs',1,0);
EXEC SQL INSERT INTO MQBankT VALUES ('Mrs S Smith',2,0);
EXEC SQL INSERT INTO MQBankT VALUES ('Ms Mary Brown',3,0);

·
·
·

EXEC SQL INSERT INTO MQBankTB VALUES ('Mr Fred Bloggs',1,0,0);
EXEC SQL INSERT INTO MQBankTB VALUES ('Mrs S Smith',2,0,0);
EXEC SQL INSERT INTO MQBankTB VALUES ('Ms Mary Brown',3,0,0);

·
·
·

EXEC SQL INSERT INTO MQFeeTB VALUES (1,0,50,0);
EXEC SQL INSERT INTO MQFeeTB VALUES (2,0,50,0);
EXEC SQL INSERT INTO MQFeeTB VALUES (3,0,50,0);

·
·
·
Note:

For COBOL, use the same SQL statements but add END_EXEC at the end of each line.

Precompiling, compiling, and linking the samples

The .SQC files (in C) and .SQB files (in COBOL) must be precompiled and bound against the appropriate database to produce the .C or .CBL files. To do this, use the normal method for your database product, as shown below.

Precompiling in C
db2 connect to MQBankDB
db2 prep AMQSXAS0.SQC
db2 connect reset
 
db2 connect to MQBankDB
db2 prep AMQSXAB0.SQC
db2 connect reset
 
db2 connect to MQFeeDB
db2 prep AMQSXAF0.SQC
db2 connect reset
Precompiling in COBOL
db2 connect to MQBankDB
db2 prep AMQ0XAS0.SQB bindfile target ibmcob
db2 bind AMQ0XAS0.BND
db2 connect reset
 
db2 connect to MQBankDB
db2 prep AMQ0XAB0.SQB bindfile target ibmcob
db2 bind AMQ0XAB0.BND
db2 connect reset
 
db2 connect to MQFeeDB
db2 prep AMQ0XAF0.SQB bindfile target ibmcob
db2 bind AMQ0XAF0.BND
db2 connect reset
Compiling and linking

The following sample commands use the symbol <DB2TOP>. <DB2TOP> represents the installation directory for the DB2 product.
On AIX the directory path is:
/usr/lpp/db2_05_00
On HP-UX and Solaris the directory path is:
/opt/IBMdb2/V5.0
On Windows systems and OS/2 the directory path depends on the path chosen when installing the product. If you chose the default settings the path is:
c:\sqllib
Note:

Before issuing the link command on Windows systems or OS/2 ensure that the LIB environment variable contains paths to the DB2 and WebSphere MQ libraries.

Copy the following files into a temporary directory:
The amqsxag0.c file from your WebSphere MQ installation
Note:
This file can be found in the following directories:
On UNIX:
<MQMTOP>/samp/xatm
On Windows systems and OS/2:
<MQMTOP>\tools\c\samples\xatm
The .c files that you have obtained by precompiling the .sqc source files, amqsxas0.sqc, amqsxaf0.sqc, and amqsxab0.sqc
The files util.c and util.h from your DB2 installation.
Note:
These files can be found in the directory:
<DB2TOP>/samples/c
Build the object files for each .c file using the following compiler command for the platform that you are using:
AIX
xlc_r -I<MQMTOP>/inc -I<DB2TOP>/include -c -o
 <FILENAME>.o <FILENAME>.c
HP-UX
cc -Aa +z -I<MQMTOP>/inc -I<DB2TOP>/include -c -o
 <FILENAME>.o <FILENAME>.c
OS/2
icc /c /I<MQMTOP>\tools\c\include /I<DB2TOP>\include <FILENAME>.c
Solaris
cc -Aa -KPIC -mt -I<MQMTOP>/inc -I<DB2TOP>/include -c -o
 <FILENAME>.o <FILENAME>.c
Windows systems
cl /c /I<MQMTOP>\tools\c\include /I<DB2TOP>\include
 <FILENAME>.c
Build the amqsxag0 executable using the following link command for the platform that you are using:
AIX
xlc_r -H512 -T512 -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib
 -lmqm util.o amqsxaf0.o amqsxab0.o amqsxag0.o -o amqsxag0
HP-UX Revision 10.20
ld -E -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib -lmqm -1c /lib/crt0.o
 util.o amqsxaf0.o amqsxab0.o amqsxag0.o -o amqsxag0
HP-UX Revision 11.00
ld -E -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib -lmqm -lc -lpthread -lcl
 /lib/crt0.o util.o amqsxaf0.o amqsxab0.o amqsxag0.o -o amqsxag0
OS/2
ilink util.obj amqsxaf0.obj amqsxab0.obj amqsxag0.obj mqm.lib
 db2api.lib /out:amqsxag0.exe
Solaris
cc -mt -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib
 -lmqm -lmqmzse-lmqmcs -lthread -lsocket -lc -lnsl -ldl util.o
  amqsxaf0.o amqsxab0.o amqsxag0.o -o amqsxag0
<Windows systems
link util.obj amqsxaf0.obj amqsxab0.obj amqsxag0.obj mqm.lib db2api.lib
 /out:amqsxag0.exe
Build the amqsxas0 executable using the following compile and link commands for the platform that you are using:
AIX
xlc_r -H512 -T512 -L<DB2TOP>/lib -ldb2
 -L<MQMTOP>/lib -lmqm util.o amqsxas0.o -o amqsxas0
HP-UX Revision 10.20
ld -E -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib -lmqm -lc
 /lib/crt0.o util.o amqsxas0.o -o amqsxas0
HP-UX Revision 11.00
ld -E -L<DB2TOP>/lib -ldb2 -L<MQMTOP>/lib -lmqm -lc -lpthread
 -lcl /lib/crt0.o util.o amqsxas0.o -o amqsxas0
OS/2
ilink util.obj amqsxas0.obj mqm.lib db2api.lib /out:amqsxas0.exe
Solaris
cc -mt -L<DB2TOP>/lib -ldb2-L<MQMTOP>/lib
 -lqm -lmqmzse -lmqmcs -lthread -lsocket -lc -lnsl -ldl util.o
  amqsxas0.o -o amqsxas0
Windows systems
link util.obj amqsxas0.obj mqm.lib db2api.lib /out:amqsxas0.exe
Additional information
If you are working on AIX or HP-UX and wish to access Oracle, use the xlc_r compiler and link to libmqm_r.a.

Running the samples

Before the samples can be run, the queue manager must be configured with the database product you are using. For information about how to do this, see WebSphere MQ System Administration Guide.

C samples

Messages must be in the following format to be read from a queue:
UPDATE Balance change=nnn WHERE Account=nnn
AMQSPUT can be used to put the messages on the queue.
The database coordination samples take two parameters:

Queue name (required)
Queue manager name (optional)

Assuming that you have created and configured a queue manager for the single database sample called singDBQM, with a queue called singDBQ, you increment Mr Fred Bloggs's account by 50 as follows:
  AMQSPUT singDBQ singDBQM
Then key in the following message:
  UPDATE Balance change=50 WHERE Account=1
You can put multiple messages on the queue.
  AMQSXAS0 singDBQ singDBQM
The updated status of Mr Fred Bloggs's account is then printed.
Assuming that you have created and configured a queue manager for the multiple-database sample called multDBQM, with a queue called multDBQ, you decrement Ms Mary Brown's account by 75 as follows:
  AMQSPUT multDBQ multDBQM
Then key in the following message:
  UPDATE Balance change=-75 WHERE Account=3
You can put multiple messages on the queue.
  AMQSXAG0 multDBQ multDBQM
The updated status of Ms Mary Brown's account is then printed.

COBOL samples

Messages must be in the following format to be read from a queue:
  UPDATE Balance change=snnnnnnnn WHERE Account=nnnnnnnn
For simplicity, the Balance change must be a signed eight-character number and the Account must be an eight-character number.
The sample AMQSPUT can be used to put the messages on the queue.
The samples take no parameters and use the default queue manager. It can be configured to run only one of the samples at any time. Assuming that you have configured the default queue manager for the single database sample, with a queue called singDBQ, you increment Mr Fred Bloggs's account by 50 as follows:
  AMQSPUT singDBQ
Then key in the following message:
  UPDATE Balance change=+00000050 WHERE Account=00000001
You can put multiple messages on the queue.
  AMQ0XAS0
Type in the name of the queue:
  singDBQ
The updated status of Mr Fred Bloggs's account is then printed.
Assuming that you have configured the default queue manager for the multiple database sample, with a queue called multDBQ, you decrement Ms Mary Brown's account by 75 as follows:
  AMQSPUT multDBQ
Then key in the following message:
  UPDATE Balance change=-00000075 WHERE Account=00000003
You can put multiple messages on the queue.
  AMQ0XAG0
Type in the name of the queue:
  multDBQ
The updated status of Ms Mary Brown's account is then printed.

WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.

IBM is a trademark of the IBM Corporation in the United States, other countries, or both.

AIX is a trademark of the IBM Corporation in the United States, other countries, or both.