WLM_GET_ACTIVITY_DETAILS table function - Return detailed information about a specific activity

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.

Note: This table function has been deprecated and replaced by MON_GET_ACTIVITY_DETAILS table function - Return information about an activity as an XML document.

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

Syntax

Read syntax diagramSkip visual syntax diagramWLM_GET_ACTIVITY_DETAILS(application_handle ,uow_id,activity_id,member )

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.
member
An input argument of type INTEGER that specifies a valid member number in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If a null value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:
  • EXECUTE privilege on the routine
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • WLMADM authority

Default PUBLIC privilege

None

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 member. This example is restricted to showing only an eleven member subset of the name-value pairs for each member 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 is an example of 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 member 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 member has a value of WLM_QUEUE.

Information returned

Table 1. Information returned for WLM_GET_ACTIVITY_DETAILS
Column Name Data Type Description
DBPARTITIONNUM SMALLINT dbpartitionnum - Database partition number monitor element
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 activity_id - Activity ID monitor element
ACTIVITY_STATE activity_state - Activity state monitor element
ACTIVITY_TYPE activity_type - Activity type monitor element
APPLICATION_HANDLE application_handle - Application handle monitor element
COORD_PARTITION_NUM 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 effective_isolation - Effective isolation monitor element
EFFECTIVE_LOCK_TIMEOUT effective_lock_timeout - Effective lock timeout monitor element
EFFECTIVE_QUERY_DEGREE effective_query_degree - Effective query degree monitor element
ENTRY_TIME entry_time - Entry time monitor element
INVOCATION_ID invocation_id - Invocation ID monitor element
LAST_REFERENCE_TIME last_reference_time - Last reference time monitor element
LOCAL_START_TIME local_start_time - Local start time monitor element
NESTING_LEVEL nesting_level - Nesting level monitor element
PACKAGE_NAME package_name - Package name monitor element
PACKAGE_SCHEMA package_schema - Package schema monitor element
PACKAGE_VERSION_ID package_version_id - Package version monitor element
PARENT_ACTIVITY_ID 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 member.
QUERY_COST_ESTIMATE query_cost_estimate - Query cost estimate monitor element
ROUTINE_ID routine_id - Routine ID monitor element
ROWS_FETCHED rows_fetched - Rows fetched monitor element
ROWS_MODIFIED rows_modified - Rows modified monitor element
SECTION_NUMBER section_number - Section number monitor element
SERVICE_CLASS_ID 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 stmt_pkgcache_id - Statement package cache identifier monitor element
STMT_TEXT stmt_text - SQL statement text monitor element
SYSTEM_CPU_TIME system_cpu_time - System CPU time monitor element
UOW_ID uow_id - Unit of work ID monitor element
USER_CPU_TIME user_cpu_time - User CPU time monitor element
UTILITY_ID 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 activitytotaltime_threshold_id - Activity total time threshold ID monitor element
ACTIVITYTOTALTIME_THRESHOLD_VALUE activitytotaltime_threshold_value - Activity total time threshold value monitor element
ACTIVITYTOTALTIME_THRESHOLD_VIOLATED activitytotaltime_threshold_violated - Activity total time threshold violated monitor element
CONCURRENTDBCOORDACTIVITIES_DB_THRESHOLD_ID concurrentdbcoordactivities_db_threshold_id - Concurrent database coordinator activities database threshold ID monitor element
CONCURRENTDBCOORDACTIVITIES_DB_THRESHOLD_QUEUED concurrentdbcoordactivities_db_threshold_queued - Concurrent database coordinator activities database threshold queued monitor element
CONCURRENTDBCOORDACTIVITIES_DB_THRESHOLD_VALUE concurrentdbcoordactivities_db_threshold_value - Concurrent database coordinator activities database threshold value monitor element
CONCURRENTDBCOORDACTIVITIES_DB_THRESHOLD_VIOLATED concurrentdbcoordactivities_db_threshold_violated - Concurrent database coordinator activities database threshold violated monitor element
CONCURRENTDBCOORDACTIVITIES_SUBCLASS_THRESHOLD_ID concurrentdbcoordactivities_subclass_threshold_id - Concurrent database coordinator activities service subclass threshold ID monitor element
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 concurrentdbcoordactivities_subclass_threshold_value - Concurrent database coordinator activities service subclass threshold value monitor element
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 estimatedsqlcost_threshold_id - Estimated SQL cost threshold ID monitor element
ESTIMATEDSQLCOST_THRESHOLD_VALUE estimatedsqlcost_threshold_value - Estimated SQL cost threshold value monitor element
ESTIMATEDSQLCOST_THRESHOLD_VIOLATED estimatedsqlcost_threshold_violated - Estimated SQL cost threshold violated monitor element
SQLROWSRETURNED_THRESHOLD_ID sqlrowsreturned_threshold_id - SQL rows read returned threshold ID monitor element
SQLROWSRETURNED_THRESHOLD_VALUE sqlrowsreturned_threshold_value - SQL rows read returned threshold value monitor element
SQLROWSRETURNED_THRESHOLD_VIOLATED sqlrowsreturned_threshold_violated - SQL rows read returned threshold violated monitor element
SQLTEMPSPACE_THRESHOLD_ID sqltempspace_threshold_id - SQL temporary space threshold ID monitor element
SQLTEMPSPACE_THRESHOLD_VALUE sqltempspace_threshold_value - SQL temporary space threshold value monitor element
SQLTEMPSPACE_THRESHOLD_VIOLATED sqltempspace_threshold_violated - SQL temporary space threshold violated monitor element