Creating a sample stored procedure to use with MQListener

You can create a sample stored procedure, APROC, that can be used by MQListener to store a message in a table. The stored procedure returns the string OK if the message is successfully inserted into the table.

About this task

This example assumes the following information about the environment:

MQListener is installed and configured for subsystem DB2A.
The IBM® MQ subsystem that is defined is named CSQ1.
The queue manager is running, and the following local queues are defined in the DB2A subsystem:
- ADMIN_Q : The administration queue
- BACKOUT_Q : The backout queue
- DB2MQ_DEFAULT_Q : The input queue, which has a backout queue with a threshold of 3
- REPLY_Q : The output queue or reply queue
- DEADLETTER_Q : The dead letter queue
The user who is running the MQListener daemon has the EXECUTE privilege on the DB2MQLSN plan.
MQlistener passes the message header (MQMD structure) to the stored procedure interface for MQListener.

Procedure

The following steps create Db2 objects that you can use with MQListener applications:

Create a table using SPUFI, DSNTEP2, or the Db2 command line processor in the subsystem where you want to run MQListener:
```
CREATE TABLE PROCTABLE (MSG VARCHAR(25) CHECK (MSG NOT LIKE 'FAIL%'));
```
The table contains a check constraint so that messages that start with the characters FAIL cannot be inserted into the table. The check constraint is used to demonstrate the behavior of MQListener when the stored procedure fails.

Create the following SQL procedure, and define it to the same Db2 subsystem.

CREATE PROCEDURE TEST.APROC (
   IN PIN VARCHAR(25), 
   OUT POUT VARCHAR(2),
   INOUT PMSGHEADER VARBINARY(500))
  VERSION V1
  LANGUAGE SQL
  PROCEDURE1: BEGIN
   DECLARE REPLYQ  VARBINARY(48);                         
   DECLARE REPLYQM VARBINARY(48);                         
   SET REPLYQ = VARBINARY(CONCAT('NEWREPLYQUEUE',X'00'));        
   SET REPLYQM = VARBINARY(CONCAT('CSQ1',X'00'));  
   SET PMSGHEADER = INSERT(PMSGHEADER,101,LENGTH(REPLYQ),REPLYQ); 
   SET PMSGHEADER = INSERT(PMSGHEADER,149,LENGTH(REPLYQM),REPLYQM); 
   INSERT INTO SYSADM.PROCTABLE VALUES (PIN);                       
   SET POUT = 'OK';    
  END PROCEDURE1

Add the following configuration, named ACFG, to the configuration table by issuing this command:

db2mqln2 add 
   -ssID DB2A  
   -config ACFG
   -queueManager CSQ1
   -inputQueue DB2MQ_DEFAULT_Q 
   -procName APROC
   -procSchema TEST

Run the MQListener daemon for two-phase commit for configuration ACFG. To run MQListener with all of the tasks that are specified in the configuration, issue the following command:
```
db2mqln2 run 
   -ssID DB2A
   -config ACFG
   -adminQueue ADMIN_Q
   -adminQMgr MQND
```
Send a request to the input queue, 'DB2MQ_DEFAULT_Q ', with the message 'another sample message'.
Query table PROCTABLE to verify that the sample message was inserted:
```
SELECT * FROM PROCTABLE;
```
Display the number of messages that remain on the input queue, to verify that the message has been removed. To do that issue the following command from a z/OS console:
```
/-CSQ1 display queue('DB2MQ_DEFAULT_Q ') curdepth
```
Look at the ReplytoQ name that you specified, to verify that the string 'OK' is generated by the stored procedure.