DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_SERVICE_CLASS_AGENTS_V97 table function - list agents running in a service class

The WLM_GET_SERVICE_CLASS_AGENTS_V97 function returns the list of agents, fenced mode processes (db2fmp processes), and system entities on a specified partition that are running in a specified service class or on behalf of a specified application. The system entities are non-agent threads and processes, such as page cleaners and prefetchers.

Syntax


>>-WLM_GET_SERVICE_CLASS_AGENTS_V97--(--service_superclass_name--,-->

>--service_subclass_name--,--application_handle--,--dbpartitionnum--)-><

The schema is SYSPROC.

Table function 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.
dbpartitionnum: An input argument of type INTEGER that specifies the partition number in the same instance as the currently connected database. Specify -1 for the current database partition, or -2 for all database partitions. If a null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the WLM_GET_SERVICE_CLASS_AGENTS_V97 function.

Example

Example 1

The following query returns a list of agents that are associated with application handle 1 for all database partitions. You can determine the application handle by using the LIST APPLICATIONS command or the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES_V97 table function.

  SELECT SUBSTR(CHAR(APPLICATION_HANDLE),1,7) AS APPHANDLE,
    SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
    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(WLM_GET_SERVICE_CLASS_AGENTS_V97(CAST(NULL AS VARCHAR(128)), 
    CAST(NULL AS VARCHAR(128)), 1, -2)) AS SCDETAILS
  ORDER BY APPHANDLE, PART, AGENT_TID

Sample output is as follows:

APPHANDLE PART 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 partition 0 and a subagent on partition 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.

Example 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(wlm_get_service_class_agents_v97('','',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.

Usage note

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.

Information returned

Table 1. Information returned by WLM_GET_SERVICE_CLASS_AGENTS_V97
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	Partition number from which this record was collected.
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	Thread ID of the agent or system entity. If this ID is unavailable, the value of the column is null.
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.
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
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 LOCK LOCK_ESCALATION QP_QUEUE REMOTE_REQUEST REQUEST ROUTINE WLM_QUEUE 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 of DSS or SMP: If the subsection number is nonzero, the subsection number in the form SUBSECTION:`subsection number`; otherwise, returns 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. Otherwise, the value is NULL. See Table 2 for more information about possible values for this column.
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

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
Acquire lock	IDLE	ACQUIRE	LOCK	Lock name
Escalate lock	EXECUTING	PROCESS	LOCK_ESCALATION	NULL
Process request	EXECUTING	PROCESS	REQUEST	NULL
Wait for a new request	IDLE	WAIT	REQUEST	NULL
Wait for a request to be processed at a remote partition	IDLE	WAIT	REMOTE_REQUEST	NULL
Wait on a Query Patroller queue	IDLE	WAIT	QP_QUEUE	NULL
Wait on a WLM threshold queue	IDLE	WAIT	WLM_QUEUE	Threshold name
Process a routine	EXECUTING	PROCESS	ROUTINE	NULL
Recreate an index	EXECUTING	PROCESS	INDEX_RECREATE	NULL
Build compression dictionary	EXECUTING	PROCESS	COMP_DICT_BLD	NULL
Implicit rebind	EXECUTING	PROCESS	IMPLICIT_REBIND	NULL