MON_GET_QUEUE_STATS table function - Return threshold queue statistics

The MON_GET_QUEUE_STATS function returns, for all active members, basic statistics for one or all threshold 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.
If this argument is null or an empty string, statistics are retrieved for all queues that meet the other criteria.

The specified value corresponds to the value in the THRESHOLDPREDICATE column of the SYSCAT.THRESHOLDS view.

domain
An input argument of type VARCHAR(18) that specifies a threshold 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.

The specified value corresponds to the value in the DOMAIN column of the SYSCAT.THRESHOLDS view.

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 this argument is null or an empty string, statistics are retrieved for all thresholds that satisfy the other criteria.
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 the argument is null or -1, statistics are retrieved for all thresholds that satisfy the other criteria.

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.

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