DB2 10.5 for Linux, UNIX, and Windows

Resetting statistics on DB2 workload management objects

This topic describes how to reset statistics for DB2® workload management objects.

Note that resetting statistics applies only to DB2 workload management statistics; metrics reported by monitoring interfaces will be collected, but not reset.

Four events will reset the statistics stored for each DB2 workload management object. (For a description of the statistics maintained for each object, see Statistics for DB2 workload management objects.)

The WLM_COLLECT_STATS stored procedure is invoked. See Collecting workload management statistics using a statistics event monitor for details.
The automatic DB2 workload management statistics collection and reset process controlled by the wlm_collect_int database configuration parameter causes a collection and reset. See Collecting workload management statistics using a statistics event monitor for details.
The database is reactivated. Every time the database is activated on a member, the statistics for all DB2 workload management objects on that member are reset.
The object for which the statistics are maintained is modified and the change is committed. For example if a service subclass is altered, when the ALTER statement is committed, the statistics for that service subclass are reset.

You can determine the last time the statistics were reset for a given DB2 workload management object using the statistics table functions and looking at timestamp in the LAST_RESET column. For example, to see the last time the statistics were reset for the service subclass SYSDEFAULTSUBCLASS under the SYSDEFAULTUSERCLASS service superclass, you could issue a query such as:

SELECT LAST_RESET
   FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS( 'SYSDEFAULTUSERCLASS',
   'SYSDEFAULTSUBCLASS', -2)) AS T

All statistics table functions return the statistics that accumulated since the last time that the statistics were reset. A statistics reset occurs when a database is activated or reactivated, when you alter a DB2 workload management object (only the statistics for that object are reset), and when you call the WLM_COLLECT_STATS stored procedure. Statistics are also reset automatically according to the time period defined by the wlm_collect_int database configuration parameter, if you set this parameter to a nonzero value.

The period of time specified by wlm_collect_int is unaffected by a statistics reset that occurs during the interval specified by the configuration parameter. For example, if you run the WLM_COLLECT_STATS table function 5 minutes after the start of a 20-minute interval specified by wlm_collect_int, the interval still expires 15 minutes later. The statistics collection and reset that occur do not delay the occurrence of the next statistics collection and reset by 5 minutes.

If you change a service class or a workload to use a different histogram template or change a histogram template, the change does not take effect until a statistics reset occurs.

If you invoke the WLM_COLLECT_STATS table function to collect and reset statistics at the same time that another collection and reset is in progress (for example, if the invocation of the table function overlaps with the periodic collection and reset interval caused by wlm_collect_int or if another user invokes WLM_COLLECT_STATS at the same time), the collection and reset request from WLM_COLLECT_STATS is ignored, and warning SQL1632W is returned.