DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_WORKLOAD_STATS table function - return workload statistics

Note: This table function has been deprecated and replaced by the WLM_GET_WORKLOAD_STATS_V97 table function - Return workload statistics.

This function returns workload statistics for every combination of workload name and database partition number.

Syntax


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

The schema is SYSPROC.

Table function parameters

workload_name: An input argument of type VARCHAR(128) that specifies a specific 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 a valid partition number in the same instance as the currently connected database when calling this function. 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 function.

Example

An administrator may want to look at the statistics for workloads. She could do so using the following query:

  SELECT SUBSTR(WORKLOAD_NAME,1,22) AS WL_DEF_NAME,
       SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
       CONCURRENT_WLO_TOP AS WLO_TOP,
       CONCURRENT_WLO_ACT_TOP AS WLO_ACT_TOP
  FROM TABLE(WLM_GET_WORKLOAD_STATS(CAST(NULL AS VARCHAR(128)), -2)) 
    AS WLSTATS 
  ORDER BY WL_DEF_NAME, PART

The following example is a sample output from this query.

WL_DEF_NAME            PART WLO_TOP         WLO_ACT_TOP
---------------------- ---- --------------- -------------------
MYUSERWORKLOAD         0                  2                   8
MYUSERWORKLOAD         1                  0                   0
SYSDEFAULTUSERWORKLOAD 0                  1                   1
SYSDEFAULTUSERWORKLOAD 1                  0                   0

Here we see that on partition 0, the highest number of concurrent occurrences of the MYUSERWORKLOAD workload was 2 and that the highest number of concurrent activities in either of these workload occurrences was 8.

Usage note

This function returns one row for every combination of workload name and database partition number. No aggregation across workloads or across partitions or across service classes is performed. However, aggregation can be achieved through SQL queries.

Information returned

Table 1. Information returned by WLM_GET_WORKLOAD_STATS
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