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.