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