My New Best Friend – mon_ Part 2: Views

Introduced with DB2 9.7 were a large number of table functions starting with ‘mon_’. Plus a few views and other interesting tidbits. The more I use them, the more I become addicted to them. In part 1 of this series, I covered the MON_GET table functions. In this post, I’ll talk about some nifty views I’ve come across.

Get An Overall View of Key Performance Metrics

The MON_DB_SUMMARY view is good for this. True, views such as SNAPDB and SNAPBP would also work, but those do no use the new lighter weight monitoring interface. MON_GET_WORKLOAD also has the data needed to calculateher these values. An example of using MON_DB_SUMMARY:

db2 "select LOCK_WAIT_TIME_PERCENT, AVG_LOCK_TIMEOUTS_PER_ACT, AVG_DEADLOCKS_PER_ACT, AVG_LOCK_ESCALS_PER_ACT, ROWS_READ_PER_ROWS_RETURNED, TOTAL_BP_HIT_RATIO_PERCENT from sysibmadm.mon_db_summary"

LOCK_WAIT_TIME_PERCENT AVG_LOCK_TIMEOUTS_PER_ACT AVG_DEADLOCKS_PER_ACT AVG_LOCK_ESCALS_PER_ACT ROWS_READ_PER_ROWS_RETURNED TOTAL_BP_HIT_RATIO_PERCENT
---------------------- ------------------------- --------------------- ----------------------- --------------------------- --------------------------
                 43.82                         0                     0                       0                          21                      99.78

  1 record(s) selected.

Sometimes it is nice to have the values already calculated for you. Being a view, this is even easier to use than the MON_GET table functions.

Find Current SQL Executing in the Database

I don’t even think this was possible with the old snapshot monitoring methodology. Please comment and correct me if I’m wrong. With the older methodology, finding the current SQL required an event monitor for statements. A dynamic SQL snapshot would give you statements, but all of the ones in the package cache. And the snapshot for applications provided the last executed SQL, but that was last executed and not current. Now, we can use MON_CURRENT_SQL:

 db2 "select APPLICATION_HANDLE, APPLICATION_NAME, ELAPSED_TIME_SEC, ACTIVITY_STATE, QUERY_COST_ESTIMATE, STMT_TEXT from sysibmadm.mon_current_sql"

APPLICATION_HANDLE   APPLICATION_NAME                                                                                                                 ELAPSED_TIME_SEC ACTIVITY_STATE                   QUERY_COST_ESTIMATE  STMT_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
-------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------- -------------------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
               31452 db2bp                                                                                                                                           0 EXECUTING                                           1 select APPLICATION_HANDLE, APPLICATION_NAME, ELAPSED_TIME_SEC, ACTIVITY_STATE, QUERY_COST_ESTIMATE, STMT_TEXT from sysibmadm.mon_current_sql                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
               31233 db2jcc_application                                                                                                                             26 IDLE                                            39825 SELECT T1.TOTALTAX, T1.LOCKED, T1.TOTALTAXSHIPPING, T1.STATUS, T1.FIELD2, T1.TIMEPLACED, T1.FIELD3, T1.CURRENCY, T1.SEQUENCE, T1.TOTALADJUSTMENT, T1.ORMORDER, T1.SHIPASCOMPLETE, T1.PROVIDERORDERNUM, T1.TOTALPRODUCT, T1.DESCRIPTION, T1.MEMBER_ID, T1.ORGENTITY_ID, T1.FIELD1, T1.STOREENT_ID, T1.ORDCHNLTYP_ID, T1.ADDRESS_ID, T1.LASTUPDATE, T1.ORDERS_ID, T1.COMMENTS, T1.NOTIFICATIONID, T1.TYPE, T1.EDITOR_ID, T1.OPTCOUNTER, T1.SOURCEID, T1.EXPIREDATE, T1.BUSCHN_ID, T1.BLOCKED, T1.TOTALSHIPPING, T1.TRANSFERSTATUS, T1.OPSYSTEM_ID, T1.BUYERPO_ID FROM ORDERS  T1 WHERE (T1.STATUS = ?)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

  2 record(s) selected.

Information Similar to LIST APPLICATIONS

The closest I can get to my favorite fields from list applications (and a bit more) from the mon_ stuff is:

$ db2 "select substr(C.SESSION_AUTH_ID,1,10) as SESSION_AUTH_ID, substr(C.APPLICATION_NAME,1,20) as app_name, C.APPLICATION_HANDLE as APP_HANDLE, substr(CON.APPLICATION_ID,1,25) as APPLICATION_ID, substr(C.WORKLOAD_OCCURRENCE_STATE,1,15) as state, substr(CLIENT_WRKSTNNAME,1,15) as CLIENT_WRKSTNNAME from sysibmadm.MON_CURRENT_UOW as C, TABLE(MON_GET_CONNECTION(NULL,-2)) as CON where C.application_handle=CON.application_handle with ur"

