Moving Forward with the On Demand Real-time Enterprise

Chapter 5. More real-time enterprise enablers 239

Figure 5-4 WebSphere MQ for business integration

5.2.2 DB2 MQ Listener

DB2 provides a capability to monitor a WebSphere MQ queue for messages.

When a message arrives, a DB2 Stored Procedure can be invoked to process

the message. This is called the DB2 MQ Listener.

You can configure the MQ Listener to listen to the WebSphere MQ message

queues that you specify in a configuration database. MQ Listener then reads the

messages as the arrive on the queue and calls a DB2 Stored Procedure with the

message as an input parameter to be processed. The message are retrieved by

priority order. If the message requires a reply, MQListener creates a reply from

the output that is generated by the Stored Procedure.

AIX

WebSphere MQ

Customer

Relationship

Management

Custom

Application

Enterprise

Resource

Planning

Supply

Chain

Management

IBM CICS

IBM IMS

WebSphere

Application

Server

Mobile

Communications

Customers

35+

Platforms

Suppliers

Application

Integration

Internet

240 Moving Forward with the On Demand Real-time Enterprise

Figure 5-5 DB2 MQ Listener

MQ Listener runs as a single multi-threaded process. Each thread or task

establishes a connection to its configured message queue for input. Each task

also connects to a DB2 database on which to run the stored procedure. Each

task will repeatedly execute the following until it gets a shutdown request.

򐂰 Get a message from MQ queue

򐂰 Call a user-supplied DB2 Stored Procedure, passing the message

򐂰 Sends reply (optional)

򐂰 Commit/Rollback the transaction which may be configured to include MQ and

DB2 operations in the commit scope

In Figure 5-6 is an example of how the DB2 MQListener can be used to take

messages off of a WebSphere MQ Queue and update the data warehouse. In

this scenario, there may be multiple stored procedures updating one table. With

multiple updaters on one table, there might have some undesirable locking

situations. You also have to be careful in the number of stored procedures that

the DB2 MQListener can start so as not to overwhelm the concurrently executing

queries.

Chapter 5. More real-time enterprise enablers 241

Figure 5-6 Using MQ Listener to transform and apply

If you implement the Transform function and the Apply function, as independent

operations, you could use the DB2 MQListener to invoke a stored procedure that

processes each message and uses the DB2 MQ functions, to put the processed

record onto a WebSphere MQ Queue which is then fed to a continuously running

stored procedure that applies those records to the data warehouse table, as

depicted in Figure 5-7.

Figure 5-7 Separating the transform and apply function

This scenario provides several advantages. The time that it takes to perform the

Transform function on a record is probably much longer that the time to actually

apply the data to the data warehouse table. By separating the two functions,

MQListener can invoke more parallel invocations of the stored procedure for the

Transform function without affecting the concurrency of the Apply function. The

WebSphere MQ Queue between the Transform and Apply functions provides a

buffering mechanism to help smooth out the speed of update to the data

warehouse.

DB2

SP

DB2

SP

DB2

SP

DB2 MQ

Listener

MQ Queues

DB2 Data

Warehouse

DB2

SP

DB2

SP

DB2

SP

DB2 MQ

Listener

Queue

DB2 Data

Warehouse

DB2

SP

Queue

Transform

Apply