--#SET TERMINATOR /

-- -------------------------------------------------------------------
-- Create event monitor: POT_EVEMON
-- -------------------------------------------------------------------

CREATE EVENT MONITOR pot_evemon FOR STATEMENTS WRITE TO TABLE  
  CONNHEADER (TABLE pot_evemon_head
	       INCLUDES (AGENT_ID,
			 APPL_ID,
			 APPL_NAME,
			 AUTH_ID,
			 CLIENT_DB_ALIAS,
			 CLIENT_NNAME,
			 CLIENT_PID,
			 CLIENT_PLATFORM,
			 CLIENT_PRDID,
			 CLIENT_PROTOCOL,
			 CODEPAGE_ID,
			 CONN_TIME,
			 CORR_TOKEN,
			 EXECUTION_ID,
			 SEQUENCE_NO,
			 TERRITORY_CODE ) ),
 STMT (TABLE pot_evemon_stmt
	       INCLUDES (AGENT_ID,
			 AGENTS_TOP,
			 APPL_ID,
			 BLOCKING_CURSOR,
			 CONSISTENCY_TOKEN,
			 CREATOR,
			 CURSOR_NAME,
			 EVMON_FLUSHES,
			 FETCH_COUNT,
			 INT_ROWS_DELETED,
			 INT_ROWS_INSERTED,
			 INT_ROWS_UPDATED,
			 PACKAGE_NAME,
			 PACKAGE_VERSION_ID,
			 PARTIAL_RECORD,
			 POOL_DATA_L_READS,
			 POOL_DATA_P_READS,
			 POOL_INDEX_L_READS,
			 POOL_INDEX_P_READS,
			 POOL_TEMP_DATA_L_READS,
			 POOL_TEMP_DATA_P_READS,
			 POOL_TEMP_INDEX_L_READS,
			 POOL_TEMP_INDEX_P_READS,
			 POOL_TEMP_XDA_L_READS,
			 POOL_TEMP_XDA_P_READS,
			 POOL_XDA_L_READS,
			 POOL_XDA_P_READS,
			 ROWS_READ,
			 ROWS_WRITTEN,
			 SECTION_NUMBER,
			 SEQUENCE_NO,
			 SORT_OVERFLOWS,
			 SQL_REQ_ID,
			 SQLCABC,
			 SQLCAID,
			 SQLCODE,
			 SQLERRD1,
			 SQLERRD2,
			 SQLERRD3,
			 SQLERRD4,
			 SQLERRD5,
			 SQLERRD6,
			 SQLERRM,
			 SQLERRP,
			 SQLSTATE,
			 SQLWARN,
			 START_TIME,
			 STATS_FABRICATE_TIME,
			 STMT_OPERATION,
			 STMT_TEXT,
			 STMT_TYPE,
			 STOP_TIME,
			 SYNC_RUNSTATS_TIME,
			 SYSTEM_CPU_TIME,
			 TOTAL_SORT_TIME,
			 TOTAL_SORTS,
			 USER_CPU_TIME ) ),
 CONTROL (TABLE pot_evemon_ctrl
	       INCLUDES (EVENT_MONITOR_NAME,
			 MESSAGE,
			 MESSAGE_TIME ) ) 
 BUFFERSIZE 16 BLOCKED MANUALSTART 
/


-- -------------------------------------------------------------------
-- Create stored procedure: MONITOR_RS
-- -------------------------------------------------------------------
-- Note: this is a SQL PL Procedure (Not a PL/SQL like MONITOR_SP)

CREATE PROCEDURE MONITOR_RS 
(
   IN  v_spName  VARCHAR(128)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN   
   DECLARE pkg_name VARCHAR(128);
   
   DECLARE c1 CURSOR WITH RETURN FOR 
        SELECT (DAYS(STOP_TIME) - DAYS(START_TIME)) * 86400 + 
               (MIDNIGHT_SECONDS(STOP_TIME) - MIDNIGHT_SECONDS(START_TIME)) + 
               (MICROSECOND (STOP_TIME) - MICROSECOND (START_TIME))/1000000 ELAPSED_TIME,
               ST.TEXT,
               ROWS_READ, 
               ROWS_WRITTEN,
               SYSTEM_CPU_TIME,
               USER_CPU_TIME
        FROM POT_EVEMON_STMT EV, SYSCAT.STATEMENTS ST
        WHERE EV.PACKAGE_NAME = ST.PKGNAME
        AND   ST.PKGSCHEMA = USER
        AND   EV.SECTION_NUMBER = ST.SECTNO
        AND   EV.PACKAGE_NAME = pkg_name
        ORDER BY ELAPSED_TIME DESC;

   SELECT   DEPS.BNAME PACKAGE      
     INTO   pkg_name
     FROM  SYSIBM.SYSDEPENDENCIES DEPS,
           SYSIBM.SYSROUTINES PROCS
    WHERE  DEPS.DTYPE = 'F' 
      AND  DEPS.BTYPE = 'K'
      AND  PROCS.SPECIFICNAME  = DEPS.DNAME
      AND  PROCS.ROUTINESCHEMA = DEPS.DSCHEMA
      AND  PROCS.ROUTINESCHEMA = USER
      AND  PROCS.ROUTINENAME   = v_spName;

  OPEN c1;

END
/

COMMIT
/