Are you using MON__GET_PKG_CACHE_STMT SQL Table Function?

New in DB2 9.7 is the mon_get_pkg_cache_stmt SQL table function. You may wonder why I am mentioning this now, several years since 9.7 has been out. Well, many folks are still not aware of it. In addition to the MONREPORT reporting module, and its various options, you should also use the mon_get_pkg_cache_stmt SQL  table function to identify suboptimal dynamic and STATIC, yes, STATIC SQL. This was first capability to capture static SQL in DB2 via a point in time table function. Here is how to call it: SELECT * FROM TABLE(MON_GET_PKG_CACHE_STMT( null,null,null,-2 )) as x, however you will want to specify the columns of interest to you instead of using SELECT *. Refer to the DB2 Knowledge Center for a description of data returned at this link, 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 . Add this SQL table function to your toolbox if you haven’t already…Stay Tuned!

Here’s a screenshot of it in DB2 10.5:

Doc4