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.