Creating a stored procedure to use with MQListener

MQListener uses the stored procedure, aProc, to store a message in a table. The stored procedure returns the string OK if the message is successfully inserted into the table.

Before you begin

The stored procedure requires a C compiler.

About this task

The run database contains the stored procedure that is run when a message arrives. The run user is the user in whose name MQListener connects to the run database to run the stored procedure. Use the following parameters with the db2mqlsn add command to define the run database and the run user:

-dbName
-dbUser

The run user must be able to connect to the run database and run the stored procedure. The run user does not need to be the owner of the stored procedure. The run user also does not need access to the MQListener configuration.

Procedure

Create a simple table as the run user (you can use the DB2® command line processor):
```
CREATE TABLE aTable (val VARCHAR(25) CHECK (val NOT LIKE 'fail%'))
```

Create the following stored procedure:


CREATE PROCEDURE aProc (IN pin VARCHAR(25), OUT pout VARCHAR(2))     
BEGIN
         INSERT INTO aTable VALUES(pin);
         SET pout = 'OK';
END

Results

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.