MON_GET_AGENT table function - List agents, fenced mode processes, and system entities for the database

The MON_GET_AGENT function returns a list of all agents, fenced mode processes (db2fmp processes), and system entities for the database. The list can be filtered to show information for a specified member, service class, or application.

The system entities are non-agent threads and processes, such as page cleaners and prefetchers.

Refer to Table 1 for a complete list of information that can be returned.

Authorization

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 diagramMON_GET_AGENT(service_superclass_name ,service_subclass_name,application_handle ,member)

The schema is SYSPROC.

Routine parameters

service_superclass_name
An input argument of type VARCHAR(128) that specifies the name of a service superclass in the currently connected database. If the argument is null or an empty string, data is retrieved for all the superclasses in the database.
service_subclass_name
An input argument of type VARCHAR(128) that refers to a specific subclass within a superclass. If the argument is null or an empty string, data is retrieved for all the subclasses in the database.
application_handle
An input argument of type BIGINT that specifies the application handle for which agent information is to be returned. If the argument is null, data is retrieved for all applications in the database. An application handle of 0 returns the system entities only.
member
An input argument of type INTEGER that specifies the member number in the same instance as the currently connected database. Specify -1 for the current database member, or -2 for all active database members. If a null value is specified, -1 is set implicitly.

Information returned

Table 1. Information returned by the MON_GET_AGENT routine
Column name Data type Description
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
APPLICATION_HANDLE BIGINT application_handle - Application handle monitor element
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
ENTITY VARCHAR (32) One of the following values:
  • If the type of entity is an agent, the value is db2agent.
  • If the type of entity is a fenced mode process, the value is db2fmp (pid) where pid is the process ID of the fenced mode process.
  • Otherwise, the value is the name of the system entity.
WORKLOAD_NAME VARCHAR (128) workload_name - Workload name monitor element
WORKLOAD_OCCURRENCE_ID INTEGER workload_occurrence_id - Workload occurrence identifier monitor element
UOW_ID INTEGER uow_id - Unit of work ID monitor element
ACTIVITY_ID INTEGER activity_id - Activity ID monitor element
PARENT_UOW_ID INTEGER parent_uow_id - Parent unit of work ID monitor element
PARENT_ACTIVITY_ID INTEGER parent_activity_id - Parent activity ID monitor element
AGENT_TID BIGINT agent_tid - Agent thread ID monitor element
AGENT_TYPE VARCHAR (32) Agent type. The agent types are as follows:
  • COORDINATOR
  • OTHER
  • PDBSUBAGENT
  • SMPSUBAGENT
If the value is COORDINATOR, the agent ID might change in concentrator environments.

The SMPSUBAGENT type is returned for agents that are processing column-organized tables.
SMP_COORDINATOR INTEGER Indication of whether the agent is an SMP coordinator: 1 for yes and 0 for no.
AGENT_SUBTYPE VARCHAR (32) Agent subtype. The possible subtypes are as follows:
  • DSS
  • OTHER
  • RPC
  • SMP
  • COLUMNAR
AGENT_STATE VARCHAR (32) Indication of whether an agent is associated or active. The possible values are:
  • ASSOCIATED
  • ACTIVE
EVENT_TYPE VARCHAR (32) Type of event last processed by this agent. The possible values are as follows:
  • ACQUIRE
  • PROCESS
  • WAIT

See Table 2 for more information about possible values for this column.
EVENT_OBJECT VARCHAR (32) Object of the event last processed by this agent. The possible values are as follows:
  • COMPRESSION_DICTIONARY_BUILD
  • IMPLICIT_REBIND
  • INDEX_RECREATE
  • LATCH
  • LOCK
  • LOCK_ESCALATION
  • QP_QUEUE
  • REMOTE_REQUEST
  • REQUEST
  • ROUTINE
  • WLM_QUEUE
  • TQ_SEND
  • TQ_RECEIVE

See Table 2 for more information about possible values for this column.
EVENT_STATE VARCHAR (32) State of the event last processed by this agent. The possible values are as follows:
  • EXECUTING
  • IDLE

