If you’re mourning the loss of “RESET MONITOR ALL”, there are a number of ways you can address that. Ideally, you’d use this approach: http://www.ibm.com/developerworks/data/library/techarticle/dm-1009db2monitoring1/, but there are some arguments for using MONREPORT over that in some ways. One argument is that MONREPORT is built-in and you don’t have to add objects to make it work. The disadvantages are a bit less flexibility and the inability to use SQL to capture information. Though if it’s the old snapshot interfaces you miss, then the output from MONREPORT is more similar in a lot of ways.
I talk as though the snapshot monitors no longer exist – they do, but he lower overhead of more recent monitoring tools is worth leaving them behind.
What is MONREPORT
MONREPORT is a built-in module for DB2 LUW, starting in version 9.7. (A module is a group of related stored procedures and other objects) Most of the MONREPORT procedures take an input parameter of a number of seconds. They then take note of the values for a number of performance related metrics and sleep for the specified number of seconds. Then the metrics are captured again, and the before values are subtracted from the after values to show only the numbers represented by what happened in the specified time interval. An easily human-readable report is then generated showing the metrics during the specified interval.
This is useful because simply querying the MON_GET table functions and SYSIBMADM views/tables will get you just the values since the last database restart. Sometimes you want to do analyses based on everything that has happened since the last database restart. Other times you really need to know what’s happening in a particular 5 minute or even one-hour interval.
MONREPORT is generally focused on database performance, though in several areas. In DB2 10.5, the following stored procedures can be called to get informaion from MONREPORT:
- DBSUMMARY – provides values for key performance indicators across the database. Optional input is simply the monitoring interval. See the IBM DB2 Knowledge Center entry on DBSUMMARY for more details.
- CONNECTION – provides values for preformance indicators specific to each connection. Input is the monitoring interval and optionally the application handle of a single connection if you want information only returned for one connection. See the IBM DB2 Knowledge Center entry on CONNECTION for more details.
- CURRENTAPPS – provides point-in-time values for performance indicators for applications at the moment it is run. No inputs. See the IBM DB2 Knowledge Center entry on CURRENTAPPS for more details.
- CURRENTSQL – provides details on current SQL executing – including it’s own SQL. Optional input of the member is only valid in a PureScale environment. See the IBM DB2 Knowledge Center entry on CURRENTSQL for more details.
- LOCKWAIT – provides details on lockwaits currently in progress. No inputs. See the IBM DB2 Knowledge Center entry on LOCKWAIT for more details.
- PKGCACHE – provides details on statements in the package cache. Note this is not my favorite way of accessing package cache information (MON_GET_PKG_CACHE_STMT is). Inputs are optional and include: an interval in which the statements in the package cache have been updated, a section type if you want to limit data to either dynamic or static SQL, and a member if you’re using PureScale. See the IBM DB2 Knowledge Center entry on PKGCACHE for more details.
How to use MONREPORT
I’m going to cover the use of DBSUMMARY. The others are similar. To use an interval of 60 seconds, use this syntax:
db2 "CALL MONREPORT.DBSUMMARY(60)"
If you think it seems to be hanging, make sure you specified the correct interval. For all of the MONREPORT procedures that take an interval, except for the PKGCACHE one, it will take a bit more than the interval you’ve specified to return the metrics.
This is a sample of the output:
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 10.5.4
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
$ db2 "call monreport.dbsummary(60)"
Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: SAMPLE
Generated: 11/24/2014 19:10:03
Interval monitored: 60
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 0
ACT_COMPLETED_TOTAL 4 271
APP_RQSTS_COMPLETED_TOTAL 0 5
TOTAL_CPU_TIME = 48751
TOTAL_CPU_TIME per request = 9750
Row processing
ROWS_READ/ROWS_RETURNED = 0 (73/271)
ROWS_MODIFIED = 0
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 0 137/60508
For activities 0 168/60532
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 1944
CLIENT_IDLE_WAIT_TIME per second = 32
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 137
I/O wait time
POOL_READ_TIME 0 0
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 43 59
DIRECT_WRITE_TIME 0 0
LOG_DISK_WAIT_TIME 0 0
LOCK_WAIT_TIME 0 0
AGENT_WAIT_TIME 0 0
Network and FCM
TCPIP_SEND_WAIT_TIME 0 0
TCPIP_RECV_WAIT_TIME 0 0
IPC_SEND_WAIT_TIME 0 0
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 0
FCM_RECV_WAIT_TIME 0 0
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 0 0
RECLAIM_WAIT_TIME 0 0
SMP_RECLAIM_WAIT_TIME 0 0
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 60371
Section execution
TOTAL_SECTION_PROC_TIME 0 263
TOTAL_SECTION_SORT_PROC_TIME 0 9
Compile
TOTAL_COMPILE_PROC_TIME 0 3
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 0
TOTAL_ROLLBACK_PROC_TIME 0 0
Utilities
TOTAL_RUNSTATS_PROC_TIME 0 0
TOTAL_REORGS_PROC_TIME 0 0
TOTAL_LOAD_PROC_TIME 0 0
Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios
Type Ratio Formula
--------------- --------------- ----------------------------------------------
Data 100 (1-(0+0-0)/(28+0))
Index 100 (1-(0+0-0)/(24+0))
XDA 0 (1-(0+0-0)/(0+0))
COL 0 (1-(0+0-0)/(0+0))
LBP Data 100 (28-0)/(28+0)
LBP Index 0 (0-0)/(24+0)
LBP XDA 0 (0-0)/(0+0)
LBP COL 0 (0-0)/(0+0)
GBP Data 0 (0 - 0)/0
GBP Index 0 (0 - 0)/0
GBP XDA 0 (0 - 0)/0
GBP COL 0 (0 - 0)/0
I/O
--------------------------------------------------------------------------------
Buffer pool reads
POOL_DATA_L_READS = 28
POOL_TEMP_DATA_L_READS = 0
POOL_DATA_P_READS = 0
POOL_TEMP_DATA_P_READS = 0
POOL_ASYNC_DATA_READS = 0
POOL_INDEX_L_READS = 24
POOL_TEMP_INDEX_L_READS = 0
POOL_INDEX_P_READS = 0
POOL_TEMP_INDEX_P_READS = 0
POOL_ASYNC_INDEX_READS = 0
POOL_XDA_L_READS = 0
POOL_TEMP_XDA_L_READS = 0
POOL_XDA_P_READS = 0
POOL_TEMP_XDA_P_READS = 0
POOL_ASYNC_XDA_READS = 0
POOL_COL_L_READS = 0
POOL_TEMP_COL_L_READS = 0
POOL_COL_P_READS = 0
POOL_TEMP_COL_P_READS = 0
POOL_ASYNC_COL_READS = 0
Buffer pool pages found
POOL_DATA_LBP_PAGES_FOUND = 28
POOL_ASYNC_DATA_LBP_PAGES_FOUND = 0
POOL_INDEX_LBP_PAGES_FOUND = 0
POOL_ASYNC_INDEX_LBP_PAGES_FOUND = 0
POOL_XDA_LBP_PAGES_FOUND = 0
POOL_ASYNC_XDA_LBP_PAGES_FOUND = 0
POOL_COL_LBP_PAGES_FOUND = 0
POOL_ASYNC_COL_LBP_PAGES_FOUND = 0
Buffer pool writes
POOL_DATA_WRITES = 0
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 592
DIRECT_READ_REQS = 16
DIRECT_WRITES = 0
DIRECT_WRITE_REQS = 0
Log I/O
LOG_DISK_WAITS_TOTAL = 0
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 0 0
LOCK_WAITS 0 0
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
Routines
--------------------------------------------------------------------------------
Per activity Total
------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS 1 290
TOTAL_ROUTINE_TIME 222 60271
TOTAL_ROUTINE_TIME per invocation = 207
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 4
SORT_OVERFLOWS = 0
POST_THRESHOLD_SORTS = 0
POST_SHRTHRESHOLD_SORTS = 0
Network
--------------------------------------------------------------------------------
Communications with remote clients
TCPIP_SEND_VOLUME per send = 0 (0/0)
TCPIP_RECV_VOLUME per receive = 0 (0/0)
Communications with local clients
IPC_SEND_VOLUME per send = 182 (365/2)
IPC_RECV_VOLUME per receive = 231 (694/3)
Fast communications manager
FCM_SEND_VOLUME per send = 0 (0/0)
FCM_RECV_VOLUME per receive = 0 (0/0)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 2
PKG_CACHE_INSERTS = 15
PKG_CACHE_LOOKUPS = 16
Catalog cache
CAT_CACHE_INSERTS = 5
CAT_CACHE_LOOKUPS = 9
Transaction processing
TOTAL_APP_COMMITS = 0
INT_COMMITS = 0
TOTAL_APP_ROLLBACKS = 0
INT_ROLLBACKS = 0
Log buffer
NUM_LOG_BUFFER_FULL = 0
Activities aborted/rejected
ACT_ABORTED_TOTAL = 0
ACT_REJECTED_TOTAL = 0
Workload management controls
WLM_QUEUE_ASSIGNMENTS_TOTAL = 0
WLM_QUEUE_TIME_TOTAL = 0
DB2 utility operations
--------------------------------------------------------------------------------
TOTAL_RUNSTATS = 0
TOTAL_REORGS = 0
TOTAL_LOADS = 0
================================================================================
Part 2 - Application performance drill down
Application performance database-wide
--------------------------------------------------------------------------------
TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
per request WAIT_TIME % COMMITS ROWS_MODIFIED
---------------------- ----------- ------------- ----------------------------
9750 0 0 73
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
74 9750 0 0 73
Application performance by service class
--------------------------------------------------------------------------------
SERVICE_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
CLASS_ID per request WAIT_TIME % COMMITS ROWS_MODIFIED
-------- ------------------- ----------- ------------- -------------
4 0 0 0 0
11 0 0 0 0
12 0 0 0 0
13 9750 0 0 73
Application performance by workload
--------------------------------------------------------------------------------
WORKLOAD_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
NAME per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ---------------------- ----------- ------------- -------------
SYSDEFAULTADM 0 0 0 0
SYSDEFAULTUSE 9750 0 0 73
================================================================================
Part 3 - Member level information
- I/O wait time is
(POOL_READ_TIME + POOL_WRITE_TIME + DIRECT_READ_TIME + DIRECT_WRITE_TIME).
TOTAL_CPU_TIME TOTAL_ RQSTS_COMPLETED_ I/O
MEMBER per request WAIT_TIME % TOTAL wait time
------ ---------------------- ----------- ---------------- -----------------
0 9750 0 5 279
267 record(s) selected.
Return Status = 0
I am not covering the output in detail here, because explaining it would be very much more in-depth than I want to get. Sample output for most of the procedures is available at the IBM DB2 Knowledge Center links supplied above.
Impact
While nearly all monitoring has some small impact, it’s on the lower side here since the stored procedures are mostly using the lowest available impacting monitoring interfaces of the MON_GET table functions. There must be some overhead in display and simple subtraction, but it must be less invasive than the old snapshot monitoring interfaces.
[…] you’re not familiar with MONREPORT, see my DB2 Basics entry on MONREPORT […]
This was very helpful, thanks!