DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_SERVICE_CLASS_AGENTS table function - List agents running in a service class

The WLM_GET_SERVICE_CLASS_AGENTS 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.

Note: This table function has been deprecated and replaced by the WLM_GET_SERVICE_CLASS_AGENTS_V97 table function - list agents running in a service class.

Syntax


>>-WLM_GET_SERVICE_CLASS_AGENTS--(--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 that match the values of the other parameters .
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 that match the values of the other parameters.
application_handle: An input argument of type BIGINT that specifies the application handle for which agent information is returned. If the argument is null, data is retrieved for all applications in the database that match the values of the other parameters. If you specify 0, only system entities are returned.
dbpartitionnum: An input argument of type INTEGER that specifies a valid 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 function.

Example

The following query returns a list of agents that are associated with application handle 1 for all database partitions. The application handle could have been determined by using the LIST APPLICATIONS command or the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES 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(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 coordinator agent reports that the request is a fetch request.

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
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: db2agent, which indicates that the type of entity is an agent db2fmp (`pid`), which indicates that the entity is a fenced mode process, where `pid` is the process ID of the fenced mode process The name of a 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 possible values are as follows: COORDINATOR OTHER PDBSUBAGENT SMPSUBAGENT If the agent type is COORDINATOR, the agent ID might change in concentrator environments.
SMP_COORDINATOR	INTEGER	Indication of whether the agent is an SMP coordinator. If yes, the value is 1; if no, the value is 0.
AGENT_SUBTYPE	VARCHAR(32)	Agent subtype. The possible values are as follows: DSS OTHER RPC SMP
AGENT_STATE	VARCHAR(32)	Agent state. The possible values are as follows: ACTIVE ASSOCIATED
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 to distinguish between one request that is taking a long time and multiple requests; for example, 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, NULL
REQUEST_TYPE (continued)	VARCHAR(32)	For subagents with an AGENT_SUBTYPE of RPC ABP CATALOG INTERNAL REORG RUNSTATS WLM For subagents with an AGENT_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

Note: The possible combinations of EVENT_STATE, EVENT_TYPE, and EVENT_OBJECT column values are listed in the following table.

Table 2. Possible combinations for EVENT_STATE, EVENT_TYPE, and EVENT_OBJECT column values
Event description	EVENT_STATE value	EVENT_TYPE value	EVENT_OBJECT value
Acquire lock	IDLE	ACQUIRE	LOCK
Escalate lock	EXECUTING	PROCESS	LOCK_ESCALATION
Process request	EXECUTING	PROCESS	REQUEST
Wait for a new request	IDLE	WAIT	REQUEST
Wait for a request to be processed at a remote partition	IDLE	WAIT	REMOTE_REQUEST
Wait on a Query Patroller queue	IDLE	WAIT	QP_QUEUE
Wait on a WLM threshold queue	IDLE	WAIT	WLM_QUEUE
Process a routine	EXECUTING	PROCESS	ROUTINE
Recreate an index	EXECUTING	PROCESS	INDEX_RECREATE
Build compression dictionary	EXECUTING	PROCESS	COMP_DICT_BLD
Implicit rebind	EXECUTING	PROCESS	IMPLICIT_REBIND