DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_ACTIVITY_DETAILS table function - Return detailed information about a specific activity

Note: This table function has been deprecated and replaced by the MON_GET_ACTIVITY_DETAILS table function - Get complete activity details.

This function returns basic statistics of one or more service subclasses.

This function returns detailed information about a specific activity identified by its application handle, unit of work ID, and activity ID. This information includes details about any thresholds that the activity has violated.

Syntax


>>-WLM_GET_ACTIVITY_DETAILS--(--application_handle--,--uow_id--,-->

>--activity_id--,--dbpartitionnum--)---------------------------><

The schema is SYSPROC.

Table function parameters

application_handle: An input argument of type BIGINT that specifies a valid application handle. If the argument is null, no rows are returned from this function. If the argument is null, an SQL171N error is returned.
uow_id: An input argument of type INTEGER that specifies a valid unit of work identifier unique within the application. If the argument is null, no rows are returned from this function. If the argument is null, an SQL171N error is returned.
activity_id: An input argument of type INTEGER that specifies a valid activity ID unique within the unit of work. If the argument is null, no rows are returned from this function. If the argument is null, an SQL171N error is returned.
dbpartitionnum: An input argument of type INTEGER that specifies a valid partition number in the same instance as the currently connected database when calling this function. 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_ACTIVITY_DETAILS function.

Example

Detailed information about an individual activity can be obtained by using the WLM_GET_ACTIVITY_DETAILS table function. This table function returns activity information as name-value pairs for each partition. This example is restricted to showing only an eleven member subset of the name-value pairs for each partition for an activity identified by an application handle of 1, a unit of work ID of 1 and an activity ID of 5. For a complete list of name-value pairs, see Table 2 and Table 3.

  SELECT SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
         SUBSTR(NAME, 1, 20) AS NAME,
         SUBSTR(VALUE, 1, 30) AS VALUE
  FROM TABLE(WLM_GET_ACTIVITY_DETAILS(1, 1, 5, -2)) AS ACTDETAIL
  WHERE NAME IN ('APPLICATION_HANDLE',
               'COORD_PARTITION_NUM',
               'LOCAL_START_TIME',
               'UOW_ID',
               'ACTIVITY_ID',
               'PARENT_UOW_ID',
               'PARENT_ACTIVITY_ID',
               'ACTIVITY_TYPE',
               'NESTING_LEVEL',
               'INVOCATION_ID',
               'ROUTINE_ID')
  ORDER BY PART

The following example is a sample output from this query.

PART NAME                 VALUE
---- -------------------- ------------------------------
0    APPLICATION_HANDLE   1
0    COORD_PARTITION_NUM  0
0    LOCAL_START_TIME     2005-11-25-18.52.49.343000
0    UOW_ID               1
0    ACTIVITY_ID          5
0    PARENT_UOW_ID        1
0    PARENT_ACTIVITY_ID   3
0    ACTIVITY_TYPE        READ_DML
0    NESTING_LEVEL        0
0    INVOCATION_ID        1
0    ROUTINE_ID           0
1    APPLICATION_HANDLE   1
1    COORD_PARTITION_NUM  0
1    LOCAL_START_TIME     2005-11-25-18.52.49.598000
1    UOW_ID               1
1    ACTIVITY_ID          5
1    PARENT_UOW_ID
1    PARENT_ACTIVITY_ID
1    ACTIVITY_TYPE        READ_DML
1    NESTING_LEVEL        0
1    INVOCATION_ID        1
1    ROUTINE_ID           0

Usage note

An ACTIVITY_STATE of QUEUED means that the coordinator activity has made a RPC to the catalog partition to obtain threshold tickets and has not yet received a response. Seeing this state might indicate that the activity has been queued by WLM or, over short periods of time, might just indicate that the activity is in the process of obtaining its tickets. To obtain a more accurate picture of whether or not the activity is really being queued, one can determine which agent is working on the activity (using the WLM_GET_SERVICE_CLASS_AGENTS table function) and find out whether this agent's event_object at the catalog partition has a value of WLM_QUEUE.

Information returned

Table 1. Information returned for WLM_GET_ACTIVITY_DETAILS
Column Name	Data Type	Description
DBPARTITIONNUM	SMALLINT	Partition number from which this record was collected.
NAME	VARCHAR(256)	Element name. See Table 2 and Table 3 for possible values.
VALUE	VARCHAR(1024)	Element values. See Table 2 and Table 3 for possible values.

