Example: Listing aggregate routine metrics for an anonymous block

You can use routine monitoring to list the aggregate metrics for statements that are executed by an anonymous block.

Scenario

In this example, a database administrator (DBA) wants to view the aggregate metrics for all statements that are executed by an anonymous block with the statement text 'BEGIN ... END'. The DBA issues the following query to find the executable_id for the anonymous block in the package cache:

SELECT EXECUTABLE_ID 
   FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL, NULL,NULL,-1)) AS T 
   WHERE STMT_TEXT = 'BEGIN BLAH BLAH END'

The returned result is:

EXECUTABLE_ID                                               
-------------------------------------------------------------------
x'01000000000000007A0000000000000000000000020020120801153841789993'

  1 record(s) selected.

Using the returned executable_id, the DBA issues the following query to find the aggregate metrics for the anonymous block:

SELECT * FROM TABLE(MON_GET_ROUTINE('A', NULL,NULL,NULL,-2)) AS T 
   WHERE DYN_COMPOUND_EXEC_ID =  x'01000000000000007A0000000000000000000000020020120801153841789993'

The returned result is:

ROUTINETYPE ROUTINESCHEMA   ROUTINEMODULENAME  ROUTINENAME                 SPECIFICNAME         DYN_COMPOUND_EXEC_ID                                               
----------- --------------- ------------------ --------------------------- -------------------- -------------------------------------------------------------------
C           SYSIBMINTERNAL  -                  COMPILED_ANON_BLOCK_INVOKE  SQL120801153841490   x'01000000000000007A0000000000000000000000020020120801153841789993'

  1 record(s) selected.

Alternatively, the DBA can look up the internal procedure and schema name corresponding to the anonymous block by using the MON_GET_SECTION_ROUTINE table function and then pass those values as inputs to the MON_GET_ROUTINE table function. This method would return only information for the anonymous block and so avoids filtering the output with a WHERE clause.