Preparing for DB2 Near-Realtime Business Intelligence

Appendix D. WebSphere MQ and DB2 MQ Listener 227

saved it, and transferred it back to the AIX machine. Figure D-9 on page 227

depicts the Administration Tool and shows the display of the service point names.

Figure D-9 AMI Administration Tool

With the service points defined, we can now put messages into the

QUEUE_FACT_ITEM queue instead of DB2MQ_DEFAULT_MQM. That can be

cone, for example, by using the following command:

db2 “values db2mq.mqsend(‘FACT_ITEM’, (‘test’))”

Once messages are loaded into the MQ queue, we can begin to process them.

To do that, in this series of test runs, we use the DB2 MQ Listener application.

The installation process is described in the next section.

D.6 DB2 MQ Listener installation

DB2 MQ Listener will monitor WebSphere MQ queues, looking for messages.

When it finds a message, it retrieves the message from the queue and invokes

the specified DB2 stored procedure based on the configuration. It can also

perform associated administrative and configuration tasks, as shown in

228 Preparing for DB2 Near-Realtime Business Intelligence

Example 9-2 on page 229. The MQ Listener configuration information is stored in

a DB2 database, and consists of a set of named configurations, including a

default. Each configuration, in turn, consists of a set of tasks. MQ Listener tasks

are defined by the message queue from which it retrieves messages and the

stored procedure that will be called. The message queue description must

include the name of the message queue and the queue manager, if it is not the

default. Information about the stored procedure must include the database in

which it is defined, a user name and password with which to access the

database, the procedure name, and schema.

To install DB2 MQ Listener, a script is provided. The script is named

MQLInstall.sql, is supplied by DB2, and resides in the $INSTHOME/sqllib/bin

directory. The command to install DB2 MQ Listener is:

db2 -tvf MQLInstall.sql

Example 9-1 on page 228 shows the script generated to install the DB2 MQ

Listener.

Example 9-1 MQLInstall.sql script

--------------------------------------------------------------------------

-- (C) COPYRIGHT International Business Machines Corp. 1997

-- All Rights Reserved

--

-- US Government Users Restricted Rights - Use, duplication or

-- disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

--

-- NAME: MQLInstall.sql

--

-- FUNCTION: SQL script used to install DB2 MQListener.

-- e.g. db2 -tvf MQLInstall.sql

--------------------------------------------------------------------------

DROP TABLE SYSMQL.Listeners;

CREATE TABLE SYSMQL.Listeners (

configurationName VARCHAR(18) NOT NULL DEFAULT '',

queueManager VARCHAR(48) NOT NULL DEFAULT '',

inputQueue VARCHAR(48) NOT NULL,

dbName VARCHAR(8) NOT NULL,

dbUserID VARCHAR(30) NOT NULL DEFAULT '',

dbPassword VARCHAR(40) FOR BIT DATA NOT NULL DEFAULT '',

procNode VARCHAR(30) NOT NULL DEFAULT '',

procSchema VARCHAR(30) NOT NULL,

procName VARCHAR(128) NOT NULL,

procType INTEGER NOT NULL DEFAULT 1,

mqCoordinated CHAR NOT NULL CHECK(mqCoordinated = 'T' OR mqCoordinated =

'F') DEFAULT 'F',