See Table 2 for more information about possible values for this column.
REQUEST_ID VARCHAR (64) Request ID. This value is unique only in combination with the value of application_handle. You can use this combination to distinguish between one request that is taking a long time and multiple requests; for example, to distinguish between one long fetch and multiple fetches.
REQUEST_TYPE VARCHAR (32) Type of request. The possible values are as follows:
  • For coordinator agents:
    • CLOSE
    • COMMIT
    • COMPILE
    • DESCRIBE
    • EXCSQLSET
    • EXECIMMD
    • EXECUTE
    • FETCH
    • INTERNAL number, where number is the value of the internal constant
    • OPEN
    • PREPARE
    • REBIND
    • REDISTRIBUTE
    • REORG
    • ROLLBACK
    • RUNSTATS
  • For subagents with an AGENT_SUBTYPE column value of DSS, SMP, or COLUMNAR:
    • If the subsection number is nonzero, the subsection number in the form SUBSECTION:subsection_number; otherwise, NULL.
REQUEST_TYPE (continued) VARCHAR (32)
  • For subagents with an AGENT_SUBTYPE of RPC:
    • ABP
    • CATALOG
    • INTERNAL
    • REORG
    • RUNSTATS
    • WLM
  • For subagents with a SUBTYPE of OTHER:
    • ABP
    • APP_RBSVPT
    • APP_RELSVPT
    • BACKUP
    • CLOSE
    • EXTERNAL_RBSVPT
    • EVMON
    • FORCE
    • FORCE_ALL
    • INTERNAL number, where number is the value of the internal constant
    • INTERRUPT
    • NOOP (if there is no request)
    • QP
    • REDISTRIBUTE
    • STMT_RBSVPT
    • STOP_USING
    • UPDATE_DBM_CFG
    • WLM
NESTING_LEVEL INTEGER nesting_level - Nesting level monitor element
INVOCATION_ID INTEGER invocation_id - Invocation ID monitor element
ROUTINE_ID INTEGER routine_id - Routine ID monitor element
EVENT_OBJECT_NAME VARCHAR (1024) Event object name.
  • If the value of EVENT_OBJECT is LOCK, the value of this column is the name of the lock that the agent is waiting on.
  • If the value of EVENT_OBJECT is WLM_QUEUE, the value of the column is the name of the WLM threshold that the agent is queued on.
  • If the value of EVENT_OBJECT is TQ_SEND or TQ_RECEIVE the value of the column represents the table queue ID number.
  • Otherwise, the value is NULL.
APPLICATION_NAME VARCHAR (128)

appl_name - Application name
APPLICATION_ID VARCHAR (128)

appl_id - Application ID
CLIENT_PID BIGINT

client_pid - Client process ID

SESSION_AUTH_ID VARCHAR (128)

session_auth_id - Session authorization ID

REQUEST_START_TIME TIMESTAMP

Time that the agent started processing the request on which it is currently working
AGENT_STATE_LAST_UPDATE_TIME TIMESTAMP

The last time that the event, being processed by the agent, was changed. The event currently processed by the agent is identified by the EVENT_TYPE, EVENT_OBJECT, and EVENT_STATE columns.
EXECUTABLE_ID VARCHAR (32) FOR BIT DATA executable_id - Executable ID monitor element
MEMBER SMALLINT member - Database member monitor element
UTILITY_INVOCATION_ID VARCHAR (32) FOR BIT DATA utility_invocation_id - Utility invocation ID
IS_SYSTEM_APPL SMALLINT is_system_appl - Is System Application monitor element
SUBSECTION_NUMBER BIGINT Displays subsection ID if an agent (any agent type) is processing a subsection; otherwise NULL.
EVENT_OBJECT_DETAILS VARCHAR (1024) Displays detailed information about the event wait object. Basic information about the event wait object is displayed in the EVENT_OBJECT_NAME field.
Note: The possible combinations of EVENT_STATE, EVENT_TYPE, EVENT_OBJECT, and EVENT_OBJECT_NAME column values are listed in the following table.
Table 2. Possible combinations for EVENT_STATE, EVENT_TYPE, EVENT_OBJECT, and EVENT_OBJECT_NAME column values
Event description EVENT_STATE value EVENT_TYPE value EVENT_OBJECT value EVENT_OBJECT_NAME value EVENT_OBJECT_DETAILS value
Acquire lock IDLE ACQUIRE LOCK Lock name NULL
Escalate lock EXECUTING PROCESS LOCK_ESCALATION NULL NULL
Process request EXECUTING PROCESS REQUEST NULL NULL
Wait for a new request IDLE WAIT REQUEST NULL NULL
Wait for a request to be processed at a remote member IDLE WAIT REMOTE_REQUEST NULL NULL
Wait on a WLM threshold queue IDLE WAIT WLM_QUEUE Threshold name NULL
Process a routine EXECUTING PROCESS ROUTINE NULL NULL
Re-create an index EXECUTING PROCESS INDEX_RECREATE NULL NULL
Build compression dictionary EXECUTING PROCESS COMP_DICT_BLD NULL NULL
Implicit rebind EXECUTING PROCESS IMPLICIT_REBIND NULL NULL
Sending data on table queue IDLE WAIT TQ_SEND Table queue number Member number that is waiting for a table queue, or -1 for any member.
Receiving data on table queue IDLE WAIT TQ_RECEIVE Table queue number Member number that is waiting for a table queue, or -1 for any member.
Latch wait IDLE WAIT LATCH Latch name NULL

