Retrieve SQL statements Info from MON_GET_PKG_CACHE_STMT

[fmshire]

DB2 Level: DB2 v9.7.0.9
OS:
Linux xxx.xxxx.com 2.6.32-431.20.3.el6.x86_64 #1 SMP Fri Jun 6 18:30:54 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux

I’m running this query to retrieve info about the current Dynamic SQL in the package cache.. :

DECIMAL(100*(FLOAT(TOTAL_CPU_TIME)/SUM_TAB.SUM_CPU),5,2) AS PCT_TOT_CPU, NUM_EXECUTIONS, STMT_EXEC_TIME, DECIMAL(100*(FLOAT(STMT_EXEC_TIME)/SUM_TAB.SUM_EXEC),5,2) AS PC

T_TOT_EXEC FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) AS T, SUM_TAB ORDER BY ROWS_READ DESC FETCH FIRST 15 ROWS ONLY WITH UR;



The results looks like this:

INSERT_TIMESTAMP STATEMENT ROWS_READ PCT_TOT_RR TOTAL_CPU_TIME PCT_TOT_CPU NUM_EXECUTIONS STMT_EXEC_TIME PCT_TOT_EXEC

-------------------------- ---------- -------------------- ---------- -------------------- ----------- -------------------- -------------------- ------------

2015-01-23-03.03.02.765094 select m.c 46773278079 83.56 20991569835 27.73 93769 21308997 26.70



As you can see this Statement was executed 9376... Does this means the SQL statement was entered into the Package cache at 2015-01-23-03.03: AM this morning and since than it has executed over 93,000 times! Right now is 3:55 PM, which means around 13 hours that many times was executed!



I need to get some scope on what the duration is that this data represents.

According to DB2 9.7 Doc. for MON_GET_PKG_CACHE_STMT table function (http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055017.html?cp=SSEPGG_9.7.0%2F3-6-1-3-9-25) they’re saying:



insert_timestamp - Insert timestamp monitor element

The time when the statement or section was inserted into the cache. For dynamic sql snapshots this represents the time when the statement entered the cache. For MON_GET_PKG_CACHE_STMT, MON_GET_PKG_CACHE_STMT_DETAILS and the package cache event monitor, the value is more granular and represents the time when an individual section for this statement was inserted into the cache....



Also, is there a way I can find out when was the last time this SQL statement was executed?