Scenario: Aggregation of activity metrics across child activities

Each row in an activity event monitor contains information and monitor metrics describing the execution of a specific activity. Included in the activity information are the parent_uow_id and parent_activity_id monitor elements. These monitor elements are nonzero if the activity is a child of some other activity (nested) and the elements identify the parent activity.

Using recursive SQL, it is possible to aggregate the monitor metrics in the activity event monitor across all child activities to their parent activity. Viewing the aggregated metrics for the parent is useful in identifying which parts of a stored procedure may benefit from tuning. For example, if a stored procedure P1 calls stored procedures P2 and P3, the aggregated metrics might show that 90% of the CPU usage during a call to P1 occurs during the processing of statements in the nested procedure P3. As a result, you can focus your investigation accordingly.

You can use the following as an example of how to obtain aggregates of nested metrics from an activity event monitor.

Note: The @ character is used as a statement termination character in this example.

The following statements can be copied to a file (for example, test.clp) and executed using db2 -td@ -f filename, as shown in the following example command:

db2 -td@ -f test.clp

For the purpose of this example, suppose the following tables and stored procedures exist:

CREATE TABLE T1 (ONE INT)@ 

DROP PROCEDURE TEST.P1@ 
DROP PROCEDURE TEST.P2@ 
DROP PROCEDURE TEST.P3@ 
DROP PROCEDURE TEST.P4@ 

CREATE PROCEDURE TEST.P4() 
LANGUAGE SQL 
BEGIN 
  INSERT INTO T1 VALUES(5); 
  INSERT INTO T1 VALUES(6); 
  INSERT INTO T1 VALUES(7); 
END@ 

CREATE PROCEDURE TEST.P3() 
LANGUAGE SQL 
BEGIN 
  DECLARE V INTEGER; 

  INSERT INTO T1 VALUES(1); 
  CALL TEST.P4(); 
  SELECT COUNT(*) INTO V FROM T1; 
END@ 

CREATE PROCEDURE TEST.P2() 
LANGUAGE SQL 
BEGIN 
  INSERT INTO T1 VALUES(2); 
  INSERT INTO T1 VALUES(3); 
END@ 

CREATE PROCEDURE TEST.P1() 
LANGUAGE SQL 
BEGIN 
  CALL TEST.P3(); 
  CALL TEST.P2(); 
  INSERT INTO T1 VALUES(4); 
END@

Procedure

Create an activity event monitor and enable activity capture. In this example, activity capture is enabled for the current connection using the WLM_SET_CONN_ENV procedure. Execute the TEST.P1 procedure using the same connection and then disable the activity event monitor. Activity information will be captured for the TEST.P1 procedure and all of its child activities.
```
CREATE EVENT MONITOR A FOR ACTIVITIES WRITE TO TABLE@ 
SET EVENT MONITOR A STATE 1@ 

CALL WLM_SET_CONN_ENV(NULL, '<collectactdata>WITH DETAILS</collectactdata><collectactpartition>ALL</collectactpartition>')@

CALL TEST.P1()@

SET EVENT MONITOR A STATE 0@

CALL WLM_SET_CONN_ENV(NULL, '<collectactdata>NONE</collectactdata>')@ 
```

Execute the following query to display the captured statements, along with their parent identification information, individual CPU consumption, and aggregate CPU consumption. The aggregate CPU consumption is recursively summed across all child activities. This query can be easily extended to support any of the monitoring metrics captured by the activity event monitor.

Note: To improve readability, only CPU consumption is reported here.

