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
- EXECUTE privilege on the routine
- DATAACCESS authority
- DBADM authority
- SQLADM authority
- WLMADM authority
Default PUBLIC privilege
None
Syntax
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
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:
|
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:
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:
|
AGENT_STATE | VARCHAR (32) | Indication of whether an agent is associated
or active. The possible values are:
|
EVENT_TYPE | VARCHAR (32) | Type of event last processed by this agent.
The possible values are as follows:
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:
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:
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:
|
REQUEST_TYPE (continued) | VARCHAR (32) |
|
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.
|
APPLICATION_NAME | VARCHAR (128) | |
APPLICATION_ID | VARCHAR (128) | |
CLIENT_PID | BIGINT | |
SESSION_AUTH_ID | VARCHAR (128) | |
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. |
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
- 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:
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. TheAPPHANDLE 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
AGENTTYPE
column with a value ofCOORDINATOR
has a value ofFETCH
for theREQTYPE
column (which indicates the main or initial request type). This means that the type of request is a fetch request for the coordinator agent. - 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:
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 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.
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.
- 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
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.