Preparing for DB2 Near-Realtime Business Intelligence

242 Preparing for DB2 Near-Realtime Business Intelligence

tail -190 urquery3.out >> querysum.out

tail -190 urquery4.out >> querysum.out

tail -190 urquery5.out >> querysum.out

Script stop

This script, in Example E-13, is started when the query execution has finished.

The event monitor is stopped and the monitor information gathered is exported

into files.

Example: E-13 Script stop

db2 connect to rtwh user udb81 using ibm123

db2 set event monitor mon_itso state 0

db2 "export to test4_run0_qur_800k_dlconn.del of del messages dlconn.msg select

* from evm.itso_dlconn"

db2 "export to test4_run0_qur_800k_dllock.del of del messages dllock.msg select

* from evm.itso_dllock"

db2 "export to test4_run0_qur_800k_stmt.del of del messages stmt.msg select *

from evm.itso_stmt"

db2 "import from /dev/nul of del messages dlconn_i.msg replace into

evm.itso_dlconn"

db2 "import from /dev/nul of del messages dllock_i.msg replace into

evm.itso_dllock"

db2 "import from /dev/nul of del messages stmt_i.msg replace into

evm.itso_stmt"

db2 connect reset

E.3 Scenario 1: DB2 MQ Listener

This section contains examples of the scripts and stored procedures used in

Scenario 1.

Configure DB2 MQ Listener

Before you can run DB2 MQ Listener, you must first configure it. Here you supply

parameters such as, database name, queue name, queue manager name, and

stored procedure name.

The command to actually add the MQL_CUS configuration is shown in

Example E-14.

Appendix E. Stored procedures and scripts 243

Example: E-14 Add Customer queue listener

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

configdb is the database DB2 MQ Listener installed, procname is the stored

procedure called by DB2 MQ Listener, and

dbname is the database where the

stored procedure resides

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

db2mqlsn add -configdb rtwh -config mql_cus -inputqueue queue_customer

-procschema udb81 -procname mql_cus -dbname rtwh -dbuser udb81 -dbpwd ibm123

The command to add the DB2 MQ Listener for the fact queue is shown in

Example E-15.

Example: E-15 Add fact queue Listener

db2mqlsn add -configdb rtwh -config mql_factitem -inputqueue queue_fact_item

-procschema udb81 -procname ql_factitem_sp -dbname rtwh -dbuser udb81 -dbpwd

ibm123

Figure E-1 depicts the configuration for a DB2 MQ Listener for the Customer

dimension queue, named MQL_CUS.

Figure E-1 DB2 MQ Listener - mql_cus

Figure E-2 shows the configuration of a DB2 MQ Listener named mql_factitem.

We use

db2mqlsn to show the definition of the DB2 MQ Listener.

244 Preparing for DB2 Near-Realtime Business Intelligence

Figure E-2 DB2 MQ Listener - mql_factitem

Before you run the DB2 MQ Listener, you must have a stored procedure defined

to process the messages on the MQ queue. Example E-16 on page 244 shows

the stored procedure that will parse the message from the customer queue,

called QUEUE_CUSTOMER

, and then updates the data warehouse.

Note that even though you may not need an output parameter in the DB2 MQ

Listener stored procedure, you must have one - and it must have the name

outMsg. If not, the DB2 MQ Listener will issue an error statement.

Example: E-16 Stored procedure for customer table

/**

* JDBC Stored Procedure UDB81.MQL_CUS

* @param inMsg

* @param outMsg

* We use Integer.parseInt(st.nextToken().trim()) to convert character back to

integer

*/

import java.sql.*;

import java.util.StringTokenizer;

import java.io.*;

import java.lang.Float;

public class MQL_CUS

{

public static void mQL_CUS (String inMsg,

String[] outMsg,

ResultSet[] rs1)

throws SQLException, Exception

{

// Get connection to the database

Appendix E. Stored procedures and scripts 245

Connection con =

DriverManager.getConnection("jdbc:default:connection");

PreparedStatement stmt = null;

boolean bFlag;

String sql;

String NAME;

String TYPE;

int CUSTKEY;

String MKTSEGMENT;

int updateCount;

StringTokenizer st = new StringTokenizer(inMsg, ";", false);

TYPE = st.nextToken().trim();

CUSTKEY = Integer.parseInt(st.nextToken().trim());

NAME = st.nextToken().trim();

MKTSEGMENT = st.nextToken().trim();

if (TYPE.equals("I"))

{

sql = "insert into db2inst3.customer values("

+ CUSTKEY

+ ","

+ "'"

+ NAME

+ "'"

+ ","

+ "'"

+ MKTSEGMENT

+ "')";

stmt = con.prepareStatement(sql);

updateCount = stmt.executeUpdate();

}

else if (TYPE.equals("U"))

{

sql = "update db2inst3.customer set NAME="

+ "'"

+ NAME

+ "' where CUSTKEY="

+ CUSTKEY;

stmt = con.prepareStatement(sql);

updateCount = stmt.executeUpdate();

}

else

{

sql = "insert into mqltime values (current timestamp)";

stmt = con.prepareStatement(sql);

updateCount = stmt.executeUpdate();

246 Preparing for DB2 Near-Realtime Business Intelligence

}

}

public static void main(String[] args) { }

}

Example E-17 shows the stored procedure to parse a message from the fact

queue, called QUEUE_FACT_ITEM, and insert it into the data warehouse fact

table.

Example: E-17 Stored Procedure for fact table update

/**

* JDBC Stored Procedure UDB81.QL_FACTITEM_SP

* @param inMsg

* @param outMsg

*/

import java.sql.*;

import java.util.StringTokenizer;

import java.io.*;

import java.lang.Float;

public class QL_FACTITEM_SP

{

public static void qL_FACTITEM_SP (String inMsg,

String[] outMsg,

ResultSet[] rs1)

throws SQLException, Exception

{

// Get connection to the database

Connection con =

DriverManager.getConnection("jdbc:default:connection");

PreparedStatement stmt = null;

boolean bFlag;

int QUANTITY;

double DISCOUNT;

double EXTENDEDPRICE;

double TAX;

int CUSTKEY;

int PARTKEY;

int SUPPKEY;

int ORDERKEY;

int TIMEKEY;

String sqlstat;

String sql;

String TYPE;

int updateCount;