Usage notes

The parameters are, in effect, ANDed together. That is, if you specify conflicting input parameters, such as a service superclass SUP_A and a subclass SUB_B such that SUB_B is not a subclass of SUP_A, no rows are returned.

Examples

  1. The following query returns a list of agents that are associated with application handle 1 for all database members. You can determine the application handle by using the LIST APPLICATIONS command or the MON_GET_CONNECTION table function.
      SELECT SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APPHANDLE,
    SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
    SUBSTR(CHAR(AGENT_TID),1,9) AS AGENT_TID,
    SUBSTR(AGENT_TYPE,1,11) AS AGENTTYPE,
    SUBSTR(AGENT_STATE,1,10) AS AGENTSTATE,
    SUBSTR(REQUEST_TYPE,1,12) AS REQTYPE,
    SUBSTR(CHAR(UOW_ID),1,6) AS UOW_ID,
    SUBSTR(CHAR(ACTIVITY_ID),1,6) AS ACT_ID
  FROM TABLE(MON_GET_AGENT(CAST(NULL AS VARCHAR(128)), 
    CAST(NULL AS VARCHAR(128)), 1, -2)) AS SCDETAILS
  ORDER BY APPHANDLE, MEMB, AGENT_TID
    Sample output is as follows: 
    APPHANDLE MEMB AGENT_TID AGENTTYPE   AGENTSTATE REQTYPE        UOW_ID ACT_ID
--------- ---- --------- ----------- ---------- -------------- ------ ------
1         0    3         COORDINATOR ACTIVE     FETCH          1      5
1         0    4         SUBAGENT    ACTIVE     SUBSECTION:1   1      5
1         1    2         SUBAGENT    ACTIVE     SUBSECTION:2   1      5
    The output shows a coordinator agent and a subagent on member 0 and a subagent on member 1 operating on behalf of an activity with UOW ID 1 and activity ID 5. The AGENTTYPE column with a value of COORDINATOR has a value of FETCH for the REQTYPE column (which indicates the main or initial request type). This means that the type of request is a fetch request for the coordinator agent.
  2. The following query determines which lock an agent is waiting on:
     select event_object, event_type, event_state, varchar(event_object_name, 30) 
   as event_object_name 
   from table(MON_GET_AGENT('','',cast(NULL as bigint), -1)) as t
    Sample output is as follows: 
    EVENT_OBJECT    EVENT_TYPE        EVENT_STATE         EVENT_OBJECT_NAME         
--------------- ----------------- ------------------- --------------------------
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         WAIT              IDLE                -                         
LOCK            ACQUIRE           IDLE                02000500000000000000000054
ROUTINE         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         

  21 record(s) selected.
    Using the same query at a later time shows that the WLM threshold has queued an agent:
    EVENT_OBJECT    EVENT_TYPE        EVENT_STATE         EVENT_OBJECT_NAME         
