DB2 10.5 for Linux, UNIX, and Windows

Exercise 1: Getting started with basic monitoring using default DB2 workload management objects

This exercise demonstrates the basic types of monitoring information that can be obtained from the default workload and service class objects.

Estimated time: 20-25 minutes

By default, the user workload (SYSDEFAULTUSERWORKLOAD) and a default user service class (SYSDEFAULTUSERCLASS) are always created for each database. These default objects can be used to take advantage of the new DB2® workload management monitoring features without having to create any user defined workloads or service classes. If no user defined workloads and service classes are created, all user activities will be associated with these default objects.

There are two separate features of monitoring that are demonstrated by this exercise:

The ability to collect aggregate statistics for all activities that run in a service class. Aggregate activity statistics provide an inexpensive way of looking at work in a service class as a whole. They show information like the number of activities that ran in the service class, and the average lifetime of those activities.
The ability to capture information about individual activities. Activity information can be useful when investigating the performance or behavior of a particular activity. Activity information includes things such as statement text, compilation environment, etc. Activity information is more expensive to collect than aggregate activity statistics and is usually targeted towards a specific subset of activities.

Step 1: Create and enable event monitors

Connect to the database and create and enable event monitors for activities and statistics.

CONNECT TO SAMPLE

CREATE EVENT MONITOR DB2ACTIVITIES FOR ACTIVITIES WRITE TO TABLE
CREATE EVENT MONITOR DB2STATISTICS FOR STATISTICS WRITE TO TABLE
 
SET EVENT MONITOR DB2ACTIVITIES STATE 1
SET EVENT MONITOR DB2STATISTICS STATE 1

Step 2: Collect individual activities

Enable collection of individual activities using the COLLECT ACTIVITY DATA clause on the CREATE or ALTER WORKLOAD STATEMENT. When the COLLECT ACTIVITY DATA clause is specified for a workload, information about any activity submitted by an occurrence of that workload will be sent to the active ACTIVITIES event monitor when the activity completes. The COLLECT ACTIVITY DATA clause permits you to specify how much information should be collected by applying one of the following options:

WITHOUT DETAILS: Collect activity information without statement and compilation environment.
WITH DETAILS: Collect activity information including statement and compilation environment.
WITH DETAILS AND VALUES: Collect activity information including statement and compilation environment, and input data values.

For this exercise, you will specify the WITH DETAILS clause so that the statement text information is captured.

ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD
   COLLECT ACTIVITY DATA ON COORDINATOR WITH DETAILS

In this example activity data is collected for the default user workload. This results in information about all user activities being collected since no other user defined workloads are currently active. This would be too expensive in a production environment. A better approach would be to isolate the activities of interest using a specific user defined workload or service class and apply the COLLECT ACTIVITY DATA clause to that workload or service class only.

Additional Information: The COLLECT ACTIVITY DATA clause can also be specified on a service class, work class (using a work action) or a threshold. If the clause is specified for a service class, information will be collected for any activity that runs in the service class. If it is specified for a work class (using a work action), any activity that has the work action applied to it will be collected. If the clause is specified for a threshold, activity information will be collected if the threshold is violated.

Step 3: Collect aggregate activity statistics

Enable collection of aggregate activity statistics for the default subclass under the default user service class using the COLLECT AGGREGATE ACTIVITY DATA clause. When this clause is specified, aggregate statistics will be maintained in memory for the corresponding service class (for example, statistics such as average activity lifetime). The statistics can be viewed using the service subclass statistics table function, or can be collected and sent to the active statistics event monitor for later analysis.

ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS
   COLLECT AGGREGATE ACTIVITY DATA BASE

Additional Information: There is a set of statistics collected by default for all DB2 workload management objects. The COLLECT AGGREGATE ACTIVITY DATA clause enables collection of a number of additional optional statistics, such as the activity lifetime histogram.

In this example all user activities will be run in the SYSDEFAULTSUBCLASS service subclass under the SYSDEFAULTUSERCLASS service super class because no user defined service classes have been created. Therefore, information will be collected for all user activities.

Step 4: Run some activities

Run some activities, which will result in statistics being updated and the activities being collected.

db2 -o -tvf work1.db2
db2 -o -tvf work2.db2

The scripts representing applications (such as work1.db2 and work2.db2) disconnect you from the database, so that after running them you will need to reconnect.

Step 5: View statistics

You can view the service class statistics using the WLM_GET_SERVICE_SUBCLASS_STATS table function. For example:

CONNECT TO SAMPLE

