MON_GET_ROUTINE_DETAILS table function - get aggregated routine execution metrics as an XML document

One of the following authorizations is required:

• EXECUTE privilege on the routine
• DATAACCESS authority
• SQLADM authority
• DBADM authority

routine_type

An input parameter of type CHAR(2) that specifies the type of routine or compiled trigger for which data is 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 an anonymous block

If the "P", "F"', "T", or "C" routine type is specified and no input name is specified, all routines of the given type will be returned. If the routine type is "SP" or "SF", the specific name of the routine must be provided.

All routines of all types will be returned if the routine type is NULL, an empty string, or blanks.

routine_schema

An input parameter of type VARCHAR(128) that specifies the schema for the routine or trigger. For dynamically prepared compound SQL statements and PL/SQL 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. Use 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 should be provided. For dynamically prepared compound SQL statements and PL/SQL anonymous blocks, the name can be determined using the MON_GET_SECTION_ROUTINES table function. Use 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.

The following example shows the structure of the XML document that is returned in the DETAILS column.

<db2_routine xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="100102000">
   <routine_type>P</routine_type>
   <routine_schema>TEST</routine_schema>
   <routine_module></routine_module>
   <routine_name>PROC1</routine_name>
   ...
   <system_metrics release="100102000">
      <act_aborted_total>5</act_aborted_total>
   </system_metrics>
</db2_routine>

See sqllib/misc/DB2MonRoutines.xsd for the full schema.

List where time was spent during the processing of the TEST.PROC1 stored procedure, identifying the top 5 areas where time is spent.

   SELECT B.* FROM TABLE(MON_GET_ROUTINE_DETAILS('P','TEST',NULL,'PROC1', -1)) 
     AS A, TABLE(MON_FORMAT_XML_TIMES_BY_ROW(A.DETAILS)) AS B
     ORDER BY TOTAL_TIME_VALUE DESC FETCH FIRST 5 ROWS ONLY

returns

METRIC_NAME                       TOTAL_TIME_VALUE COUNT PARENT_METRIC_NAME
--------------------------------- ---------------- ----- ------------------
TOTAL_RQST_TIME                               1055     0 -
TOTAL_ROUTINE_USER_CODE_PROC_TIME              673     0 TOTAL_RQST_TIME
TOTAL_WAIT_TIME                                317     - TOTAL_RQST_TIME
POOL_READ_TIME                                 236   142 TOTAL_WAIT_TIME
DIRECT_READ_TIME                                81    39 TOTAL_WAIT_TIME

  5 record(s) selected.