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:

  1. AMQSXAS0 (in C) or AMQ0XAS0 (in COBOL), which updates a single database within a WebSphere MQ unit of work.
  2. 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.

Figure 37 shows how the samples provided are used to update databases:

Figure 37. The database coordination samples

The figure is a diagram of the relationship between programs and databases as described in the preceding text.

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:

  1. Use name of input queue from program argument
  2. Connect to default queue manager (or optionally supplied name in C) using MQCONN
  3. Open queue (using MQOPEN) for input while no failures
  4. Start a unit of work using MQBEGIN
  5. Get next message (using MQGET) from queue under syncpoint
  6. Get information from databases
  7. Update information from databases
  8. Commit changes using MQCMIT
  9. Print updated information (no message available counts as failure, and loop ends)
  10. Close queue using MQCLOSE
  11. 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, you must 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.

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:

Build the object files for each .c file using the following compiler command for the platform that you are using:

Build the amqsxag0 executable using the following link command for the platform that you are using:

Build the amqsxas0 executable using the following compile and link commands for the platform that you are using:

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:

  1. Queue name (required)
  2. 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.


© IBM Corporation 1993, 2002. All Rights Reserved