DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_WORKLOAD_STATS_V97 table function - Return workload statistics

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

Syntax


>>-WLM_GET_WORKLOAD_STATS_V97--(--workload_name--,--dbpartitionnum--)-><

The schema is SYSPROC.

Table function 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.
dbpartitionnum: An input argument of type INTEGER that specifies the number of a partition in the same instance as the currently connected database. Specify -1 for the current database partition, or -2 for all database partitions. If a null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the WLM_GET_WORKLOAD_STATS_V97 function.

Example

The following query displays statistics for workloads:

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

Sample output from the query is as follows:

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

Usage note

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

Information returned

Table 1. Information returned by WLM_GET_WORKLOAD_STATS_V97
Column name	Data type	Description
WORKLOAD_NAME	VARCHAR(128)	workload_name - Workload name monitor element
DBPARTITIONNUM	SMALLINT	Partition number from which this record was collected
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