MON_COLLECT_STATS procedure - Collect and reset statistics for workload management objects and operating system resources

The MON_COLLECT_STATS procedure gathers or resets statistics for service classes, workloads, work classes, threshold queues, and operating system resources.

If there is an active statistics event monitor, the MON_COLLECT_STATS procedure writes the statistics it collects to the statistics event monitor.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority
WLMADM authority

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine 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.

Usage notes

The MON_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 MON_COLLECT_STATS procedure only starts the collection and reset process. 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 occur, the call to the MON_COLLECT_STATS procedure (which is itself an activity) is counted in the statistics for either the prior collection interval or the new collection interval that has just started.

In synchronous mode, the MON_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.

Examples

Call MON_COLLECT_STATS to initiate statistics collection and reset.
```
  CALL MON_COLLECT_STATS()
```
The following is an example of output from this query:
```
  Return Status = 0
```
Call MON_COLLECT_STATS to collect and reset statistics, and return only when data has been written to statistics event monitor tables.
```
  CALL MON_COLLECT_STATS('Y', ::collect_timestamp)
```
The following is an example of output from this query:
```
  Return Status = 0
```

Call MON_COLLECT_STATS to collect and reset statistics while another call is in progress.

  CALL MON_COLLECT_STATS()

The following is an example of output from this query:

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