Sunday, February 26, 2012

DB2 for z/OS: of Stored Procedures and the DB2 MQListener

As a mainframe DB2 guy, I'm bullish on stored procedures -- I've presented on the topic at international conferences and regional user group meetings, written about stored procedures for the old DB2 Magazine, and posted many a related entry to my DB2 blogs, both this blog (an example being an entry from August of last year) and the one I maintained while working as an independent DB2 consultant (included among these is part 1 of a 3-part entry on stored procedure usage from an application architecture perspective). I'm an advocate of DB2 stored procedure technology because I'm very big on the use of DB2 for z/OS as an enterprise data server for multi-tiered, client-server applications, and stored procedures have an important role to play there.

I'm also a proponent of asynchronous data processing, particularly as it pertains to DB2 data-changing operations (e.g., database inserts and updates). In this context, "asynchronous" means that the processing of a back-end data change is "unhooked," time-wise, from the client-side process that supplied the input to the data change operation (this input could come from an end-user clicking "submit" on a screen of a Web-based application). The means of accomplishing this "unhooking" of front-end input and back-end insert or update is often a message queue placed in-between the two processes. In many cases, that message queue will be managed by MQ. Asynchronous processing of DB2 data change operations can be attractive for several reasons, including better end-user response time (the data-input transaction is complete once the data is placed on the MQ queue) and improved application resiliency (if the back-end database is temporarily unavailable for some reason, input messages simply accumulate on the MQ queue, and are processed when the database is brought back online).

A handy piece of code called the DB2 MQListener enables the bringing together of these two favorite technologies of mine -- DB2 for z/OS stored procedures and asynchronous transaction processing -- and that's what this blog entry is about.

Here's the deal: if you want information in a message that an application process has placed on an MQ queue to be input to a DB2 INSERT or UPDATE (or, conceivably, a DELETE), something has to take that message off the queue and initiate the DB2 data change operation. That "something" can be the DB2 MQListener. It is provided with DB2 (starting, I believe, with DB2 for z/OS V8), and you can read about it in the DB2 for z/OS Application Programming and SQL Guide (this manual -- and other DB2 manuals -- are available in PDF and HTML form for DB2 Versions 8, 9, and 10 at

The DB2 MQListener runs as a daemon (a background process, in the parlance of Linux/UNIX) under the UNIX System Services component of z/OS. Conceptually, what it does is pretty simple: it calls a DB2 for z/OS stored procedure in response to a message arriving on an MQ queue. What stored procedure does it call? That's up to you. See, the DB2 MQListener is a multi-threaded process, and each thread is connected to one MQ queue. When a message arrives on a particular queue, the DB2 MQListener calls the stored procedure that is associated with the queue (this association between a queue and a stored procedure is established by way of MQListener commands, and the configuration information is stored in a DB2 table named SYSMQL.LISTENERS). If you want several different message types to be processed by different DB2 stored procedures (i.e., message type A drives an insert to DB2 table X, message type B is input to an update of table Y, etc.), set up several input queues, and associate with each the stored procedure that is to operate on a given message type. Then, have the input-providing application programs direct each message type to its designated queue.

What, exactly, is passed to a stored procedure by the DB2 MQListener when a message arrives on a queue? That's easy: the message itself -- all of it. Each arriving message generates one stored procedure call. A stored procedure will do with a message what you've programmed it to do. If you want to keep things really simple, you can have a stored procedure insert a message, in its entirety, into a column of a DB2 table. If the message contains several items of information and you want these various items to go into different columns of a table, you can write a stored procedure to do that. This would be pretty straightforward if the message parts were of a fixed and consistent length (e.g., positions 1 through 4 contain item A, positions 5 through 12 contain item B, etc.). Disassembling a message into its component parts would be a little more involved if the parts were of varying length, but this could certainly be done -- you just have to be able to find the end of one item in the message and the beginning of the next item (the parts of the message might be separated by commas, for example). Want to drive an update of information in a table, based on the content of a message? Write a stored procedure to do that. Have a maybe insert, maybe update situation? Let the message be input to a MERGE statement in a stored procedure. You get the picture: the DB2 MQListener is going to pass a message, in its entirety, to the stored procedure associated with an input queue. What the stored procedure does with that message is your call. [Note that, in addition to the one input parameter (a message taken from a queue), the DB2 MQListener will specify a single output parameter when calling a stored procedure. You can have your stored procedure assign a value to this output parameter, but you don't have to -- it can be NULL.]

Just as you decide what a stored procedure is to do with a message supplied as input by the DB2 MQListener, so, too, are you in control of the type of stored procedure that will be used with the MQListener. Want it to be an external stored procedure, perhaps one that's written in COBOL? That's fine. Want to go with a native SQL procedure (these were introduced with DB2 9 in new-function mode)? That's OK, too. The right choice is the one that make sense in your environment and provides the processing functionality that you need (though I will say that I'm a big proponent of native SQL procedures).