SESSION_AUTH_ID APP_NAME             APP_HANDLE           APPLICATION_ID            STATE           CLIENT_WRKSTNNAME
--------------- -------------------- -------------------- ------------------------- --------------- -----------------
DB2INST1        db2bp                               55809 *LOCAL.db2inst1.130307034    UOWEXEC         -
WSCOMUSR        db2jcc_application                  55789 100.159.65.4.57849.130307030 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55334 100.159.65.4.55535.130306094 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55347 100.159.65.4.55612.130306094 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55662 100.159.65.4.57212.130306220 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55458 100.159.65.4.56161.130306140 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55661 100.159.65.4.57211.130306220 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55817 100.159.65.4.57977.130307040 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55343 100.159.65.4.55607.130306094 UOWWAIT         dev-app01
WSCOMUSR        db2jcc_application                  55737 100.159.65.4.57593.130307010 UOWWAIT         dev-app01

  10 record(s) selected.

For me, SYSIBMADM.APPLICATIONS is still superior here:

$ db2 "select substr(A.AUTHID,1,10) as AUTH_ID, substr(A.APPL_NAME,1,20) as app_name, A.AGENT_ID as APP_HANDLE, substr(A.APPL_ID,1,25) as APPLICATION_ID, substr(A.APPL_STATUS,1,15) as status, A.STATUS_CHANGE_TIME, second(current timestamp-A.STATUS_CHANGE_TIME) as SEC_IN_STATUS, substr(A.CLIENT_NNAME,1,15) as CLIENT_NNAME from SYSIBMADM.APPLICATIONS A with ur"

AUTH_ID    APP_NAME             APP_HANDLE           APPLICATION_ID               STATUS          STATUS_CHANGE_TIME         SEC_IN_STATUS CLIENT_NNAME
---------- -------------------- -------------------- ---------------------------- --------------- -------------------------- ------------- ---------------
DB2INST1   db2bp                               55809 *LOCAL.db2inst1.130307034    UOWEXEC         2013-03-06-23.38.21.759104             0 sigma-dev-db01
WSCOMUSR   db2jcc_application                  55789 100.159.65.4.57849.130307030 UOWWAIT         2013-03-06-23.30.00.212507            21 sigma-dev-app01
WSCOMUSR   db2stmm                             55335 *LOCAL.DB2.130306094532      UOWWAIT         2013-03-06-23.36.55.997962            25 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55334 100.159.65.4.55535.130306094 UOWWAIT         2013-03-06-23.35.00.155727            21 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55347 100.159.65.4.55612.130306094 UOWWAIT         2013-03-06-23.30.00.209638            21 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55662 100.159.65.4.57212.130306220 UOWWAIT         2013-03-06-23.30.00.210223            21 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55458 100.159.65.4.56161.130306140 UOWWAIT         2013-03-06-23.35.53.351758            28 sigma-dev-app01
WSCOMUSR   db2fw0                              55339 *LOCAL.DB2.130306094536      UOWWAIT         2013-03-06-04.45.32.972073            48 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55661 100.159.65.4.57211.130306220 UOWWAIT         2013-03-06-23.30.00.206092            21 sigma-dev-app01
WSCOMUSR   db2lused                            55338 *LOCAL.DB2.130306094535      UOWWAIT         2013-03-06-23.30.58.412692            23 sigma-dev-app01
WSCOMUSR   db2wlmd                             55337 *LOCAL.DB2.130306094534      CONNECTED       2013-03-06-04.45.32.830828            48 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55343 100.159.65.4.55607.130306094 UOWWAIT         2013-03-06-23.35.00.162591            21 sigma-dev-app01
WSCOMUSR   db2taskd                            55336 *LOCAL.DB2.130306094533      UOWWAIT         2013-03-06-23.35.55.966370            25 sigma-dev-app01
WSCOMUSR   db2jcc_application                  55737 100.159.65.4.57593.130307010 UOWWAIT         2013-03-06-23.30.00.208060            21 sigma-dev-app01

  14 record(s) selected.

I couldn’t find a way to get the status change time without going to SYSIBMADM.APPLICATIONS (or one of the SNAP APPL INFO variants). If you find one, please share it in the comments.

One drawback here is that you’re only looking at the connections for the database you’re connected to, while LIST APPLICATIONS lets you see connections for all databases on the current instance. Notice also that SYSIBMADM.APPLICATIONS includes the “internal” connections that started showing up in list applications in DB2 version 9.

• Author Details

Ember Crooks

https://datageek.blog/

ember.crooks@gmail.com

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home