DB2 10.5 for Linux, UNIX, and Windows

Collecting workload management statistics using a statistics event monitor

Statistics for DB2® workload management objects can be sent to a statistics event monitor for historical analysis.

About this task

You can use statistics to understand the behavior of your system over time (for example, what is the average lifetime of activities, how much time do activities spend queued, what is the distribution of large compared to small activities, and so on), set thresholds (for example, find the upper boundary for concurrent activities), and detect problems (for example, detect whether the average lifetime that users are experiencing is higher than normal). See Statistics for DB2 workload management objects for a description of which statistics are collected for each DB2 workload management object.

You can automatically send workload management statistics to an event monitor on a fixed interval of time, or you can manually send statistics to an event monitor at any point in time.

Procedure

To automatically collect workload management statistics on a fixed time interval:

Use the CREATE EVENT MONITOR statement to create a STATISTICS event monitor. For example, you could issue the following statement:
```
CREATE EVENT MONITOR STATS1 FOR STATISTICS WRITE TO TABLE
```
Use the COMMIT statement to commit your changes.
Use the SET EVENT MONITOR STATE statement to activate the event monitor. Instead of using the SET EVENT MONITOR STATE statement, you can use the AUTOSTART default for the STATISTICS event monitor to have it activated the next time that the database is activated. If you want to define multiple STATISTICS event monitors, you should not use the AUTOSTART option.
Use the COMMIT statement to commit your changes.
Optional: Enable the collection of additional statistics. By default, only a minimal set of statistics is collected for each DB2 workload management object. See Statistics for DB2 workload management objects for details on which statistics are collected by default for each object. Specify the collection of aggregate activity data for service subclasses, workloads, and work classes using the COLLECT AGGREGATE ACTIVITY DATA keyword on the ALTER SERVICE CLASS and ALTER WORK ACTION SET statements. Specify the collection of aggregate request data for service subclasses using the COLLECT AGGREGATE REQUEST DATA keyword on the ALTER SERVICE CLASS statement. COMMIT any changes.
Specify a collection interval by updating the database configuration parameter wlm_collect_int. The wlm_collect_int parameter specifies an interval of time in minutes. Every interval, the copy of the workload management statistics for all DB2 workload management objects is written to the active statistics event monitor and the statistics are reset. In a multimember database environment, the wlm_collect_int parameter must be updated on the catalog member. This parameter can be updated dynamically. For example:
```
CONNECT TO database alias
UPDATE DATABASE CONFIGURATION USING WLM_COLLECT_INT 5 IMMEDIATE
```

Results

After you perform the preceding steps, workload management statistics are written to the statistics event monitor every wlm_collect_int minutes. Each record written to the statistics event monitor has a STATISTICS_TIMESTAMP value and a LAST_WLM_RESET value. The interval of time from LAST_WLM_RESET to STATISTICS_TIMESTAMP defines the collection interval (that is, interval of time over which the statistics in that record were collected).

Collections occur at the specified interval times as measured relative to Sunday at 00:00:00. When the catalog member becomes active, the next collection will occur at the start of the next scheduled interval relative to this fixed time. The scheduled interval is not relative to the catalog member activation time. If a member is not active at the time of collection, no statistics are gathered for that member. For example, if the interval value was set to 60 and the catalog member was activated on 9:24 AM on Sunday, then the collections would be scheduled to occur each hour on the hour. Therefore, the next collection will occur at 10:00 AM. If the member is not active at 10:00 AM, then no statistics will be gathered for that member

If the wlm_collect_int parameter is set to a nonzero value and there is no active statistics event monitor, the workload management statistics are still reset every wlm_collect_int minutes, but statistics are not collected. The data will be lost. For this reason, it is not recommended that you specify a nonzero wlm_collect_int value without activating a statistics event monitor.

If the wlm_collect_int parameter is set to 0 (the default) statistics are not sent to the statistics event monitor automatically. You can manually send statistics to the statistics event monitor for later historical analysis by using the WLM_COLLECT_STATS stored procedure. When this procedure is invoked, it performs the same actions that occur with an automatic statistics collection interval. That is, the statistics are sent to the statistics event monitor and the statistics are reset. If there is no active statistics event monitor, the values are reset, but data is not collected. If you only want to reset statistics, you can invoke the WLM_COLLECT_STATS procedure while there is no active statistics event monitor.

Manual collection of statistics does not interfere with the automatic collection of statistics. For example, assume that you have wlm_collect_int set to 60. Statistics are sent to the statistics event monitor every hour. Now assume that the last time the statistics were collected was 5:00 AM. You can invoke the WLM_COLLECT_STATS procedure at 5:55 AM, which sends the values of the statistics to the event monitor and resets the statistics. The next automatic statistics collection still occurs at 6:00 AM, one hour after the last automated collection. The collection interval is not affected by any manual collection and resetting of statistics that occurs during the interval.

Notes:

The DB2 workload management statistics table functions report the current values of the statistics. If you have automatic workload management statistics collection enabled, these values are reset periodically on the interval defined by the wlm_collect_int database configuration parameter. When looking at the statistics reported by the table functions, you should always consider the LAST_RESET column. This column indicates the last time the statistics were reset. If the time interval between the last reset time to the current time is not sufficiently large, there may not be enough data to draw any meaningful conclusions.
If you are using automatic collection of workload management statistics, you need to prune your event monitor files or tables periodically. The event monitor does not automatically prune the data that is collected, and the automatic collection will fill your files or tables over time.
When a database is deactivated, the statistics are reset. Deactivating the database does not send statistics to the statistics event monitor. If you do not want to lose the statistics accumulated since the last collection because of a deactivation, you should manually invoke the WLM_COLLECT_STATS procedure before deactivating the database.
The WLM_COLLECT_STATS procedure resets statistics differently than the RESET MONITOR command. The RESET MONITOR command resets the values of snapshot monitor elements by storing their present values. After the RESET MONITOR command has been issued, snapshot processing reports the delta between these values and the current values. In contrast, the reset caused by the WLM_COLLECT_STATS procedure does not store any values, but instead resets all of the statistics counters themselves for each applicable DB2 workload management object.
Also, with the RESET MONITOR command, each process (attachment) has its own private view of the monitor data. If one user performs a reset, other users are unaffected. By contrast, a reset of the workload management statistics applies to all users.