DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_COLLECT_STATS procedure - Collect and reset workload management statistics

The WLM_COLLECT_STATS procedure gathers statistics for service classes, workloads, work classes, and threshold queues and writes them to the statistics event monitor. The procedure also resets the statistics for service classes, workloads, work classes, and threshold queues. If there is no active statistics event monitor, the procedure only resets the statistics.

Syntax


>>-WLM_COLLECT_STATS--(--+-------------------------------+--)--><
                         '-wait--,--statistics_timestamp-'

The schema is SYSPROC.

Procedure parameters

wait: An optional input argument of type CHAR that specifies whether this procedure returns immediately after initiating a statistics collection and reset. If 'Y' is specified, then the procedure will not return until all statistics have been written and flushed to the statistics event monitor tables. Otherwise, the procedure will return immediately after initiating a statistics collection and reset.
statistics_timestamp: An optional output argument of type TIMESTAMP that returns the timestamp value for the beginning of the statistics collection.

Authorization

EXECUTE privilege on the WLM_COLLECT_STATS procedure.

Examples

Example 1: Call WLM_COLLECT_STATS to initiate statistics collection and reset.

  CALL WLM_COLLECT_STATS()

The following example is a sample output from this query.

  Return Status = 0

Example 2: Call WLM_COLLECT_STATS to collect and reset statistics, but not return until data has been written to statistics event monitor tables.

  CALL WLM_COLLECT_STATS('Y', ::collect_timestamp)

The following example is a sample output from this query.

  Return Status = 0

Example 3: Call WLM_COLLECT_STATS to collect and reset statistics while another call is in progress.

  CALL WLM_COLLECT_STATS()

The following example is a sample output from this query.

  SQL1632W The collect and reset statistics request was ignored because 
  another collect and reset statistics request is already in progress.

Usage notes

The WLM_COLLECT_STATS procedure performs the same collection operation (send statistics to the active statistics event monitor) and reset operation that occur automatically on the interval defined by the wlm_collect_int database configuration parameter.

If you call the procedure while another collection and reset request is in progress (for example, while another invocation of the procedure is running or automatic collection is occurring), SQL1632W with SQLSTATE 01H53 is returned, and your new request is ignored.

In asynchronous mode, the WLM_COLLECT_STATS procedure only starts the collection and reset process. It might return before the process has completed, that is, the procedure might return to the caller before all statistics have been written to the active statistics event monitor. Depending on how quickly the statistics collection and reset occurs, the call to the WLM_COLLECT_STATS procedure (which is itself an activity and will be counted in activity statistics) might be counted in either the prior collection interval or the new collection interval that has just started.

In synchronous mode, the WLM_COLLECT_STATS procedure does not return until the statistics collection is complete and all statistics are written to the tables of any active statistics event monitors. The timestamp at which the statistics collection began is returned via the statistics_timestamp output parameter.