DB2 Version 10.1 for Linux, UNIX, and Windows

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


>>-MON_GET_SECTION_ROUTINE--(--executable_id--)----------------><

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

Table 1. Information returned for MON_GET_SECTION_ROUTINE
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'.

SELECT * FROM 
   TABLE(MON_GET_SECTION_ROUTINE(
      x'0100000000000000520100000000000001000000010020120822205618607103'))
   AS T

returns

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:

SELECT * FROM 
   TABLE(MON_GET_SECTION_ROUTINE(
      x'0100000000000000520100000000000001000000010020120822205618607103')) 
   AS T WHERE ROUTINETYPE = 'T'

returns

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'.

SELECT ROUTINESCHEMA, ROUTINENAME, SPECIFICNAME 
   FROM TABLE(MON_GET_SECTION_ROUTINE(
      x'0100000000000000520100000000000001000000010020120822205618607103')) 
   AS T

returns

ROUTINE_SCHEMA  ROUTINE_NAME       SPECIFIC_NAME       ...
--------------- ------------------ ------------------ ...
GSOSCHEMA       PING_RUBBLE        SQL120801135351900 ...
  1 record(s) selected.