DBSUMMARY procedure - Generate a summary report of system and application performance metrics
The DBSUMMARY procedure generates a text-formatted monitoring report that summarizes system and application performance metrics.
The DB Summary report contains in-depth monitor data for the entire database as well as key performance indicators for each connection, workload, service class, and database member.
Syntax
Parameters
- monitoring_interval
- An optional input argument of type INTEGER that specifies the duration in seconds that monitoring data is collected before it is reported. For example, if you specify a monitoring interval of 30, the routine calls the table functions, waits 30 seconds and then calls the table functions again. The DBSUMMARY procedure then calculates the difference, which reflects changes during the interval. If the monitoring_interval argument is not specified (or if null is specified), the default value is 10. The range of valid inputs are the integer values 0-3600 (that is, up to 1 hour).
Authorization
The following privilege is
required:
- EXECUTE privilege on the MONREPORT module
Examples
The following examples demonstrate various ways to call the DBSUMMARY procedure.
The first example
produces a report that displays data corresponding to an interval
of 30 seconds.
call monreport.dbsummary(30);The
next example produces a report that displays data corresponding to
an interval of 10 seconds (the default value):
call monreport.dbsummary; Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: SAMPLE
Generated: 04/06/2010 13:35:24
Interval monitored: 10
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 2
ACT_COMPLETED_TOTAL 0 9
APP_RQSTS_COMPLETED_TOTAL 0 6
TOTAL_CPU_TIME = 2649800
TOTAL_CPU_TIME per request = 441633
Row processing
ROWS_READ/ROWS_RETURNED = 97 (685/7)
ROWS_MODIFIED = 117
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 19 3434/17674
For activities 10 1203/11613
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 70566
CLIENT_IDLE_WAIT_TIME per second = 7056
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 3434
I/O wait time
POOL_READ_TIME 23 805
POOL_WRITE_TIME 8 280
DIRECT_READ_TIME 3 131
DIRECT_WRITE_TIME 3 104
LOG_DISK_WAIT_TIME 10 344
LOCK_WAIT_TIME 0 18
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 6 212
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 32 1101
RECLAIM_WAIT_TIME 2 98
SMP_RECLAIM_WAIT_TIME 3 118
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 14240
Section execution
TOTAL_SECTION_PROC_TIME 2 365
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 0 17
TOTAL_IMPLICIT_COMPILE_PROC_TIME 2 294
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 36
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)/(27+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 (27-0)/(27+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 = 27
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 = 27
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 = 620
DIRECT_READ_REQS = 15
DIRECT_WRITES = 0
DIRECT_WRITE_REQS = 0
Log I/O
LOG_DISK_WAITS_TOTAL = 0
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 2 18
LOCK_WAITS 22 2
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
Routines
--------------------------------------------------------------------------------
Per activity Total
------------------------ ------------------------
TOTAL_ROUTINE_INVOCATIONS 0 1
TOTAL_ROUTINE_TIME 1117 10058
TOTAL_ROUTINE_TIME per invocation = 10058
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 5
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 = 137 (1101/8)
IPC_RECV_VOLUME per receive = 184 (1106/6)
Fast communications manager
FCM_SEND_VOLUME per send = 3475 (31277/9)
FCM_RECV_VOLUME per receive = 2433 (131409/54)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 4
PKG_CACHE_INSERTS = 11
PKG_CACHE_LOOKUPS = 13
Catalog cache
CAT_CACHE_INSERTS = 74
CAT_CACHE_LOOKUPS = 112
Transaction processing
TOTAL_APP_COMMITS = 2
INT_COMMITS = 2
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
---------------------- ----------- ------------- ----------------------------
441633 19 2 802
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
180 0 0 0 0
65711 495970 46 1 566
131323 324379 43 1 222
Application performance by service class
--------------------------------------------------------------------------------
SERVICE_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
CLASS_ID per request WAIT_TIME % COMMITS ROWS_MODIFIED
-------- ------------------- ----------- ------------- -------------
11 0 0 0 0
12 0 0 0 0
13 440427 19 2 802
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 410174 45 2 788
================================================================================
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 17804 0 9 10
1 108455 47 14 866
2 74762 41 13 441
267 record(s) selected.
Return Status = 0