MON_GET_ACTIVITY_DETAILS table function - Return information about an activity as an XML document

Authorization

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine 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, and an SQL0171N 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, and an SQL0171N 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, and an SQL0171N 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 active database members. If the null value is specified, -1 is set implicitly.

Information returned

<db2_activity_details xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
  <member>0</member>
  <application_handle>70</application_handle>
  <activity_id>1</activity_id>
  <activity_state>IDLE</activity_state>
  <activity_type>READ_DML</activity_type>
  <uow_id>1</uow_id>
  ...
  <activity_metrics release="90700000">
    <lock_wait_time>2000</lock_wait_time>
    ...
  </activity_metrics>
</db2_activity_details>

<xs:simpleType name = "executable_id_type" >
  <xs:annotation>
     <xs:documentation>
       The binary Executable ID
     </xs:documentation>
  </xs:annotation>
   <xs:restriction base = "xs:hexBinary" >
      <xs:maxLength value = "32" />
   </xs:restriction>
</xs:simpleType>

Detailed metrics returned

Usage notes

The MON_GET_ACTIVITY_DETAILS function provides maximum flexibility for formatting output because it returns detailed information for a single activity as an XML document. The XML output includes both descriptive information (for example, statement text) and metrics. The output can be parsed directly by an XML parser, or it can be converted to relational format by the XMLTABLE function as shown in the example.

The metrics reported through this function (for example, CPU usage) are rolled up to the activity periodically during the lifetime of the activity. Therefore, the values reported by this table function reflect the current state of the system at the time of the most recent rollup.

Activity metrics are controlled through the COLLECT ACTIVITY METRICS clause on workloads, or the mon_act_metrics database configuration parameter at the database level. Metrics are collected if the connection that submits the activity is associated with a workload or database for which activity metrics are enabled. If activity metrics are not collected for an activity, all metrics are reported as 0.

The MON_GET_ACTIVITY_DETAILS table function returns one row of data for each member on which the activity exists. No aggregation across members is performed for the metrics. However, aggregation can be achieved through SQL queries.

The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.

Example

Investigate a long running query to determine if it is spending its time executing or waiting (for example, blocked on locks or I/O).

Note: The following queries can be combined into one statement and are shown in 2 steps for reasons of clarity. Also, if you want to retrieve the complete text, you could use the executable ID to obtain the statement text from the MON_GET_PKG_CACHE_STMT table function.

1. First use the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function to list activities and their start times.

   SELECT application_handle, 
          activity_id, 
          uow_id, 
          local_start_time 
   FROM TABLE(
      WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(
         cast(NULL as bigint), -1)
   ) AS T

   The following is an example of output from this query.

   APPLICATION_HANDLE   ACTIVITY_ID UOW_ID      LOCAL_START_TIME          
   -------------------- ----------- ----------- --------------------------
                      7           1           2 2008-06-10-10.06.55.675668
                     16           1           7 2008-06-10-10.08.38.613610
   
     2 record(s) selected.
   

2. Then use the MON_GET_ACTIVITY_DETAILS table function to view the percentage of time that the activity has spent waiting.

   SELECT actmetrics.application_handle,
     actmetrics.activity_id,
     actmetrics.uow_id,
     varchar(actmetrics.stmt_text, 50) as stmt_text,
     actmetrics.total_act_time,
     actmetrics.total_act_wait_time,
     CASE WHEN actmetrics.total_act_time > 0
       THEN DEC((
         FLOAT(actmetrics.total_act_wait_time) / 
         FLOAT(actmetrics.total_act_time)) * 100, 5, 2)
       ELSE NULL
     END AS PERCENTAGE_WAIT_TIME 
   FROM TABLE(MON_GET_ACTIVITY_DETAILS(7, 2, 1, -2)) AS ACTDETAILS,
   XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
     '$actmetrics/db2_activity_details' 
     PASSING XMLPARSE(DOCUMENT ACTDETAILS.DETAILS) as "actmetrics"
     COLUMNS "APPLICATION_HANDLE" INTEGER PATH 'application_handle', 
       "ACTIVITY_ID" INTEGER PATH 'activity_id',
       "UOW_ID" INTEGER PATH 'uow_id',
       "STMT_TEXT" VARCHAR(1024) PATH 'stmt_text',
       "TOTAL_ACT_TIME" INTEGER PATH 'activity_metrics/total_act_time',
       "TOTAL_ACT_WAIT_TIME" INTEGER PATH 'activity_metrics/total_act_wait_time'
     ) AS ACTMETRICS;
   

   The following is an example of output from this query.

   APPLICATION_HANDLE ACTIVITY_ID UOW_ID      ... 
   ------------------ ----------- ----------- ... 
                    7           1           2 ... 
   
     1 record(s) selected.
   

   Output for query (continued).

   ... STMT_TEXT                                          ... 
   ... -------------------------------------------------- ... 
   ... select * from syscat.tables optimize for 1 row     ... 

   Output for query (continued).

   ... TOTAL_ACT_TIME TOTAL_ACT_WAIT_TIME PERCENTAGE_WAIT_TIME
   ... -------------- ------------------- --------------------
   ...            459                   0                 0.00

Use the MON_GET_ACTIVITY_DETAILS table function to create a query that captures information about all the activities currently running on a system.

• Example 1: Run the following command using the command line processor (CLP)

  WITH A1 AS 
     (SELECT * FROM TABLE(wlm_get_workload_occurrence_activities(null, -1)) 
      WHERE activity_id > 0 ) 
  SELECT A1.application_handle, 
    A1.activity_id, 
    A1.uow_id, 
    total_act_time, 
    total_act_wait_time, 
    varchar(actmetrics.stmt_text, 50) AS stmt_text FROM A1, 
  TABLE(MON_GET_ACTIVITY_DETAILS(A1.application_handle, A1.uow_id,A1.activity_id, -1)) 
      AS ACTDETAILS, 
  XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'), 
   '$actmetrics/db2_activity_details' 
   PASSING XMLPARSE(DOCUMENT ACTDETAILS.DETAILS) AS "actmetrics" 
   COLUMNS "STMT_TEXT" VARCHAR(1024) PATH 'stmt_text', 
   "TOTAL_ACT_TIME" INTEGER PATH 'activity_metrics/total_act_time', 
   "TOTAL_ACT_WAIT_TIME" INTEGER PATH 'activity_metrics/total_act_wait_time' ) 
   AS ACTMETRICS

  The following is an example of output from this query:

  APP...HANDLE  A..._ID  UOW_ID  T...ACT_TIME  T...WAIT_TIME  
  ------------  -------  ------  ------------  -------------  
  15            1        5       16            5              
  15            1        3       17            5              
  7             1        49      0             0              
  SQL0445W Value "with A1 as (select * from table(wlm_get_workload
  3 record(s) selected with 1 warning messages printed.

  The following sample continues output from this query:

  ... STMT_TEXT
  ... -------------------------------------------------
  ... select name from sysibm.systables
  ... select * from sysibm.systables
  ... with A1 as (select * from table(wlm_get_workload_o
  _occurrence_" has been truncated. SQLSTATE=01004
  
  3 record(s) selected with 1 warning messages printed.