SELECT VARCHAR(SERVICE_SUPERCLASS_NAME, 30) AS SUPERCLASS,
       VARCHAR(SERVICE_SUBCLASS_NAME, 30) AS SUBCLASS,
       LAST_RESET,
       COORD_ACT_COMPLETED_TOTAL,
       COORD_ACT_REJECTED_TOTAL,
       COORD_ACT_ABORTED_TOTAL,
       COORD_ACT_LIFETIME_AVG
FROM TABLE(SYSPROC.WLM_GET_SERVICE_SUBCLASS_STATS('SYSDEFAULTUSERCLASS', 
       'SYSDEFAULTSUBCLASS', -1)) AS T

The output from this query will look something such as the following:

SUPERCLASS                     SUBCLASS                       LAST_RESET
COORD_ACT_COMPLETED_TOTAL COORD_ACT_REJECTED_TOTAL COORD_ACT_ABORTED_TOTAL
COORD_ACT_LIFETIME_AVG 
------------------------------ ------------------------------ -------------------------- --
----------------------- ------------------------ ----------------------- ------------------
------
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS             2007-07-18-16.03.51.752190
74                        0                       0   +1.40288000000000E+002

  1 record(s) selected.

The COORD_ACT_COMPLETED_TOTAL column indicates how many activities have completed successfully in this service class. The last reset time indicates the last time that statistics were reset for this service class.

Additional Information: If you do not enable aggregate activity statistics for a service class using the COLLECT AGGREGATE ACTIVITY DATA clause, some statistics reported by the WLM_GET_SERVICE_SUBCLASS_STATS table function will be NULL.

Step 6: Send statistics to event monitor

Use the WLM_COLLECT_STATS stored procedure to send the statistics for all DB2 workload management objects to the active statistics event monitor. When statistics are collected and sent to the statistics event monitor, the values are reset.

CALL SYSPROC.WLM_COLLECT_STATS()

Additional Information: If there is no active statistics event monitor, you can still use the WLM_COLLECT_STATS procedure to reset the statistics, but the current values will be lost. It is possible to automate workload management statistics collection using the WLM_COLLECT_INT database configuration parameter. If you set this parameter to a nonzero value, workload management statistics will be collected automatically every wlm_collect_int minutes (as if you manually invoked the WLM_COLLECT_STATS procedure every wlm_collect_int minutes).

Step 7: View statistics again

Invoke the WLM_GET_SERVICE_SUBCLASS_STATS table function again. Note that the LAST_RESET timestamp has been updated and the statistics have been reset.

SELECT VARCHAR(SERVICE_SUPERCLASS_NAME, 30) AS SUPERCLASS,
       VARCHAR(SERVICE_SUBCLASS_NAME, 30) AS SUBCLASS,
       LAST_RESET,
       COORD_ACT_COMPLETED_TOTAL,
       COORD_ACT_REJECTED_TOTAL,
       COORD_ACT_ABORTED_TOTAL,
       COORD_ACT_LIFETIME_AVG
FROM TABLE(SYSPROC.WLM_GET_SERVICE_SUBCLASS_STATS('SYSDEFAULTUSERCLASS',
       'SYSDEFAULTSUBCLASS', -1)) AS T

The output will look something like:

SUPERCLASS                     SUBCLASS                       LAST_RESET     
COORD_ACT_COMPLETED_TOTAL COORD_ACT_REJECTED_TOTAL
COORD_ACT_ABORTED_TOTAL COORD_ACT_LIFETIME_AVG 
------------------------------ ------------------------------ ----------
---------------- ------------------------- ------------------------ ----
------------------- ------------------------
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS             2007-07-18-
16.04.03.505818                         0                        0           
0   +0.00000000000000E+000

  1 record(s) selected.

Step 8: View service class statistics collected by the statistics event monitor

The WLM_COLLECT_STATS procedure sent the service class statistics to the statistics event monitor. You can look at the statistics that were collected by the event monitor using statement such as the following:

SELECT VARCHAR(SERVICE_SUPERCLASS_NAME, 30) AS SUPERCLASS,
       VARCHAR(SERVICE_SUBCLASS_NAME, 30) AS SUBCLASS,
       LAST_WLM_RESET,
       STATISTICS_TIMESTAMP,
       COORD_ACT_COMPLETED_TOTAL,
       COORD_ACT_REJECTED_TOTAL,
       COORD_ACT_ABORTED_TOTAL,
       COORD_ACT_LIFETIME_AVG
