How to display SYSTEM_CPU_TIME for DB2 statements on Linux in Workload Manager for DB2 (WLM)
Workload Manger in DB2 (WLM) does not show SYSTEM_CPU_TIME on Linux Redhat Enterprise Linux (RHEL) 5 and SuSE Linux Enterprise Server (SLES) 10.
The field SYSTEM_CPU_TIME is not populated in table WLM_EVENT created by Workload Manager by default in newer Linux releases
Depending on the Linux version there are two flavors of CPU usage collection. SLES 10 and RHEL5 use the newer (faster) collection method by default.
The new style collection is faster as it uses less overhead. However it only collects User CPU time but not System CPU time.
The old style collection method which is used by RHEL 4 and SLES 9 is slower due to a higher overhead. However it collects both System and User CPU times.
You can enable the old style collection (slower) with the following setting:
Note: A setting of FALSE for OLD_CPU_USAGE will be ignored - only the old behavior will be used.
Resolving the problem
Creating and activating an event monitor:
1. Connect to a database (in this example the database is SAMPLE)
db2 connect to sample
2. Create the event monitor:
db2 CREATE EVENT MONITOR WLM_EVENT FOR ACTIVITIES
WRITE TO TABLE ACTIVITY (TABLE WLM_EVENT ),
ACTIVITYSTMT (TABLE WLM_EVENT_STMT ),
ACTIVITYVALS (TABLE WLM_EVENT_VALS),
CONTROL (TABLE WLM_EVENT_CONTROL)
3. Activate the event monitor:
SET EVENT MONITOR WLM_EVENT STATE 1
4. Alter service class:
ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS
COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS
WITH DETAILS COLLECT AGGREGATE ACTIVITY DATA EXTENDED
5. select data from WLM_EVENT table:
SELECT APPL_ID, APPL_NAME, TIME_COMPLETED, TIME_STARTED, TIME_CREATED,
SYSTEM_CPU_TIME, USER_CPU_TIME from WLM_EVENT;