Using SQL on z/OS you can use functions that map to MQPUT and MQGET to process MQ messages.
Because the MQGET WAIT time was too small, this meant that there were MQCONN ... MQOPEN. MQGET.. MQDISC calls which added to the CPU time
Ok that was was the answer for those in a hurry. In the rest of this blog describe in more detail about using MQ from DB2.
Simple SQL statement
SELECT LENGTH(MSG),MSG FROM PROCTABLE
- MSG is a column in table
- Length() is a function
This can return all of the row (or be limited to perhaps 100 rows)
SQL statement using MQ
SELECT DB2MQ.MQSEND('TRY_SEND2', 'CPPOLICY2', 'TEST DATA') FROM SYSIBM.SYSDUMMY1;
MQSEND is a special function
DB2 has configuration tables for MQ. I set these up using
- INSERT INTO SYSIBM.MQSERVICE_TABLE VALUES('TRY_SEND','MQPA','CP0000',500,785,'COLIN');
- INSERT INTO SYSIBM.MQPOLICY_TABLE (POLICYNAME, SEND_PERSISTENCE, DESC) VALUES('CPPOLICY2','Y','TEST POLICY FOR PERSISTENT MESSAGES');
- COMMIT WORK;
See here for a description of the layout of the tables.
The MQSERVICE table has
- Service name TRY_SEND used in the MQSEND function
- Queue manager : 'MQPA'
- Queue name 'CP0000'
- CCSID 500
- Encoding 785
- Comment COLIN
The MQPolicy table has columns including Priority, Persistence,Expiry, retry count, retry interval, correlid, reply to q, reply to queue manager,syncpoint,RCV_WAIT_INTERVAL
The RCV_WAIT_INTERVAL corresponds to the WaitInterval field in the get message options structure (MQGMO). The default is 10 milliseconds.
SYSIBM.SYSDUMMY1 is a pseudo 'one row table' – so the SELECT returns one row.
MQReceive returns a “table” with columns
SELECT T.MSG, T.QNAME
FROM TABLE (
'TRY_SEND', /* queue etc */
'CPPOLICY2', /* policy eg syncpoint */
’1234’) /* this correlid */
) T; /* MQRECEIVE all returns as a table called T */
MQRECEIVE generates table T.
This get messages with correlid '1234'
The code for these MQ functions run in a stored procedure. When the MQGET with wait returned no message, the program running in the stored procedure ended. When the next MQRECEIVE function was issued, DB2 started the DB2 supplied program in the Stored Procedure address space. It had to do MQCONN, MQOPEN... etc
By having a larger value of RCV_WAIT_INTERVAL, this should keep the address space active for longer - and eliminate the MQCONN.. MQOPEN. MQCLOSE MQDISC requests.