DB2 10.5 for Linux, UNIX, and Windows

MON_GET_RTS_RQST table function - Retrieve information about real-time statistics requests

The MON_GET_RTS_RQST table function returns information about all real-time statistics requests that are pending in the system, and the set of requests that are currently being processed by the real time statistics daemon (such as on the real-time statistics processing queue).

Syntax

Read syntax diagramSkip visual syntax diagram
>>-MON_GET_RTS_RQST--()----------------------------------------><

The schema is SYSPROC.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority

Default PUBLIC privilege

None

Example

Display all pending and currently queued or executing real-time statistics requests.

SELECT MEMBER,
       QUEUE_POSITION,
       REQUEST_STATUS,
       REQUEST_TYPE,
       OBJECT_TYPE,
       VARCHAR(OBJECT_SCHEMA, 10) AS SCHEMA,
       VARCHAR(OBJECT_NAME, 10) AS NAME
FROM TABLE(MON_GET_RTS_RQST()) AS T
ORDER BY MEMBER, QUEUE_POSITION ASC

The following is an example of output from this query.

MEMBER QUEUE_POSITION REQUEST_STATUS REQUEST_TYPE    OBJECT_TYPE SCHEMA    NAME
------ -------------- -------------- --------------- ----------- --------- -------
     0              1 EXECUTING      COLLECT_STATS   TABLE       TEST      EMPLOYEE
     0              2 QUEUED         COLLECT_STATS   TABLE       TEST      T1
     0              3 QUEUED         WRITE_STATS     TABLE       TEST      T3
     0              - PENDING        WRITE_STATS     TABLE       TEST      BLAH
     1              - PENDING        COLLECT_STATS   TABLE       TEST      DEPT
     1              - PENDING        WRITE_STATS     TABLE       TEST      SALES
     2              - PENDING        WRITE_STATS     TABLE       TEST      SALARY

7 record(s) selected.

Usage notes

The MON_GET_RTS_RQST table function returns information about all real-time statistics requests that are pending on all members. The queue for processing real-time statistics requests exists only on a single member.

The MON_GET_RTS_RQST table function does not report any real-time statistics requests if real-time statistics collection is not enabled.

Information returned

Table 1. Information returned for MON_GET_RTS_RQST
Column Name Data Type Description
OBJECT_TYPE VARCHAR(8) Type of object. One of:
  • TABLE
  • VIEW
OBJECT_SCHEMA VARCHAR(128) object_schema - Object schema monitor element
OBJECT_NAME VARCHAR(128) object_name - Object name monitor element
MEMBER SMALLINT member - Database member monitor element
REQUEST_TYPE VARCHAR(14) One of:

WRITE_STATS - Request is to write already collected statistics to the catalogs.

COLLECT_STATS - Request is to perform full statistics collection.
REQUEST_STATUS VARCHAR(10) One of:

PENDING - Request is waiting to be picked up the real-time statistics daemon

QUEUED - Request has been gathered by the real-time statistics daemon and is awaiting processing

EXECUTING - Request is currently being processed by the real-time statistics daemon
REQUEST_TIME TIMESTAMP Time that request was submitted.
QUEUE_POSITION INTEGER If REQUEST_STATUS is QUEUED, position of the request in the real-time statistics daemon processing queue.
QUEUE_ENTRY_TIME TIMESTAMP If REQUEST_STATUS is QUEUED, time that the request was added to the real-times statistics daemon processing queue.
EXECUTION_START_TIME TIMESTAMP If REQUEST_STATUS is EXECUTION, time that the request began execution. NULL otherwise.