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;

Get Preparing for DB2 Near-Realtime Business Intelligence now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.