469,307 Members | 1,926 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

Retrieve SQL statements Info from MON_GET_PKG_CACHE_STMT

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

Im 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) theyre 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?
Jan 26 '15 #1
0 1107

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Koen Van Herck | last post: by
2 posts views Thread by Torben Jensen | last post: by
reply views Thread by S.Mark | last post: by
13 posts views Thread by Ashley Bryant | last post: by
reply views Thread by buzdugans | last post: by
reply views Thread by Robert Bravery | last post: by
1 post views Thread by Mido Moonlite | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.