It's easy to use event monitor tables and the default DB2 Workload Manager objects to capture and
store information for historical analysis. This example is from a DB2 v9.7 fp 8 on AIX system.
Facts:
· By default, all work within the database is routed through the default WLM objects.
· Adding event monitors for Activities and Statistics, in combination with WLM, allow one to store historical information for a determined period of time to analyze query run-times, query cost, queries by application, and queries by id.
· We have been able to identify long running queries and make many improvements to costly reports.
· We have also been able to identify developer user id’s that execute long queries.
An Activity Event monitor won't capture a ton of data but it may add 3-4% overhead. I recommend you create a pruning script to delete data from the event monitor tables older than 7 days.
.
Scripts:
· Create a new tablespace and event monitor tables:
CREATE TABLESPACE "TS_WLM" IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP
MANAGED BY AUTOMATIC
STORAGE
AUTORESIZE YES
MAXSIZE NONE
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY
ON;
·
For DB2 v9.7 fp 4 and later
CREATE EVENT MONITOR "DB2ACTIVITIES"
FOR ACTIVITIES
WRITE TO TABLE CONTROL (TABLE "CONTROL_DB2ACTIVITIES", IN
TS_WLM ),
ACTIVITYMETRICS (TABLE "ACTIVITYMETRICS_DB2ACTIVITIES", IN
TS_WLM ),
ACTIVITY (TABLE "ACTIVITY_DB2ACTIVITIES", IN TS_WLM ),
ACTIVITYSTMT (TABLE "ACTIVITYSTMT_DB2ACTIVITIES", IN
TS_WLM ),
ACTIVITYVALS (TABLE "ACTIVITYVALS_DB2ACTIVITIES", IN
TS_WLM )
AUTOSTART;
·
For DB2 v9.7 fp 3a and earlier.
No ACTIVITYMETRICS is available so run this to create the Activity Event
monitor
·
CREATE EVENT
MONITOR "DB2ACTIVITIES"
FOR ACTIVITIES
WRITE TO TABLE
CONTROL (TABLE "CONTROL_DB2ACTIVITIES", IN TS_WLM ),
ACTIVITYMETRICS
(TABLE "ACTIVITYMETRICS_DB2ACTIVITIES", IN TS_WLM ),
ACTIVITY (TABLE
"ACTIVITY_DB2ACTIVITIES", IN TS_WLM
),
ACTIVITYSTMT (TABLE
"ACTIVITYSTMT_DB2ACTIVITIES", IN TS_WLM ),
ACTIVITYVALS (TABLE
"ACTIVITYVALS_DB2ACTIVITIES", IN TS_WLM )
AUTOSTART;
Turn on the Event Monitor:
SET EVENT MONITOR DB2ACTIVITIES STATE 1;
· Enable Workload manager to collect data
ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA
ON COORDINATOR WITH DETAILS;
ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER
SYSDEFAULTUSERCLASS COLLECT AGGREGATE ACTIVITY DATA BASE;
· Update database configuration to gather WLM statistics every 2 hours into the event monitor tables
UPDATE DB CFG FOR <dbname> USING WLM_COLLECT_INT 120
· Example of a script to gather Activity Data
SELECT
VARCHAR(A.APPL_NAME, 15) as APPL_NAME,
VARCHAR(A.SESSION_AUTH_ID, 10) as
AUTH_ID,
A.TIME_STARTED,
A.TIME_COMPLETED,
A.QUERY_COST_ESTIMATE,
A.TOTAL_SORT_TIME,
A.SYSTEM_CPU_TIME,
A.USER_CPU_TIME,
A.ROWS_FETCHED as ROWS_READ,
A.ROWS_RETURNED,
A.ROWS_MODIFIED,
VARCHAR(S.STMT_TEXT, 300) AS STMT_TEXT
FROM
ACTIVITY_DB2ACTIVITIES AS A,
ACTIVITYSTMT_DB2ACTIVITIES AS S
WHERE
A.APPL_ID = S.APPL_ID AND
A.ACTIVITY_ID = S.ACTIVITY_ID AND
A.UOW_ID = S.UOW_ID
ORDER BY
A.QUERY_COST_ESTIMATE
DESC
FETCH FIRST
10 ROWS ONLY;