--------------- ----------------- ------------------- --------------------------
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
WLM_QUEUE       WAIT              IDLE                MYCONCDBCOORDTH           
ROUTINE         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -                         
REQUEST         PROCESS           EXECUTING           -

  21 record(s) selected.
  3. In this example, an application is running in a partitioned database environment with BLU Acceleration. You want to gather some information on the state of various agents involved. You can use the snapshot interface to determine items such as:
    • Which agents are working on which subsection
    • The number of rows that are sent and received between subsections on table queues
    • The number or rows that are read and written from disk
    However, you cannot use the snapshot interface determine the state of each agent that is working on a particular subsection. Some agents might be waiting on different table queues and partitions.
    The following query can be used to gather information on the state of various agents involved. This query assumes that you know the application number (for example, 349, in this case):
       SELECT SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APPHANDLE, 
          SUBSTR(CHAR(MEMBER),1,4) AS MEMB, 
          SUBSTR(CHAR(AGENT_TID),1,20) AS AGENT_TID, 
          SUBSTR(AGENT_SUBTYPE,1,10) AS AGENT_SUBTYPE, 
          SUBSTR(AGENT_TYPE,1,11) AS AGENTTYPE, 
          SUBSTR(AGENT_STATE,1,10) AS AGENTSTATE, 
          SUBSTR(EVENT_STATE,1,10) AS EVENT_STATE, 
          SUBSTR(EVENT_TYPE,1,10) AS EVENT_TYPE, 
          SUBSTR(EVENT_OBJECT,1,10) AS EVENT_OBJECT, 
          SUBSTR(REQUEST_TYPE,1,12) AS REQTYPE, 
          SUBSTR(EVENT_OBJECT_NAME,1,10) AS TQ_ID, 
          SUBSTR(CHAR(SUBSECTION_NUMBER),1,4) AS SUB_ID, 
          SUBSTR(EVENT_OBJECT_DETAILS,1,10) AS TQ_WAIT_MEM 
      FROM TABLE(MON_GET_AGENT(CAST(NULL AS VARCHAR(128)), CAST(NULL AS VARCHAR(128)), 349, -2)) 
          AS SCDETAILS 
          ORDER BY APPHANDLE, MEMB, AGENT_TID
    Note: EVENT_OBJECT_NAME is displayed as TQ_ID, SUBSECTION_NUMBER is displayed as SUB_ID, and EVENT_OBJECT_DETAILS is displayed as TQ_WAIT_MEM. TQ_WAIT_MEM represents the member that you are waiting for, where -1 means 'any'.

    The following is an example of output from this query. 

    APPHANDLE MEMB AGENT_TID            AGENT_SUBTYPE AGENTTYPE   AGENTSTATE ...
--------- ---- -------------------- ------------- ----------- ---------- ...
349       0    250                  -             COORDINATOR ACTIVE     ...
349       0    338                  COLUMNAR      SMPSUBAGENT ACTIVE     ...
349       0    343                  COLUMNAR      SMPSUBAGENT ACTIVE     ...
349       0    335                  SMP           SMPSUBAGENT ACTIVE     ...
349       0    336                  SMP           SMPSUBAGENT ACTIVE     ...
349       1    277                  COLUMNAR      SMPSUBAGENT ACTIVE     ...
349       2    278                  COLUMNAR      SMPSUBAGENT ACTIVE     ...
    Output for query (continued).
    ...EVENT_STATE EVENT_TYPE EVENT_OBJECT REQTYPE      TQ_ID      SUB_ID TQ_WAIT_MEM    
...----------- ---------- ------------ ------------ ---------- ------ -----------    
...IDLE        WAIT       TQ_RECEIVE   OPEN         1          0      -1             
...IDLE        WAIT       TQ_SEND      SUBSECTION:2 3          2      -1             
...IDLE        WAIT       TQ_RECEIVE   -            2          0      -1             
...IDLE        WAIT       REQUEST      -            -          0      -              
...EXECUTING   PROCESS    REQUEST      -            -          0      -              
...IDLE        WAIT       TQ_SEND      SUBSECTION:2 3          2      -1             
...IDLE        WAIT       TQ_SEND      SUBSECTION:2 3          2      -1

    In this example, an agent with an AGENT_TID value of 338 represents partition 0; a value of 277 represents partition 1; a value of 278 represents partition 2. All three of these agents are waiting to send to TQ_ID 3 on all partitions. Sending to all partitions is known as a broadcast. In this case, this broadcast occurs in subsection ID 2. The agent with an ID of 250 on partition 0 is processing subsection 0 and is waiting to receive on TQ_ID 1 from any partition. The agent with an ID of 343 is also waiting to receive on subsection 0 but on TQ_ID 2 from any partition. The agent with IDs 335 and 336 are not waiting on any TQ.