WITH ACT( APPL_ID,
          UOW_ID,
          ACTIVITY_ID,
          PARENT_UOW_ID,
          PARENT_ACTIVITY_ID,
          CPU )
  AS ( SELECT APPL_ID,
              UOW_ID,
              ACTIVITY_ID,
              MAX(PARENT_UOW_ID),
              MAX(PARENT_ACTIVITY_ID),
              SUM(METRICS.TOTAL_CPU_TIME)
        FROM ACTIVITY_A AS A,
            XMLTABLE (XMLNAMESPACES( DEFAULT 'http://www.ibm.com/xmlns/prod/db2/mon'),
                '$actmetrics/activity_metrics' PASSING XMLPARSE(DOCUMENT A.DETAILS_XML) as "actmetrics"
                COLUMNS
                  TOTAL_CPU_TIME BIGINT PATH 'total_cpu_time' ) AS METRICS
        WHERE A.PARTIAL_RECORD = 0
        GROUP BY APPL_ID,
                UOW_ID,
                ACTIVITY_ID ),
    TMP( BASE_APPL_ID,
          BASE_UOW_ID,
          BASE_ACTIVITY_ID,
          APPL_ID,
          UOW_ID,
          ACTIVITY_ID,
          PARENT_UOW_ID,
          PARENT_ACTIVITY_ID,
          CPU,
          LEVEL )
  AS ( SELECT APPL_ID,
              UOW_ID,
              ACTIVITY_ID,
              APPL_ID,
              UOW_ID,
              ACTIVITY_ID,
              PARENT_UOW_ID,
              PARENT_ACTIVITY_ID,
              CPU,
              1
        FROM ACT
      UNION ALL
        SELECT T.BASE_APPL_ID,
              T.BASE_UOW_ID,
              T.BASE_ACTIVITY_ID,
              A.APPL_ID,
              A.UOW_ID,
              A.ACTIVITY_ID,
              A.PARENT_UOW_ID,
              A.PARENT_ACTIVITY_ID,
              A.CPU,
              T.LEVEL + 1
        FROM ACT AS A, TMP AS T
        WHERE A.APPL_ID = T.APPL_ID AND
              A.PARENT_UOW_ID = T.UOW_ID AND
              A.PARENT_ACTIVITY_ID = T.ACTIVITY_ID AND
              T.LEVEL < 128 ),
    AGG( APPL_ID,
          UOW_ID,
          ACTIVITY_ID,
          CPU )
  AS ( SELECT BASE_APPL_ID,
              BASE_UOW_ID,
              BASE_ACTIVITY_ID,
              SUM(CPU)
        FROM TMP
        GROUP BY BASE_APPL_ID,
                BASE_UOW_ID,
                BASE_ACTIVITY_ID )
SELECT
      A.UOW_ID,
      A.ACTIVITY_ID,
      A.PARENT_UOW_ID,
      A.PARENT_ACTIVITY_ID,
      A.CPU AS STMT_CPU,
      B.CPU AS AGG_CPU,
      SUBSTR(CONCAT(REPEAT('  ',C.STMT_NEST_LEVEL),
              C.STMT_TEXT),
              1, 30) AS STMT_TEXT
FROM ACT AS A,
    AGG AS B,
    ACTIVITYSTMT_A AS C,
    ACTIVITY_A AS D
WHERE A.APPL_ID = B.APPL_ID AND
      A.UOW_ID = B.UOW_ID AND
      A.ACTIVITY_ID = B.ACTIVITY_ID AND
      D.COORD_PARTITION_NUM = D.PARTITION_NUMBER AND
      A.APPL_ID = C.APPL_ID AND
      A.UOW_ID = C.UOW_ID AND
      A.ACTIVITY_ID = C.ACTIVITY_ID AND
      A.APPL_ID = D.APPL_ID AND
      A.UOW_ID = D.UOW_ID AND
      A.ACTIVITY_ID = D.ACTIVITY_ID AND
      D.PARTIAL_RECORD = 0
ORDER BY D.TIME_CREATED ASC@

This query produces the following output. The STMT_CPU column reports the CPU consumption for the statement without including CPU usage of any child activities. The AGG_CPU column reports the aggregate CPU consumption for the activity and all of its children.

Note: If queries execute too quickly to be measurable, STMT_CPU and AGG_CPU might be zero.

UOW_ID      ACTIVITY_ID          PARENT_UOW_ID PARENT_ACTIVITY_ID   STMT_CPU             AGG_CPU              STMT_TEXT                    
----------- -------------------- ------------- -------------------- -------------------- -------------------- ------------------------------ 
        576                    1             0                    0                 5353                84064 CALL TEST.P1()                
        576                    2           576                    1                 7444                52043 CALL TEST.P3()              
        576                    3           576                    2                 1869                 1869 INSERT INTO T1 VALUES(1)  
        576                    4           576                    2                11727                26935 CALL TEST.P4()            
        576                    5           576                    4                 2017                 2017 INSERT INTO T1 VALUES(5) 
        576                    6           576                    4                 6602                 6602 INSERT INTO T1 VALUES(6) 
        576                    7           576                    4                 6589                 6589 INSERT INTO T1 VALUES(7) 
        576                    8           576                    2                15795                15795 SELECT COUNT(*) INTO :HV00 
        576                    9           576                    1                11727                20314 CALL TEST.P2()              
        576                   10           576                    9                 1941                 1941 INSERT INTO T1 VALUES(2)  
        576                   11           576                    9                 6646                 6646 INSERT INTO T1 VALUES(3)  
        576                   12           576                    1                 6354                 6354 INSERT INTO T1 VALUES(4)    

  12 record(s) selected.