MON_GET_QUEUE_STATS table function - Return threshold and admission control queue statistics

The MON_GET_QUEUE_STATS function returns, for all active members, basic statistics for a single threshold queue, for all admission control queues, or for all threshold and admission control queues. This function returns one row of statistics for each queue.

Authorization

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

Read syntax diagramSkip visual syntax diagram MON_GET_QUEUE_STATS ( predicate , domain , threshold_name , threshold_id )

The schema is SYSPROC.

Routine parameters

predicate
An input argument of type VARCHAR(27) that specifies for which queue or queues statistics are to be retrieved:
CONCDBC
Retrieve statistics for the concurrent database coordinator activities threshold queue.
DBCONN
Retrieve statistics for the total database member connections threshold queue.
SCCONN
Retrieve statistics for the total service class member connections threshold queue.
ADMQUEUE
Retrieve statistics for all admission control queues.
If this argument is null or an empty string, statistics are retrieved for all queues that meet the other criteria.

For a threshold queue, the specified value corresponds to the value in the THRESHOLDPREDICATE column of the SYSCAT.THRESHOLDS view. For an admission control queue, the value in the THRESHOLDPREDICATE column of the SYSCAT.THRESHOLDS view is NULL.

domain
An input argument of type VARCHAR(18) that specifies a threshold or admission control queue domain:
DB
Database.
SB
Service subclass.
SP
Service superclass.
WA
Work action set.
If this argument is null or an empty string, statistics are retrieved for all queues that meet the other criteria.

For a threshold queue, the specified domain corresponds to the value in the DOMAIN column of the SYSCAT.THRESHOLDS view. For an admission control queue, no statistics are contained in the WA domain.

threshold_name
An input argument of type VARCHAR(128) that specifies the name of a particular threshold for which statistics are to be retrieved. The specified name corresponds to the value in the THRESHOLDNAME column of the SYSCAT.THRESHOLDS view. If CONCDBC, DBCONN, or SCCONN is specified for predicate, and if this argument is null or an empty string, statistics are retrieved for all thresholds that satisfy the other criteria. If ADMQUEUE is specified for predicate, this argument must be null or an empty string.
threshold_id
An input argument of type INTEGER that specifies the ID of a particular threshold for which statistics are to be retrieved. The specified ID corresponds to the value in the THRESHOLDID column of the SYSCAT.THRESHOLDS view. If CONCDBC, DBCONN, or SCCONN is specified for predicate, and if this argument is null or an empty string, statistics are retrieved for all thresholds that satisfy the other criteria. If ADMQUEUE is specified for predicate, this argument must be null or an empty string.

Information returned

Table 1. Information returned by the MON_GET_QUEUE_STATS routine
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

Usage notes

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

When rows are returned for admission control queues:
  • The following columns contain the value NULL:
    THRESHOLD_PREDICATE
WORK_ACTION_SET_NAME
WORK_CLASS_NAME
WORKLOAD_NAME
THRESHOLD_ID
TRESHOLD_MAX_CONCURRENCY
  • The THRESHOLD_NAME column contains the value 'WLM_ADMISSION_QUEUE'.
For admission control queues, this function presents a hierarchical view of the monitor data:
  • Subclass rows contain data for their respective admission control queues.
  • Superclass rows contain data aggregated over the admission control queues for the subclasses that belong to the superclass.
  • The database row contains data aggregated over all admission control queues.
The aggregated data provides a general picture of admission-control queuing behavior, and the subclass data provides a more detailed picture of the queuing behavior for a particular service class.

Examples

  • 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(MON_GET_QUEUE_STATS('', '', '', -1)) as QSTATS
    Sample output: 
    THRESHNAME THRESHOLD_PREDICATE         THRESHOLD_DOMAIN   MEMB QUEUE_SIZE_TOP QUEUE_TIME_TOTAL QUEUE_ASSIGN
---------- --------------------------- ------------------ ---- -------------- ---------------- ------------
LIMIT1     CONCDBC                     DB                    0             12          1238540          734
LIMIT2     SCCONN                      SP                    0              4           741249           24
LIMIT3     DBCONN                      DB                    0              7           412785          128
  • On a database for which admission control is enabled, the following query returns all the rows for all admission control queues in all domains:
    SELECT substr(THRESHOLD_DOMAIN, 1, 5) DOMAIN, 
