How to display SYSTEM_CPU_TIME for DB2 statements on Linux in Workload Manager for DB2 (WLM)

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

The field SYSTEM_CPU_TIME is not populated in table WLM_EVENT created by Workload Manager by default in newer Linux releases


Cause

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:

db2set DB2_SYSTEM_MONITOR_SETTINGS=OLD_CPU_USAGE:TRUE

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)
AUTOSTART;


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;

Related information

General Registry Variables

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
Monitor - Performance monitor

Software version:

9.7

Operating system(s):

Linux

Reference #:

1450854

Modified date:

2013-01-22

Translate my page

Machine Translation

Content navigation