Example: Retrieving statement text for a routine
You can use routine monitoring to retrieve the statements that are executed by a routine.
Scenario
In this example, a database administrator
(DBA) is manually investigating costly statements executed by a routine.
As part of the investigation it can be useful to link a row in MON_GET_ROUTINE_EXEC_LIST
to a specific statement or line of the routine. Getting statement
text for short lived routines like anonymous blocks, dynamic SQL statements,
or external routines is accomplished by retrieving the statement from
the package cache. The following query links a row in MON_GET_ROUTINE_EXEC_LIST
to a specific statement:
SELECT
A.ROUTINETYPE, A.ROUTINESCHEMA, A.ROUTINENAME,
A.SECTION_TYPE, A.SECTION_NUMBER, A.STMTNO,
SUBSTR(B.STMT_TEXT,1,160)
FROM
TABLE(MON_GET_ROUTINE_EXEC_LIST('P','DRICARD',NULL,'PROC1',-1)) AS A,
TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS B
WHERE
A.EXECUTABLE_ID=B.EXECUTABLE_ID
The returned result
is as follows:STMT_TEXT
--------------------------------------------
WITH GET_UPDATE_LIST (COL1, COL2STATS) AS AF
insert into T1 values(3,'d','d','d')
call SYSIBMSUBROUTINE.P1_66613_1157394573()
3 record(s) selected.
Note: If a dynamic SQL statement
or external routine statement is returned by MON_GET_ROUTINE_EXEC_LIST
and the associated executable_id is no longer
in the package cache, that statement text cannot be recovered unless
an event monitor was used to log this information. The refresh cycle
for InfoSphere® Optim Performance Manager product
allows it to retrieve this information in most cases.
For
compiled SQL statements and inlined routines, the statement text can
be found by using the package and statement information that is returned
by MON_GET_ROUTINE_EXEC_LIST. For example:
SELECT RS.ROUTINETYPE, RS.ROUTINESCHEMA, RS.ROUTINENAME,
RS.SECTION_NUMBER, RS.STMTNO, SUBSTR(SS.TEXT,1,160)
FROM
TABLE(MON_GET_ROUTINE_EXEC_LIST('F','DRICARD','','MYFUNC',-1)) AS RS,
SYSIBM.SYSSTMT SS
WHERE
RS.SECTION_TYPE = 'S'
AND SS.PLNAME = RS.PACKAGE_SCHEMA
AND SS.PLCREATOR = RS.PACKAGE_NAME
AND SS.STMTNO = RS.STMTNO
AND SS.SECTNO = RS.SECTION_NUMBER
The returned result
is as follows:STMT_TEXT
--------------------------------------------
insert into MYTABLE values('1')
1 record(s) selected.