Quick steps to setup WLM (workload manager) and its related tables. This is an extension to event monitoring. In this sample I am using default system WLM service class
-- create workload (uses default system service classes)
CREATE WORKLOAD "WL_POWERDESIGNER" SYSTEM_USER ('APPUSER_ID')
COLLECT ACTIVITY DATA
ON COORDINATOR DATABASE PARTITION
WITH DETAILS AND VALUES;
GRANT USAGE ON WORKLOAD WL_POWERDESIGNER TO PUBLIC;
SET CURRENT SCHEMA = "WLM";
-- following create statements will create all the required tables for capturing the trace output.
CREATE EVENT MONITOR "DB2STATISTICS"
FOR STATISTICS
WRITE TO TABLE
CONTROL (TABLE "CONTROL_DB2STATISTICS", IN USERSPACE1 ),
HISTOGRAMBIN (TABLE "HISTOGRAMBIN_DB2STATISTICS", IN USERSPACE1 ),
QSTATS (TABLE "QSTATS_DB2STATISTICS", IN USERSPACE1 ),
SCSTATS (TABLE "SCSTATS_DB2STATISTICS", IN USERSPACE1 ),
WCSTATS (TABLE "WCSTATS_DB2STATISTICS", IN USERSPACE1 ),
WLSTATS (TABLE "WLSTATS_DB2STATISTICS", IN USERSPACE1 )
AUTOSTART;
CREATE EVENT MONITOR "DB2ACTIVITIES" FOR ACTIVITIES
WRITE TO TABLE
ACTIVITY (TABLE "ACTIVITY_DB2ACTIVITIES", IN USERSPACE1 ),
ACTIVITYSTMT (TABLE "ACTIVITYSTMT_DB2ACTIVITIES", IN USERSPACE1 ),
ACTIVITYVALS (TABLE "ACTIVITYVALS_DB2ACTIVITIES", IN USERSPACE1 ),
CONTROL (TABLE "CONTROL_DB2ACTIVITIES", IN USERSPACE1 )
AUTOSTART;
CREATE EVENT MONITOR DB2THRESHOLDVIOLATIONS
FOR THRESHOLD VIOLATIONS
WRITE TO TABLE
THRESHOLDVIOLATIONS (TABLE THRESHOLDVIOLATIONS_DB2THRESHOLDVIOLATIONS IN USERSPACE1 ),
CONTROL (TABLE CONTROL_DB2THRESHOLDVIOLATIONS IN USERSPACE1 )
AUTOSTART;
-- activate the EM's
-- SET STATE = 1 and STATE = 0 to deactivate
SET EVENT MONITOR DB2ACTIVITIES STATE 1;
SET EVENT MONITOR DB2STATISTICS STATE 1;
SET EVENT MONITOR DB2THRESHOLDVIOLATIONS STATE 1;
Now run some queries on the given database and data will be captured in below tables.
-- following are the table to look at, I used activity_id to link the two.
SELECT * FROM WLM.ACTIVITYSTMT_DB2ACTIVITIES
SELECT * FROM WLM.ACTIVITYVALS_DB2ACTIVITIES
Note the “CREATE WORKLOAD” statement has “WITH DETAILS AND VALUES” clause, which means we capture the statements and also the host variables passed to the statements if any. So, the “ACTIVITYVALS_DB2ACTIVITIES” table will have multiple rows for every row in “ACTIVITYSTMT_DB2ACTIVITIES” table depending on how many variables were passed in the statements.