substr(SERVICE_SUPERCLASS_NAME, 1, 20) SUPERCLASS, 
substr(SERVICE_SUBCLASS_NAME, 1, 20) SUBCLASS, 
THRESHOLD_CURRENT_CONCURRENCY as THRESH_CUR_CONCUR, 
QUEUE_SIZE_CURRENT as Q_SZ_CUR, 
QUEUE_SIZE_TOP as Q_SZ_TOP, 
QUEUE_TIME_TOTAL as Q_TI_Tot, 
QUEUE_TIME_LATEST as Q_TI_LTST, 
QUEUE_ASSIGNMENTS_TOTAL as Q_AS_TOT, 
QUEUE_EXIT_TIME_LATEST, 
substr(THRESHOLD_NAME, 1, 20) THRESHOLD_NAME 
FROM table(MON_GET_QUEUE_STATS('', '', '', -1)) 
as QSTATS order by SERVICE_SUPERCLASS_NAME,SERVICE_SUBCLASS_NAME
    Sample output: 
    DOMAIN SUPERCLASS           SUBCLASS             THRESH_CUR_CONCUR Q_SZ_CUR    Q_SZ_TOP    Q_TI_TOT     Q_TI_LTST    Q_AS_TOT  QUEUE_EXIT_TIME_LATEST     THRESHOLD_NAME      
------ -------------------- -------------------- ----------------- ----------- ----------- ------------ ------------ --------- -------------------------- --------------------
SB     PAYROLL_SUPERCLASS   SYSDEFAULTSUBCLASS                   2           9          11       174813        87426        11 2017-03-24-11.38.34.419334 WLM_ADMISSION_QUEUE 
SP     PAYROLL_SUPERCLASS   -                                    2           9          11       174813        87426        11 2017-03-24-11.38.34.419334 WLM_ADMISSION_QUEUE 
SB     SYSDEFAULTUSERCLASS  SYSCOMPLEXSUBCLASS                   0           2           2            0            0         2 -                          WLM_ADMISSION_QUEUE 
SB     SYSDEFAULTUSERCLASS  SYSDEFAULTSUBCLASS                   0           0           0            0            0         0 -                          WLM_ADMISSION_QUEUE 
SB     SYSDEFAULTUSERCLASS  SYSLOADSUBCLASS                      0           0           0            0            0         0 -                          WLM_ADMISSION_QUEUE 
SB     SYSDEFAULTUSERCLASS  SYSMEDIUMSUBCLASS                    1           5           6        93436        93436         6 2017-03-24-11.38.42.060636 WLM_ADMISSION_QUEUE 
SP     SYSDEFAULTUSERCLASS  -                                    1           7           8        93436        93436         8 2017-03-24-11.38.42.060636 WLM_ADMISSION_QUEUE 
DB     -                    -                                    3          16          19       268250        93436        19 2017-03-24-11.38.42.060636 WLM_ADMISSION_QUEUE
  • On a database for which admission control is enabled, the following query returns the current admission control information at the DB level and indicates how many activities are running and how many are queued.:
    SELECT substr(THRESHOLD_DOMAIN, 1, 5) DOMAIN, substr(THRESHOLD_NAME, 1, 20) 
THRESHOLD_NAME, THRESHOLD_CURRENT_CONCURRENCY as THRESH_CUR_CONCUR, QUEUE_SIZE_CURRENT 
as Q_SZ_CUR FROM table(MON_GET_QUEUE_STATS('ADMQUEUE', 'DB', '', -1)) as QSTATS
    Sample output: 
    DOMAIN THRESHOLD_NAME       THRESH_CUR_CONCUR Q_SZ_CUR   
------ -------------------- ----------------- -----------
DB     WLM_ADMISSION_QUEUE                  3          16
  • On a database for which admission control is enabled, the following query returns the current admission control information at the subclass level and indicates how many activities are running and how many are queued for each subclass:
    SELECT substr(THRESHOLD_DOMAIN, 1, 5) DOMAIN, 
substr(SERVICE_SUPERCLASS_NAME, 1, 20) SUPERCLASS,
substr(SERVICE_SUBCLASS_NAME, 1, 20) SUBCLASS, 
substr(THRESHOLD_NAME, 1, 20) THRESHOLD_NAME, 
THRESHOLD_CURRENT_CONCURRENCY as THRESH_CUR_CONCUR, 
QUEUE_SIZE_CURRENT as Q_SZ_CUR  
FROM table(MON_GET_QUEUE_STATS('', 'SB', '', -1)) as QSTATS
    Sample output: 
    DOMAIN SUPERCLASS           SUBCLASS             THRESHOLD_NAME       THRESH_CUR_CONCUR Q_SZ_CUR   
------ -------------------- -------------------- -------------------- ----------------- -----------
SB     SYSDEFAULTUSERCLASS  SYSMEDIUMSUBCLASS    WLM_ADMISSION_QUEUE                  1           5
SB     SYSDEFAULTUSERCLASS  SYSCOMPLEXSUBCLASS   WLM_ADMISSION_QUEUE                  0           2
SB     SYSDEFAULTUSERCLASS  SYSLOADSUBCLASS      WLM_ADMISSION_QUEUE                  0           0
SB     SYSDEFAULTUSERCLASS  SYSDEFAULTSUBCLASS   WLM_ADMISSION_QUEUE                  0           0
SB     PAYROLL_SUPERCLASS   SYSDEFAULTSUBCLASS   WLM_ADMISSION_QUEUE                  2           9