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!!!!!
