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:
- Create a simple table as the run user (you can use the DB2® command
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
The table contains a check constraint so that messages that start with the characters
failcannot be inserted into the table. The check constraint is used to demonstrate the behavior of MQListener when the stored procedure fails.