Sending, reading and receiving a WebSphere MQ message using SQL.

A little understood and exploited feature of WebSphere MQ and DB2, is the ability to send, read and receive a message using a simple SQL statement.  The validity of using this feature we will leave for the systems architects and application developers. The purpose of this post is to explain how it works and how to configure the set-up.

During the installation of DB2, job DSNTIJSG creates several user defined functions.  Some of these functions are the AMI interface from DB2 to WebSphere MQ.

To see which functions were created, you can issue the following SQL statement in SPUFI or QMF:

SELECT DISTINCT(NAME) FROM SYSIBM.SYSROUTINES WHERE SCHEMA = 'DB2MQ';

You should see the following:

MQREAD
MQREADALL
MQREADALLCLOB
MQREADCLOB
MQRECEIVE
MQRECEIVEALL
MQRECEIVEALLCLOB
MQRECEIVECLOB
MQSEND

In this post we will specifically be dealing with the MQSEND, MQREAD and MQRECEIVE user defined functions.

There are two tables that control the MQ Services within DB2:

SYSIBM.MQSERVICE_TABLE
SYSIBM.MQPOLICY_TABLE

The MQSERVICE_TABLE contains the service name, queue manager name, queue name, CCSID, encoding and a description of the service.

The MQPOLICY_TABLE contains items that are specific to the message itself and its delivery.  You can think of the policy table as a mapping of a lot of the fields in the MQ Message Descriptor (MQMD). Items like, priority, persistence, expiry, message ID, correlation ID, COA, COD, reply to queue, reply to queue manager...and so on.

Let's define a service we can test with. Using SPUFI or QMF, execute the following:

INSERT INTO SYSIBM.MQSERVICE_TABLE VALUES('TEST_DB2_SEND','qmgrName','queueName',500,785,
 'A TEST SERVICE');

Looking at the values, we have:

TEST_DB2_SEND - The service name
qmgrName - The name of the Queue Manager that you DB2 can access
queueName - The name of a local, remote or alias queue define on that queue manager
500 - Code character set ID (z/OS)
785 - Encoding (z/OS)
A TEST SERVICE - Description of the service

Now we will need a policy to govern the delivery of this message. For our example, we will keep it simple and specify that the message is to be persistent.  So using SPUFI or QMF, execute

INSERT INTO SYSIBM.MQPOLICY_TABLE (POLICYNAME, SEND_PERSISTENCE, DESC)
VALUES('TEST_PERSIST_POLICY','Y','TEST POLICY FOR PERSISTENT MESSAGES');


In this statement, we specify the COLUMN names because there are a lot of columns on the table, and we only want these updated.  The others will assume a default value.  For the default values you can checkout the DB2 Information Center:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_db2mqtables.htm

So now we have a "service" as well as a "policy" defined, but before we can test the MQSEND function, we will need a Workload Manager (WLM) Application Environment established.  Your z/OS systems programmer can set this up for you.  Here are the values:

                     Application Environment
Name             DB21MQ
Description      TEST
Procedure Name   DB21MQ     Subsystem        DB2
Server Limit     No Limit
Start Parameters DB2SSN=&IWMSSNM,NUMTCB=20,APPLENV=DB21MQ

In the above example, we have a DB2 subsystem named DB21.  Now we need a procedure in a system PROCLIB called DB21MQ..(see procedure name above).

DB21MQ   PROC RGN=0M,APPLENV=DB21MQ,DB2SSN=DB21,NUMTCB=20
IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT,
        PARM='&DB2SSN,&NUMTCB,&APPLENV'
STEPLIB  DD  DISP=SHR,DSN=db2hlq.SDSNEXIT
         DD  DISP=SHR,DSN=db2hlq.SDSNLOAD
         DD  DISP=SHR,DSN=mqhlq.SCSQLOAD
         DD  DISP=SHR,DSN=mqhlq.SCSQAUTH
         DD  DISP=SHR,DSN=mqhlq.SCSQANLE
DSSPRINT DD  SYSOUT=*
SYSTSPRT DD  SYSOUT=*
CEEDUMP  DD  SYSOUT=*
SYSPRINT DD  SYSOUT=*
SYSABEND DD  DUMMY

The db2hlq and mqhlq need to be replaced with your high level qualifier for your DB2 and MQ libraries. Okay..we should now be set to give this a test.

To test this we will using SPUFI and issuing a SQL statement to demonstrate the execution.

SELECT DB2MQ.MQSEND('TEST_DB2_SEND', 'TEST_PERSIST_POLICY',

'TEST MESSAGE WITH NEW POLICY')

FROM SYSIBM.SYSDUMMY1;

This SQL statement should have put the message, "TEST MESSAGE WITH NEW POLICY" on the queue you specified with the INSERT statement of the SYSIBM.MQSERVICE table.

Now let's define a service to read the queue. Using the same structure as before:

INSERT INTO SYSIBM.MQSERVICE_TABLE VALUES('TEST_DB2_READ','qmgrName','queueName',500,785,'A TEST READ SERVICE');

And now a policy.  For this policy, we will just take the default values.  You can tailor your policy to control your specific needs, but for this example..the defaults will work:

INSERT INTO  SYSIBM.MQPOLICY_TABLE (POLICYNAME, DESC)
VALUES('TEST_READ_POLICY', 'POLICY FOR MQREAD');

We can now read the message from the queue using the following SQL statement:

SELECT DB2MQ.MQREAD('TEST_DB2_READ', 'TEST_READ_POLICY')
FROM SYSIBM.SYSDUMMY1;

Your output should display the contents of the message we placed on the queue.  Using the MQREAD function, the message is still on the queue.  To remove the message we will use the MQRECEIVE function.
So using the same policy as we did in the MQREAD...we can issue the statement:

SELECT DB2MQ.MQRECEIVE('TEST_DB2_READ', 'TEST_READ_POLICY')
FROM SYSIBM.SYSDUMMY1;

Your output should show the contents of the message, and the message is now removed from the queue.

You will probably want to name your services and policies something that makes sense based on the queue names and the specific behavior you are looking for.  But this gives you an idea of what you are capable of doing. One practical use for these features would be to send and receive messages from a JDBC client without having the z/OS WebSphere MQ Client Attach Facility installed.

Keep messaging!!!!!