MON_GET_WORKLOAD_STATS table function - Return workload statistics

The MON_GET_WORKLOAD_STATS function returns one row of workload statistics for every combination of workload name and database member number.

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

workload_name

An input argument of type VARCHAR(128) that specifies a workload for which the statistics are to be returned. If the argument is NULL or an empty string, statistics are returned for all workloads.

member

An input argument of type INTEGER that specifies the number of a member in the same instance as the currently connected database. Specify -1 for the current member, or -2 for all active database members. If a null value is specified, -1 is set implicitly.

Information returned

Table 1. Information returned by MON_GET_WORKLOAD_STATS
Column name	Data type	Description
WORKLOAD_NAME	VARCHAR(128)	workload_name - Workload name monitor element
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
LAST_RESET	TIMESTAMP	last_reset - Last Reset Timestamp monitor element
CONCURRENT_WLO_TOP	INTEGER	concurrent_wlo_top - Concurrent workload occurrences top monitor element
CONCURRENT_WLO_ACT_TOP	INTEGER	concurrent_wlo_act_top - Concurrent WLO activity top monitor element
COORD_ACT_COMPLETED_TOTAL	BIGINT	coord_act_completed_total - Coordinator activities completed total monitor element
COORD_ACT_ABORTED_TOTAL	BIGINT	coord_act_aborted_total - Coordinator activities aborted total monitor element
COORD_ACT_REJECTED_TOTAL	BIGINT	coord_act_rejected_total - Coordinator activities rejected total monitor element
WLO_COMPLETED_TOTAL	BIGINT	wlo_completed_total - Workload occurrences completed total monitor element
COORD_ACT_LIFETIME_TOP	BIGINT	coord_act_lifetime_top - Coordinator activity lifetime top monitor element
COORD_ACT_LIFETIME_AVG	DOUBLE	coord_act_lifetime_avg - Coordinator activity lifetime average monitor element
COORD_ACT_LIFETIME_STDDEV	DOUBLE	Standard deviation of lifetime for completed or aborted coordinator activities at nesting level 0 that are associated with this workload. Units are milliseconds. If the COLLECT AGGREGATE ACTIVITY DATA parameter of the workload is set to NONE, the value of the column is null. This standard deviation is computed from the coordinator activity lifetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. If any values fall into the last histogram bin, the value -1 is returned.
COORD_ACT_EXEC_TIME_AVG	DOUBLE	coord_act_exec_time_avg - Coordinator activities execution time average monitor element
COORD_ACT_EXEC_TIME_STDDEV	DOUBLE	Standard deviation of the execution times for completed or aborted coordinator activities at nesting level 0 that are associated with this workload. Units are milliseconds. This standard deviation is computed from the coordinator activity executetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. If any values fall into the last histogram bin, the value -1 is returned. If the COLLECT AGGREGATE ACTIVITY DATA parameter of the workload is set to NONE, the value of the column is null.
COORD_ACT_QUEUE_TIME_AVG	DOUBLE	coord_act_queue_time_avg - Coordinator activity queue time average monitor element
COORD_ACT_QUEUE_TIME_STDDEV	DOUBLE	Standard deviation of the queue time for completed or aborted coordinator activities at nesting level 0 that are associated with this workload. Units are milliseconds. If the COLLECT AGGREGATE ACTIVITY DATA parameter of the workload is set to NONE, the value of the column is null. This standard deviation is computed from the coordinator activity queuetime histogram and may be inaccurate if the histogram is not correctly sized to fit the data. If any values fall into the last histogram bin, the value -1 is returned.
UOW_TOTAL_TIME_TOP	BIGINT	uow_total_time_top - UOW total time top monitor element
UOW_THROUGHPUT	DOUBLE	uow_throughput - Unit of work throughput monitor element
UOW_LIFETIME_AVG	DOUBLE	uow_lifetime_avg - Unit of work lifetime average monitor element
UOW_COMPLETED_TOTAL	BIGINT	uow_completed_total - Total completed units of work monitor element
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time monitor element
TOTAL_DISP_RUN_QUEUE_TIME	BIGINT	total_disp_run_queue_time - Total dispatcher run queue time monitor element
ACT_THROUGHPUT	DOUBLE	act_throughput - Activity throughput monitor element
CPU_UTILIZATION	DOUBLE	cpu_utilization - CPU utilization monitor element
APP_ACT_COMPLETED_TOTAL	BIGINT	app_act_completed_total - Total successful external coordinator activities monitor element
APP_ACT_ABORTED_TOTAL	BIGINT	app_act_aborted_total - Total failed external coordinator activities monitor element
APP_ACT_REJECTED_TOTAL	BIGINT	app_act_rejected_total - Total rejected external coordinator activities monitor element
MEMBER	SMALLINT	member - Database member monitor element
ACTIVE_HASH_GRPBYS_TOP	BIGINT	active_hash_grpbys_top - Active hash Group By operations high watermark
ACTIVE_HASH_JOINS_TOP	BIGINT	active_hash_joins_top - Active hash joins operations high watermark
ACTIVE_OLAP_FUNCS_TOP	BIGINT	active_olap_funcs_top - Active OLAP functions operations high watermark
ACTIVE_PEAS_TOP	BIGINT	active_peas_top - Active partial early aggregations operations high watermark
ACTIVE_PEDS_TOP	BIGINT	active_peds_top - Active partial early distinct operations high watermark
ACTIVE_SORT_CONSUMERS_TOP	BIGINT	active_sort_consumers_top - Active sort consumers high watermark
ACTIVE_SORTS_TOP	BIGINT	active_sorts_top - Active Sorts high watermark
ACTIVE_COL_VECTOR_CONSUMERS_TOP	BIGINT	active_col_vector_consumers_top - Active columnar_vector consumers high watermark
SORT_CONSUMER_HEAP_TOP	BIGINT	sort_consumer_heap_top - Individual private sort heap consumer high watermark
SORT_CONSUMER_SHRHEAP_TOP	BIGINT	sort_consumer_shrheap_top - Individual shared sort heap consumer high watermark
SORT_HEAP_TOP	BIGINT	sort_heap_top - Sort private heap high watermark
SORT_SHRHEAP_TOP	BIGINT	sort_shrheap_top - Sort share heap high watermark
SORT_SHRHEAP_UTILIZATION_AVG	DOUBLE	sort_shrheap_utilization_avg - The average sort shared heap utilization
SORT_SHRHEAP_UTILIZATION_TOP	DOUBLE	sort_shrheap_utilization_top - The peak sort shared heap utilization
SORT_SHRHEAP_AVG	DOUBLE	sort_shrheap_avg - The average amount of used sort shared heap
ACTUAL_PARALLELISM_AVG	DOUBLE	actual_parallelism_avg - The average parallelism
ACTUAL_PARALLELISM_TOP	BIGINT	actual_parallelism_top - The peak parallelism
ACT_TOP	BIGINT	act_top - The peak activity count
AGENT_LOAD_TRGT_UTILIZATION_TOP	DOUBLE	agent_load_trgt_utilization_top - The agent load utilization peak.
AGENT_LOAD_TRGT_UTILIZATION_AVG	DOUBLE	agent_load_trgt_utilization_avg - The agent load utilization average.
CONCURRENT_ACT_TOP	BIGINT	concurrent_act_top - The peak concurrent activities for a service subclass
EFF_PARALLELISM_AVG	DOUBLE	eff_parallelism_avg - The average effective parallelism
EFF_PARALLELISM_TOP	BIGINT	eff_parallelism_top - The top effective parallelism
COORD_ACT_EST_COST_AVG	DOUBLE	coord_act_est_cost_avg - Coordinator activity estimated cost average
COORD_ACT_EST_COST_STDDEV	DOUBLE	coord_act_est_cost_stddev - The coordinator activity estimated cost standard deviation
COORD_ACT_INTERARRIVAL_TIME_AVG	DOUBLE	coord_act_interarrival_time_avg - Coordinator activity arrival time average
COORD_ACT_INTERARRIVAL_TIME_STDDEV	DOUBLE	COORD_ACT_INTERARRIVAL_TIME_STDDEV - The coordinator activity inter-arrival time standard deviation
act_sort_shrheap_top	BIGINT	act_sort_shrheap_top - Activity shared sort memory high watermark monitor element
act_estimated_sort_shrheap_top	BIGINT	act_estimated_sort_shrheap_top - Activity estimated shared sort memory high watermark monitor element

Usage notes

The function does not aggregate data across workloads, members, or service classes. However, you can use SQL queries to aggregate data.

Example

The following query displays statistics for workloads:

SELECT SUBSTR(WORKLOAD_NAME,1,18) AS WL_DEF_NAME,
       SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
       COORD_ACT_LIFETIME_TOP,
       COORD_ACT_LIFETIME_AVG,
       COORD_ACT_LIFETIME_STDDEV
FROM TABLE(MON_GET_WORKLOAD_STATS(CAST(NULL AS VARCHAR(128)), -2)) AS WLSTATS
ORDER BY WL_DEF_NAME, MEMB

Sample output from the query is as follows:

WL_DEF_NAME        MEMB COORD_ACT_LIFETIME_TOP ...
------------------ ---- ---------------------- ...
SYSDEFAULTADMWORKL 0                        -1 ...
SYSDEFAULTUSERWORK 0                        -1 ...
WL1                0                         2 ...

... COORD_ACT_LIFETIME_AVG   COORD_ACT_LIFETIME_STDDEV
... ------------------------ -------------------------
... -1.00000000000000E+000    -1.00000000000000E+000
... -1.00000000000000E+000    -1.00000000000000E+000
... +2.56000000000000E+000    +6.00000000000001E-002