Table 2. Elements returned
Element Name	Description
ACTIVITY_ID	Unique activity identifier within an application.	activity_id - Activity ID monitor element
ACTIVITY_STATE	Possible values include: CANCEL_PENDING EXECUTING IDLE INITIALIZING QP_CANCEL_PENDING QP_QUEUED QUEUED TERMINATING UNKNOWN	activity_state - Activity state monitor element
ACTIVITY_TYPE	Possible values include: CALL DDL LOAD OTHER READ_DML WRITE_DML	activity_type - Activity type monitor element
APPLICATION_HANDLE	A system-wide unique ID for the application. On a single-partitioned database, this identifier consists of a 16 bit counter. On a multi-partitioned database, this identifier consists of the coordinating partition number concatenated with a 16 bit counter. In addition, this identifier will be the same on every partition where the application may make a secondary connection.	application_handle - Application handle monitor element
COORD_PARTITION_NUM	The coordinator partition of the activity.	coord_partition_num - Coordinator partition number monitor element
DATABASE_WORK_ACTION_SET_ID	If this activity has been mapped to a work action set that has been applied to the database, this column contains the ID of the work action set. This column contains 0 if the activity has not been mapped to a work action set that has been applied to the database.
DATABASE_WORK_CLASS_ID	If this activity has been mapped to a work action set that has been applied to the database, this column contains the ID of the work class of this activity. This column contains 0 if the activity has not been mapped to a work action set that has been applied to the database.
EFFECTIVE_ISOLATION	The effective isolation level for this activity.	effective_isolation - Effective isolation monitor element
EFFECTIVE_LOCK_TIMEOUT	The effective lock timeout value for this activity.	effective_lock_timeout - Effective lock timeout monitor element
EFFECTIVE_QUERY_DEGREE	The effective value of query degree for this activity.	effective_query_degree - Effective query degree monitor element
ENTRY_TIME	The time that this activity arrived into the system.	entry_time - Entry time monitor element
INVOCATION_ID	An identifier that distinguishes one invocation of a routine from others at the same nesting level within a unit of work. It is unique within a unit of work for a specific nesting level.	invocation_id - Invocation ID monitor element
LAST_REFERENCE_TIME	Every time a request occurs in this activity, this field is updated.	last_reference_time - Last reference time monitor element
LOCAL_START_TIME	The time that this activity began doing work on the partition. It is in local time. This field can be an empty string when an activity has entered the system but is in a queue and has not started executing.	local_start_time - Local start time monitor element
NESTING_LEVEL	This represents the nesting level of this activity. Nesting level is the depth to which this activity is nested within its top-most parent activity.	nesting_level - Nesting level monitor element
PACKAGE_NAME	If the activity is a SQL statement, this represents the name of its package.	package_name - Package name monitor element
PACKAGE_SCHEMA	If the activity is a SQL statement, this represents the schema name of its package.	package_schema - Package schema monitor element
PACKAGE_VERSION_ID	If the activity is a SQL statement, this represents the version of its package.	package_version_id - Package version monitor element
PARENT_ACTIVITY_ID	Unique activity identifier within a unit of work for the parent of the activity whose ID is ACTIVITY_ID. Returns an empty string if the activity has no parent activity.	parent_activity_id - Parent activity ID monitor element
PARENT_UOW_ID	Unique unit of work identifier within an application. Refers to the original unit of work this activity's parent activity started in. Returns an empty string if the activity has no parent activity or when at a remote partition.
QP_QUERY_ID	The query ID assigned to this activity by Query Patroller if the activity is a query. A query ID of 0 indicates that Query Patroller did not assign a query ID to this activity.	qp_query_id - Query patroller query ID monitor element
QUERY_COST_ESTIMATE	Estimated cost, in timerons, for a query, as determined by the SQL compiler.	query_cost_estimate - Query cost estimate monitor element
ROUTINE_ID	Routine unique identifier. Returns zero if the activity is not part of a routine.	routine_id - Routine ID monitor element
ROWS_FETCHED	This is the number of rows read from the table. This reports only those values for the database partition for which this record is recorded. In a partitioned database environment, these values may not reflect the correct totals for the whole activity. When the statement monitor switch is not turned on, this element is not collected and -1 is written instead.	rows_fetched - Rows fetched monitor element
ROWS_MODIFIED	This is the number of rows inserted, updated, or deleted. This reports only those values for the database partition for which this record is recorded. In a partitioned database environment, these values may not reflect the correct totals for the whole activity. When the statement monitor switch is not turned on, this element is not collected and -1 is written instead.	rows_modified - Rows modified monitor element
SECTION_NUMBER	If the activity is a SQL statement, this represents its section number.	section_number - Section number monitor element
SERVICE_CLASS_ID	Unique identifier of the service class to which this activity belongs.	service_class_id - Service class ID monitor element
SERVICE_CLASS_WORK_ACTION_SET_ID	If this activity has been mapped to a work action set that has been applied to a service class, this column contains the ID of the work action set. This column contains 0 if the activity has not been mapped to a work action set that has been applied to a service class.
SERVICE_CLASS_WORK_CLASS_ID	If this activity has been mapped to a work action set that has been applied to a service class, this column contains the ID of the work class of this activity. This column contains 0 if the activity has not been mapped to a work action set that has been applied to a service class.
STMT_PKG_CACHE_ID	Statement package cache identifier.	stmt_pkgcache_id - Statement package cache identifier monitor element
STMT_TEXT	If the activity is dynamic SQL or it is static SQL for which the statement text is available, this field contains the first 1024 characters of the statement text. It is an empty string otherwise.	stmt_text - SQL statement text monitor element
SYSTEM_CPU_TIME	The total system CPU time (in seconds and microseconds) used by the database manager agent process, the unit of work, or the statement. When either the statement monitor switch or the timestamp switch is not turned on, this element is not collected and -1 is written instead.	system_cpu_time - System CPU time monitor element
UOW_ID	Unique unit of work identifier within an application. Refers to the original unit of work this activity started in.	uow_id - Unit of work ID monitor element
USER_CPU_TIME	The total user CPU time (in seconds and microseconds) used by the database manager agent process, the unit of work, or the statement. When either the statement monitor switch or the timestamp switch is not turned on, this element is not collected and -1 is written instead.	user_cpu_time - User CPU time monitor element
UTILITY_ID	If the activity is a utility, this is its utility ID. Otherwise, this field is 0.	utility_id - Utility ID monitor element