Herein I've provided just a brief overview of the DB2 MQListener and the ways in which you can use it. As I mentioned, you can get a lot more information from the DB2 for z/OS Application Programming and SQL Guide (including examples of MQListener set-up and operation and DB2 stored procedure coding). Check it out. DB2 and MQ are a great combination, and the DB2 MQListener can help you take asynchronous invocation of stored procedures from concept to reality.


  1. What would happen to the messages in #2 if this scenario were to occur?

    1. DB2 goes down.
    2. More messages arrive on the queue.
    3. DB2 comes back up.

    Are the messages in #2 above automatically read off the queue when DB2 becomes available in #3?


  2. Sorry about the delay in responding, Brian.

    If the DB2 subsystem is unavailable, what happens next will depend on whether or not the DB2 MQListener daemon is running in two-phase commit mode (specified in setting up a DB2 MQListener configuartion). If the DB2 MQListener daemon is running in two-phase commit mode then the failure of a stored procedure call (due, for example, to the DB2 subsystem being down because of unplanned or planned outage) will cause the message that triggered the stored procedure call to be placed back on the input queue (as part of a rollback operation). From there it should be automatically processed, eventually, when DB2 is back up.

    If the DB2 MQListener daemon is not running in two-phase commit mode, a stored procedure call failure should cause the message to be sent to the reply queue specified for the configuration (or to the configuration's dead-letter queue, if the send to the reply queue fails for some reason). I believe that a user action might be necessary to get the messages sent to the reply queue (or dead-letter queue) to be processed by the appropriate stored procedure when DB2 is back up (this would involve, I believe, getting the messages from the reply queue (or dead-letter queue) back onto the input queue associated with the stored procedure that should process the messages.

  3. I have a DB2 table and whenever that able is updated, it is placing messages onto the MQ. I am not sure what creates the message or triggers it. Is there a way to find out ?

    1. My guess would be that a trigger is defined on the table, and when data in the table is updated, the trigger is fired and the trigger puts a message on an MQ queue. A DBA could verify that by checking the SYSIBM.SYSTRIGGERS table in the catalog of the Db2 for z/OS system (I assume that you're talking about a Db2 for z/OS system, versus a Db2 for Linux/UNIX/Windows system). The TBNAME column in SYSTRIGGERS contains the name of the table (or view) on which a trigger is defined.


    2. This is exactly what I need to do, I just don't know how to

  4. Hello Robert, very good article.
    I have the opposite need: I have to put on a MQ Queue whatever UPDATE/INSERT happens on a certain table.
    How do I do that? I saw people talking about it conceptually(and the articles are from years ago, including this one), but still have no examples on how to implement it, I mean which commands to use and where.
    The theory part I have, I need the practical part, can you point me in the right direction?
    I tried to look for it under the trigger part on the IBM DB2 manual, but still doesn't get me where I need to be.
    So, I appreciate any help you can provide.

    1. First of all, you'd need two triggers - one fired by an update operation, and one fired by an insert operation. The Db2 for z/OS CREATE TRIGGER documentation for a basic is here: The documentation for CREATE TRIGGER for an advanced trigger (which can contain in its body a routine written in SQL procedure language) is here:

      A trigger, when fired, can place data on an MQ queue via the Db2 built-in function MQSEND. Documentation on MQSEND is here: Additional documentation about using MQSEND is here: