MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine

Authorization

Default PUBLIC privilege

None

Syntax

>>-MON_GET_ROUTINE_EXEC_LIST--(--routine_type--,--routine_schema--,-->

>--routine_module_name--,--routine_name--,--member--)----------><

The schema is SYSPROC.

Routine parameters

Information returned

Usage notes

The MON_GET_ROUTINE_EXEC_LIST table function returns one row for each unique statement (section) executed by a routine or trigger. The function also returns a set of metrics aggregated across all executions of that statement within the routine or trigger. No aggregation across members is performed. However, an aggregation across members is possible through SQL queries (as shown in the Examples section).

The aggregate metrics do not include metrics for any child statements. For example, if a CALL statement is executed by a routine, the metrics returned in MON_GET_ROUTINE_EXEC_LIST for the CALL statement do not include metrics for any work executed by other statements invoked by the CALL. The only exception is coord_stmt_exec_time monitor element that returns the elapsed time of the statement and so implicitly captures any time spent in child statements.

Routine monitoring data collection and statement monitoring must be explicitly enabled using the mon_rtn_data and mon_rtn_execlist database configuration parameters. If these configuration parameters are disabled, no information is returned.

The counters and time-spent monitor elements returned by this table function are controlled with the COLLECT ACTIVITY METRICS clause on workloads and the mon_act_metrics database configuration parameter at the database level. If neither control is enabled, the counters and time-spent monitor elements reported are 0.

When the package for a dynamically prepared compound SQL statement or an anonymous block is cleaned up from the package cache, information for this statement is no longer be reported by MON_GET_ROUTINE_EXEC_LIST function. Similarly, when a routine or trigger is dropped, information about the routine or trigger is no longer be reported.

Statements executed by routines that were subsequently dropped or not executed during the previous 24 hour period are pruned from memory and not returned.

Examples

1. List all the statements executed by function TEST.X.

   SELECT SUBSTR(P.STMT_TEXT,1,45) AS TEXT 
      FROM TABLE(MON_GET_ROUTINE_EXEC_LIST('F', 'TEST', NULL, 'X', -1)) 
      AS T, TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS P 
      WHERE T.EXECUTABLE_ID = P.EXECUTABLE_ID

   returns

    TEXT                                   
    ---------------------------------------------
    WITH PID_VALUES (WIDEBAND,ENGN_TEMP) AS SEL
    insert into GSO_LOGS values(8.7, 145, 1.406)
    call SYSIBMSUBROUTINE.TEST_66613_1157394573() 
   
     3 record(s) selected.

2. List all the statements in procedure TEST.PROC1 by the percentage of coordinator statement execution time that they are consuming.

   SELECT 100*B.COORD_STMT_EXEC_TIME / A.TOTAL_ROUTINE_COORD_EXEC_TIME 
      AS PERCENT_EXEC_TIME, SUBSTR(C.STMT_TEXT,1,45) 
      AS STMT_TEXT FROM TABLE(MON_GET_ROUTINE('SP',NULL,NULL,'PROC1', -1)) AS A, 
      TABLE(MON_GET_ROUTINE_EXEC_LIST('SP',NULL,NULL,'PROC1', -1)) AS B, 
      TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS C 
      WHERE B.EXECUTABLE_ID = C.EXECUTABLE_ID ORDER BY B.COORD_STMT_EXEC_TIME DESC

   returns

    PERCENT_EXEC_TIME  STMT_TEXT                                   
    ------------------ ---------------------------------------------
                    10  SELECT WAFR, MPHX64, ENGN_RPM FROM KB28_LOGS
                     3  SELECT ( ENGN_TQ * ENGN_RPM )/5252 AS HP FRO 
   
     2 record(s) selected.