Monday, March 14, 2011

db2 luw monitoring using WLM



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.



About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.