DB2 Version 10.1 for Linux, UNIX, and Windows

WLM_GET_QUEUE_STATS table function - Return threshold queue statistics

The WLM_GET_QUEUE_STATS function returns basic statistics for one or more threshold queues on all active members. This function returns one row of statistics for each threshold queue.

Syntax


>>-WLM_GET_QUEUE_STATS--(--threshold_predicate--,--threshold_domain--,-->

>--threshold_name--,--threshold_id--)--------------------------><

The schema is SYSPROC.

Table function parameters

threshold_predicate

An input argument of type VARCHAR(27) that specifies a threshold predicate. The possible values are as follows:

CONCDBC: Concurrent database coordinator activities threshold
DBCONN: Total database member connections threshold
SCCONN: Total service class member connections threshold

If the argument is null or an empty string, data is returned for all thresholds that meet the other criteria.

The threshold_predicate values match those of the THRESHOLDPREDICATE column in the SYSCAT.THRESHOLDS view.

threshold_domain

An input argument of type VARCHAR(18) that specifies a threshold domain. The possible values are as follows:

DB: Database
SB: Service subclass
SP: Service superclass
WA: Work action set

If the argument is null or an empty string, data is returned for all thresholds that meet the other criteria.

The threshold_domain values match those of the DOMAIN column in the SYSCAT.THRESHOLDS view.

threshold_name

An input argument of type VARCHAR(128) that specifies a threshold name. If the argument is null or an empty string, data is returned for all thresholds that meet the other criteria. The threshold_name values match those of the THRESHOLDNAME column in the SYSCAT.THRESHOLDS view.

threshold_id

An input argument of type INTEGER that specifies a threshold ID. If the argument is null or -1, data is returned for all thresholds that meet the other criteria. The threshold_id values match those of the THRESHOLDID column in the SYSCAT.THRESHOLDS view.

Authorization

One of the following authorities is required to execute the routine:

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

Default PUBLIC privilege

None

Example

The following query displays the basic statistics for all the queues on a system, across all members:

  SELECT substr(THRESHOLD_NAME, 1, 6) THRESHNAME,
       THRESHOLD_PREDICATE,
       THRESHOLD_DOMAIN,
       MEMBER MEMB,
       QUEUE_SIZE_TOP,
       QUEUE_TIME_TOTAL,
       QUEUE_ASSIGNMENTS_TOTAL QUEUE_ASSIGN 
  FROM table(WLM_GET_QUEUE_STATS('', '', '', -1)) as QSTATS

Sample output is as follows:

THRESHNAME THRESHOLD_PREDICATE         THRESHOLD_DOMAIN   ...
---------- --------------------------- ------------------ ...
LIMIT1     CONCDBC                     DB                 ...
LIMIT2     SCCONN                      SP                 ...
LIMIT3     DBCONN                      DB                 ...

... MEMB QUEUE_SIZE_TOP QUEUE_TIME_TOTAL QUEUE_ASSIGN 
... ---- -------------- ---------------- ------------ 
... 0             12          1238540          734
... 0              4           741249           24
... 0              7           412785          128

Usage note

The function does not aggregate data across queues (on a member) or across members (for one or more queues). However, you can use SQL queries to aggregate data, as shown in the previous example.

Information returned

Table 1. Information returned for WLM_GET_QUEUE_STATS
Column name	Data type	Description
THRESHOLD_PREDICATE	VARCHAR(27)	threshold_predicate - Threshold predicate monitor element
THRESHOLD_DOMAIN	VARCHAR(18)	threshold_domain - Threshold domain monitor element
THRESHOLD_NAME	VARCHAR(128)	threshold_name - Threshold name monitor element
THRESHOLD_ID	INTEGER	thresholdid - Threshold ID monitor element
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
SERVICE_SUPERCLASS_NAME	VARCHAR(128)	service_superclass_name - Service superclass name monitor element
SERVICE_SUBCLASS_NAME	VARCHAR(128)	service_subclass_name - Service subclass name monitor element
WORK_ACTION_SET_NAME	VARCHAR(128)	work_action_set_name - Work action set name monitor element
WORK_CLASS_NAME	VARCHAR(128)	work_class_name - Work class name monitor element
WORKLOAD_NAME	VARCHAR(128)	workload_name - Workload name monitor element
LAST_RESET	TIMESTAMP	last_reset - Last Reset Timestamp monitor element
QUEUE_SIZE_TOP	INTEGER	queue_size_top - Queue size top monitor element
QUEUE_TIME_TOTAL	BIGINT	queue_time_total - Queue time total monitor element
QUEUE_ASSIGNMENTS_TOTAL	BIGINT	queue_assignments_total - Queue assignments total monitor element
QUEUE_SIZE_CURRENT	INTEGER	Number of connections or activities in the queue.
QUEUE_TIME_LATEST	BIGINT	Time spent in the queue by the last connection or activity to leave the queue. Units are milliseconds.
QUEUE_EXIT_TIME_LATEST	TIMESTAMP	Time that the last connection or activity left the queue.
THRESHOLD_CURRENT_CONCURRENCY	INTEGER	Number of connections or activities that are currently running according to the threshold.
THRESHOLD_MAX_CONCURRENCY	INTEGER	Maximum number of connections or activities that the threshold allows to be concurrently running.
MEMBER	SMALLINT	member - Database member monitor element