Important: The WLM_GET_ACTIVITY_DETAILS table function shows only the thresholds that are currently being applied to an activity.

The following elements are returned only if the corresponding thresholds apply to the activity.

Table 3. Elements returned if applicable
Element Name	Description
ACTIVITYTOTALTIME_THRESHOLD_ID	The ID of the ACTIVITYTOTALTIME threshold that was applied to the activity.	activitytotaltime_threshold_id - Activity total time threshold ID monitor element
ACTIVITYTOTALTIME_THRESHOLD_VALUE	A timestamp that is computed by adding the ACTIVITYTOTALTIME threshold duration to the activity entry time. If the activity is still executing when this timestamp is reached, the threshold will be violated.	activitytotaltime_threshold_value - Activity total time threshold value monitor element
ACTIVITYTOTALTIME_THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the ACTIVITYTOTALTIME threshold. 'No' indicates that the activity has not yet violated the threshold.	activitytotaltime_threshold_violated - Activity total time threshold violated monitor element
CONCURRENTDBCOORDACTIVITIES_DB _THRESHOLD_ID	The ID of the CONCURRENTDBCOORDACTIVITIES_DB threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_DB _THRESHOLD_QUEUED	'Yes' indicates that the activity was queued by the CONCURRENTDBCOORDACTIVITIES_DB threshold. 'No' indicates that the activity was not queued.
CONCURRENTDBCOORDACTIVITIES_DB _THRESHOLD_VALUE	The upper bound of the CONCURRENTDBCOORDACTIVITIES_DB threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_DB _THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the CONCURRENTDBCOORDACTIVITIES_DB threshold. 'No' indicates that the activity has not yet violated the threshold.
CONCURRENTDBCOORDACTIVITIES_SUBCLASS _THRESHOLD_ID	The ID of the CONCURRENTDBCOORDACTIVITIES_SUBCLASS threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_SUBCLASS _THRESHOLD_QUEUED	'Yes' indicates that the activity was queued by the CONCURRENTDBCOORDACTIVITIES_SUBCLASS threshold. 'No' indicates that the activity was not queued.
CONCURRENTDBCOORDACTIVITIES_SUBCLASS _THRESHOLD_VALUE	The upper bound of the CONCURRENTDBCOORDACTIVITIES_SUBCLASS threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_SUBCLASS _THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the CONCURRENTDBCOORDACTIVITIES_SUBCLASS threshold. 'No' indicates that the activity has not yet violated the threshold.
CONCURRENTDBCOORDACTIVITIES_SUPERCLASS _THRESHOLD_ID	The ID of the CONCURRENTDBCOORDACTIVITIES _SUPERCLASS threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_SUPERCLASS _THRESHOLD_QUEUED	'Yes' indicates that the activity was queued by the CONCURRENTDBCOORDACTIVITIES _SUPERCLASS threshold. 'No' indicates that the activity was not queued.
CONCURRENTDBCOORDACTIVITIES_SUPERCLASS _THRESHOLD_VALUE	The upper bound of the CONCURRENTDBCOORDACTIVITIES _SUPERCLASS threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_SUPERCLASS _THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the CONCURRENTDBCOORDACTIVITIES _SUPERCLASS threshold. 'No' indicates that the activity has not yet violated the threshold.
CONCURRENTDBCOORDACTIVITIES_WORK_ACTION_SET _THRESHOLD_ID	The ID of the CONCURRENTDBCOORDACTIVITIES_WORK _ACTION_SET threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_WORK_ACTION_SET _THRESHOLD_QUEUED	'Yes' indicates that the activity was queued by the CONCURRENTDBCOORDACTIVITIES _WORK_ACTION_SET threshold. 'No' indicates that the activity was not queued.
CONCURRENTDBCOORDACTIVITIES_WORK_ACTION_SET _THRESHOLD_VALUE	The upper bound of the CONCURRENTDBCOORDACTIVITIES_WORK _ACTION_SET threshold that was applied to the activity.
CONCURRENTDBCOORDACTIVITIES_WORK_ACTION_SET _THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the CONCURRENTDBCOORDACTIVITIES_WORK _ACTION_SET threshold. 'No' indicates that the activity has not yet violated the threshold.
CONCURRENTWORKLOADACTIVITIES _THRESHOLD_ID	The ID of the CONCURRENTWORKLOADACTIVITIES threshold that was applied to the activity.
CONCURRENTWORKLOADACTIVITIES _THRESHOLD_VALUE	The upper bound of the CONCURRENTWORKLOADACTIVITIES threshold that was applied to the activity.
CONCURRENTWORKLOADACTIVITIES _THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the CONCURRENTWORKLOADACTIVITIES threshold. 'No' indicates that the activity has not yet violated the threshold.
ESTIMATEDSQLCOST_THRESHOLD_ID	The ID of the ESTIMATEDSQLCOST threshold that was applied to the activity.	estimatedsqlcost_threshold_id - Estimated SQL cost threshold ID monitor element
ESTIMATEDSQLCOST_THRESHOLD_VALUE	The upper bound of the ESTIMATEDSQLCOST threshold that was applied to the activity.	estimatedsqlcost_threshold_value - Estimated SQL cost threshold value monitor element
ESTIMATEDSQLCOST_THRESHOLD_VIOLATED	'1' indicates that the activity violated the ESTIMATEDSQLCOST threshold. '0' indicates that the activity has not yet violated the threshold.	estimatedsqlcost_threshold_violated - Estimated SQL cost threshold violated monitor element
SQLROWSRETURNED_THRESHOLD_ID	The ID of the SQLROWSRETURNED threshold that was applied to the activity	sqlrowsreturned_threshold_id - SQL rows read returned threshold ID monitor element
SQLROWSRETURNED_THRESHOLD_VALUE	The upper bound of the SQLROWSRETURNED threshold that was applied to the activity.	sqlrowsreturned_threshold_value - SQL rows read returned threshold value monitor element
SQLROWSRETURNED_THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the SQLROWSRETURNED threshold. 'No' indicates that the activity has not yet violated the threshold.	sqlrowsreturned_threshold_violated - SQL rows read returned threshold violated monitor element
SQLTEMPSPACE_THRESHOLD_ID	The ID of the SQLTEMPSPACE threshold that was applied to the activity.	sqltempspace_threshold_id - SQL temporary space threshold ID monitor element
SQLTEMPSPACE_THRESHOLD_VALUE	The upper bound of the SQLTEMPSPACE threshold that was applied to the activity.	sqltempspace_threshold_value - SQL temporary space threshold value monitor element
SQLTEMPSPACE_THRESHOLD_VIOLATED	'Yes' indicates that the activity violated the SQLTEMPSPACE threshold. 'No' indicates that the activity has not yet violated the threshold.	sqltempspace_threshold_violated - SQL temporary space threshold violated monitor element