FROM SCSTATS_DB2STATISTICS

The output will look something like:

SUPERCLASS                     SUBCLASS                      
LAST_WLM_RESET             STATISTICS_TIMESTAMP      
COORD_ACT_COMPLETED_TOTAL COORD_ACT_REJECTED_TOTAL
COORD_ACT_ABORTED_TOTAL COORD_ACT_LIFETIME_AVG
------------------------------ ------------------------------ -----------
--------------- -------------------------- ------------------------- ----
-------------------- ----------------------- ----------------------
SYSDEFAULTSYSTEMCLASS          SYSDEFAULTSUBCLASS             2007-07-18-
16.03.46.333724 2007-07-18-16.04.03.505818                         0
0                       0                     -1
SYSDEFAULTMAINTENANCECLASS     SYSDEFAULTSUBCLASS             2007-07-18-
16.03.46.334301 2007-07-18-16.04.03.505818                         0
0                       0                     -1
SYSDEFAULTUSERCLASS            SYSDEFAULTSUBCLASS             2007-07-18-
16.03.51.752190 2007-07-18-16.04.03.505818                        75
0                       0                    136

  3 record(s) selected.

Every time statistics are sent to the event monitor, a statistics record will be created for each DB2 workload management object. Note the two timestamps LAST_WLM_RESET and STATISTICS_TIMESTAMP. The interval of time from LAST_WLM_RESET to STATISTICS_TIMESTAMP indicates the period of time over which the statistics in that record were collected. The STATISTICS_TIMESTAMP indicates when the statistics were collected. Note that the average lifetime for activities on the coordinator is -1 for the default system and maintenance service classes. The average activity lifetime statistic is only maintained for a service class if aggregate activity statistics are enabled using the COLLECT AGGREGATE ACTIVITY DATA clause.

Step 9: View activity information

Information about every individual activity associated with the default user workload was also collected by the activities event monitor, due to the specification of the COLLECT ACTIVITY DATA clause on the default workload in step 2. You can look at this activity information using a query such as the following:

SELECT VARCHAR(A.APPL_NAME, 15) as APPL_NAME,
       VARCHAR(A.TPMON_CLIENT_APP, 20) AS CLIENT_APP_NAME,
       VARCHAR(A.APPL_ID, 30) as APPL_ID,
       A.ACTIVITY_ID,
       A.UOW_ID,
       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

The output will look something like:

APPL_NAME       CLIENT_APP_NAME      APPL_ID                        
ACTIVITY_ID          UOW_ID      STMT_TEXT
--------------- -------------------- ------------------------------ -----
--------------- ----------- ---------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
------------------------------------
db2bp           CLP wlmmonbasic.db2  *LOCAL.db2inst1.070718200344
1           8 ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER
SYSDEFAULTUSERCLASS COLLECT AGGREGATE ACTIVITY DATA BASE
db2bp           CLP work1.db2        *LOCAL.db2inst1.070718200352
1           1 values(current client_applname)
db2bp           CLP work1.db2        *LOCAL.db2inst1.070718200352
2           1 select * from org
db2bp           CLP work1.db2        *LOCAL.db2inst1.070718200352
3           1 select * from employee
db2bp           CLP work1.db2        *LOCAL.db2inst1.070718200352
4           1 select * from sales

...

Note that you may see some truncation warnings (SQL0445).

When CLP executes a script, it will set the CURRENT CLIENT_APPLNAME special register to "CLP script name". So you can tell from the query shown previously, which script submitted each activity.

Step 10: Reset for next exercise

Update the SYSDEFAULTUSERWORKLOAD workload and the SYSDEFAULTSUBCLASS service subclass so that no activity data or aggregate activity statistics is collected, disable event monitors and clear out the activity and statistics tables, and call WLM_COLLECT_STATS() to reset the statistics.

ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD COLLECT ACTIVITY DATA NONE

ALTER SERVICE CLASS SYSDEFAULTSUBCLASS UNDER SYSDEFAULTUSERCLASS
      COLLECT AGGREGATE ACTIVITY DATA NONE

SET EVENT MONITOR DB2ACTIVITIES STATE 0
SET EVENT MONITOR DB2STATISTICS STATE 0

DELETE FROM ACTIVITY_DB2ACTIVITIES
DELETE FROM ACTIVITYSTMT_DB2ACTIVITIES
DELETE FROM SCSTATS_DB2STATISTICS
DELETE FROM WLSTATS_DB2STATISTICS

CALL WLM_COLLECT_STATS()