DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities

The WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES function returns the list of all activities that were submitted by a specified application on a specified partition and have not yet been completed.

Note: This table function has been deprecated and replaced by the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 table function - return a list of activities.

Syntax


>>-WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES--(--application_handle--,-->

>--dbpartitionnum--)-------------------------------------------><

The schema is SYSPROC.

Table function parameters

application_handle: An input argument of type BIGINT that specifies an application handle for which a list of activities is to be returned. If the argument is null, the data is retrieved for all the applications in the database.
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 the null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES function.

Example

After you identify the application handle, you can look up all the activities currently running in this application. For example, suppose that an administrator wants to list the activities of an application whose application handle, determined by using the LIST APPLICATIONS command, is 1. The administrator runs the following query:

SELECT SUBSTR(CHAR(COORD_PARTITION_NUM),1,5) AS COORD,
       SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
       SUBSTR(CHAR(UOW_ID),1,5) AS UOWID,
       SUBSTR(CHAR(ACTIVITY_ID),1,5) AS ACTID,
       SUBSTR(CHAR(PARENT_UOW_ID),1,8) AS PARUOWID,
       SUBSTR(CHAR(PARENT_ACTIVITY_ID),1,8) AS PARACTID,
              ACTIVITY_TYPE AS ACTTYPE,
       SUBSTR(CHAR(NESTING_LEVEL),1,7) AS NESTING
  FROM TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(1, -2)) AS WLOACTS
  ORDER BY PART, UOWID, ACTID

Sample output from the query is as follows:

COORD PART UOWID ACTID PARUOWID PARACTID ACTTYPE  NESTING
----- ---- ----- ----- -------- -------- -------- -------
0     0    2     3     -        -        CALL     0
0     0    2     5     2        3        READ_DML 1

Information returned

Table 1. Information returned by WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
Column name	Data type	Description
APPLICATION_HANDLE	BIGINT	application_handle - Application handle monitor element
DBPARTITIONNUM	SMALLINT	Partition number from which this record was collected.
COORD_PARTITION_NUM	SMALLINT	coord_partition_num - Coordinator partition number monitor element
LOCAL_START_TIME	TIMESTAMP	local_start_time - Local start time 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
ACTIVITY_STATE	VARCHAR(32)	activity_state - Activity state monitor element
ACTIVITY_STATE (continued)	VARCHAR(32)	Activity state. Possible values are as follows: `QP_CANCEL_PENDING` This state is the same as the CANCEL_PENDING state except that the activity was cancelled by Query Patroller rather than by the WLM_CANCEL_ACTIVITY procedure. `QP_QUEUED` The activity is queued by Query Patroller. `QUEUED` The activity is queued by a workload management queuing threshold. In a partitioned database environment, this state might mean that the coordinator agent has made an RPC to the catalog partition to obtain threshold tickets and has not yet received a response. This state might indicate that the activity has been queued by a workload management queuing threshold or, if not much time has elapsed, can indicate that the activity is in the process of obtaining its tickets. To obtain a more accurate picture of whether the activity is being queued, determine what agent is working on the activity, and find out whether the value of the EVENT_OBJECT of the agent at the catalog partition is WLM_QUEUE. `TERMINATING` The activity has completed running and is being removed from the system.
ACTIVITY_TYPE	VARCHAR(32)	Activity type. Possible values are as follows: `CALL` `DDL` `LOAD` `OTHER` `READ_DML` `WRITE_DML` Refer to Identify types of work with work classes for a description of the different types of SQL statements that are associated with each activity type.
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
UTILITY_ID	INTEGER	utility_id - Utility ID monitor element
SERVICE_CLASS_ID	INTEGER	service_class_id - Service class ID monitor element
DATABASE_WORK_ACTION_SET_ID	INTEGER	One of the following values: If this activity has been categorized into a work class of database scope, the value is the ID of the work class set of which this work class is a member. If this activity has not been categorized into a work class of database scope, the value is null.
DATABASE_WORK_CLASS_ID	INTEGER	One of the following values: If this activity has been categorized into a work class of database scope, the value is the ID of the work class. If this activity has not been categorized into a work class of database scope, the value is null.
SERVICE_CLASS_WORK_ACTION_SET_ID	INTEGER	One of the following values: If this activity has been categorized into a work class of service class scope, the value is the ID of the work action set associated with the work class set to which the work class belongs. If this activity has not been categorized into a work class of service class scope, the value is null.
SERVICE_CLASS_WORK_CLASS_ID	INTEGER	One of the following values: If this activity has been categorized into a work class of service class scope, the value is the ID of the work class assigned to this activity. If this activity has not been categorized into a work class of service class scope, the value is null.