MON_GET_ROUTINE_EXEC_LIST table function - get list of statements executed by routine
The MON_GET_ROUTINE_EXEC_LIST table function returns a list of all statements (sections) executed by each procedure, external function, compiled function, compiled trigger, and anonymous block invoked since the database was activated.
Authorization
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
- routine_type
- An input parameter of type CHAR(2) that specifies the type of
routine or compiled trigger for which data is be returned:
- "P" for a procedure
- "SP" for the specific name of a procedure
- "F" for a compiled function
- "SF" for the specific name of a compiled function
- "T" for a compiled trigger
- "C" for a dynamically prepared compound SQL statement in SQL PL or an anonymous block in PL/SQL
If the routine type is an empty string, NULL, or blanks, all routines of all types are returned.
- routine_schema
- An input parameter of type VARCHAR(128) that specifies the schema for the routine or trigger. For dynamically prepared compound SQL statements or anonymous blocks, the schema can be determined using the MON_GET_SECTION_ROUTINES table function. Use NULL or an empty string to return the routines and triggers in all schemas. This parameter is case sensitive.
- routine_module_name
- An input parameter of type VARCHAR(128) that specifies the name of the module for the input routine, if applicable. Using NULL or an empty string to return the routines in all modules. This parameter is case sensitive.
- routine_name
- An input parameter of type VARCHAR(128) that specifies the name of the routine. If the input parameter is "SP" or "SF", the specific name of the routine must be provided. For dynamically prepared compound SQL statements or anonymous blocks, the name can be determined using the MON_GET_SECTION_ROUTINES table function. Using NULL or an empty string to return all routines matching the other input parameters. This parameter is case sensitive.
- member
- An input parameter of type INTEGER that specifies a valid member 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 implicitly set.
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
- List all the statements executed by function TEST.X.
returnsSELECT 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
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.
- List all the statements in procedure TEST.PROC1 by the percentage
of coordinator statement execution time that they are consuming.
returnsSELECT 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
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.