MON_GET_SECTION_ROUTINE table function - get list of routines for input section
The MON_GET_SECTION_ROUTINE table function returns a list of all procedures, external functions, compiled functions, and compiled triggers that might be invoked during the execution of the input section.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
-
executable_id
- An input parameter of type VARCHAR(32) FOR BIT DATA that uniquely identifies a section for which the list of routines is to be returned. If this argument is null, an empty string, or the section cannot be found in memory, SQL0171 is returned.
Information returned
Column name | Data type | Description or corresponding monitor element |
---|---|---|
ROUTINE_TYPE | CHAR(1) | routine_type - Routine type monitor element |
ROUTINE_SCHEMA | VARCHAR(128) | routine_schema - Routine schema monitor element |
ROUTINE_MODULE_NAME | VARCHAR(128) | routine_module_name - Routine module name monitor element |
ROUTINE_NAME | VARCHAR(128) | routine_name - Routine name monitor element |
SPECIFIC_NAME | VARCHAR(128) | specific_name - Specific name monitor element |
ROUTINE_ID | INTEGER | routine_id - Routine ID monitor element |
SUBROUTINE_ID | INTEGER | subroutine_id - Subroutine identifier monitor element |
LIB_ID | BIGINT | lib_id - Library identifier monitor element |
Usage notes
The input section must reside in the package cache.
Objects returned by this function are
not necessarily invoked during execution of the input section. For
example, in the following select statement FUNCTION1 is a compiled
function:
SELECT CASE WHEN COL1 = 0 THEN FUNCTION1() ELSE NULL END AS T1 FROM TABLE1
FThe
MON_GET_SECTION_ROUTINE table function will return a row with the
FUNCTION1 function when executable_id
is passed as
an argument, identifying the section for this statement. However,
FUNCTION1 will be invoked during execution of the statement only if
TABLE1 contains the value 0 for COL1 in one of its rows. Examples
- List all routines that might be executed by the section in the
package cache with executable ID x'0100000000000000520100000000000001000000010020120822205618607103'.
returnsSELECT * FROM TABLE(MON_GET_SECTION_ROUTINE( x'0100000000000000520100000000000001000000010020120822205618607103')) AS T
ROUTINE_TYPE ROUTINE_SCHEMA ROUTINE_MODULE_NAME ROUTINE_NAME ... ------------ --------------- ------------------- ------------ ... F SPEEDY TANKS ANALYZE_LOG1 ... P SPEEDY TANKS POST_PROCESS ... 2 record(s) selected.
- A user notices that the insert statement with executable ID x'0100000000000000520100000000000001000000010020120822205618607103'
ran for a long time. Run the following statement to check if any compiled
triggers were invoked during the execution of the insert:
returnsSELECT * FROM TABLE(MON_GET_SECTION_ROUTINE( x'0100000000000000520100000000000001000000010020120822205618607103')) AS T WHERE ROUTINETYPE = 'T'
ROUTINE_TYPE ROUTINE_SCHEMA ROUTINE_MODULE_NAME ROUTINE_NAME ... ------------ -------------- ------------------- ------------------ ... T MIKE INVALID_WELD_VALUE ... P MIKE INSERT_WELD_SPECS ... 2 record(s) selected.
- Find the internal schema and procedure name for an anonymous block
with executable ID x'0100000000000000520100000000000001000000010020120822205618607103'.
returnsSELECT ROUTINESCHEMA, ROUTINENAME, SPECIFICNAME FROM TABLE(MON_GET_SECTION_ROUTINE( x'0100000000000000520100000000000001000000010020120822205618607103')) AS T
ROUTINE_SCHEMA ROUTINE_NAME SPECIFIC_NAME ... --------------- ------------------ ------------------ ... GSOSCHEMA PING_RUBBLE SQL120801135351900 ... 1 record(s) selected.