DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The DBSUMMARY procedure generates a text-formatted monitoring report that summarizes system and application performance metrics.
The DBSUMMARY procedure is available starting with DB2® Version 9.7 Fix Pack 1.
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.
call monreport.dbsummary; call monreport.dbsummary(30); --------------------------------------------------------------------------------
Monitoring report - database summary
--------------------------------------------------------------------------------
Database: WLMDISP1
Generated: 07/09/2010 12:50:38
Interval monitored: 10
================================================================================
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 9
ACT_COMPLETED_TOTAL 3 32
APP_RQSTS_COMPLETED_TOTAL 4 49
TOTAL_CPU_TIME = 11569133
TOTAL_CPU_TIME per request = 236104
Row processing
ROWS_READ/ROWS_RETURNED = 66318 (397908/6)
ROWS_MODIFIED = 70076
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 96 121396671/125265175
For activities 96 121387234/125211126
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 713
CLIENT_IDLE_WAIT_TIME per second = 71
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 121396671
I/O wait time
POOL_READ_TIME 0 3428
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 0 624
DIRECT_WRITE_TIME 0 112
LOG_DISK_WAIT_TIME 0 763
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 2
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 372361
FCM_RECV_WAIT_TIME 99 121019382
WLM_QUEUE_TIME_TOTAL 0 0
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 3868504
Section execution
TOTAL_SECTION_PROC_TIME 98 3813875
TOTAL_SECTION_SORT_PROC_TIME 0 180
Compile
TOTAL_COMPILE_PROC_TIME 0 33621
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 0 20
TOTAL_ROLLBACK_PROC_TIME 0 13
Utilities
TOTAL_RUNSTATS_PROC_TIME 0 51
TOTAL_REORGS_PROC_TIME 0 0
TOTAL_LOAD_PROC_TIME 0 0
Buffer pool
--------------------------------------------------------------------------------
Buffer pool hit ratios
Type Ratio Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data 99 34870/144
Index 99 14104/19
XDA 0 0/0
Temp data 99 4031/5
Temp index 100 501/0
Temp XDA 0 0/0
I/O
--------------------------------------------------------------------------------
Buffer pool writes
POOL_DATA_WRITES = 0
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
Direct I/O
DIRECT_READS = 2114
DIRECT_READ_REQS = 82
DIRECT_WRITES = 754
DIRECT_WRITE_REQS = 23
Log I/O
LOG_DISK_WAITS_TOTAL = 82
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 0 1
TOTAL_ROUTINE_TIME 318 10178
TOTAL_ROUTINE_TIME per invocation = 10178
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 73
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 = 342 (16440/48)
IPC_RECV_VOLUME per receive = 674 (33050/49)
Fast communications manager
FCM_SEND_VOLUME per send = 1386 (8711447/6283)
FCM_RECV_VOLUME per receive = 1325 (5707159/4307)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 17
PKG_CACHE_INSERTS = 76
PKG_CACHE_LOOKUPS = 437
Catalog cache
CAT_CACHE_INSERTS = 64
CAT_CACHE_LOOKUPS = 404
Transaction processing
TOTAL_APP_COMMITS = 9
INT_COMMITS = 3
TOTAL_APP_ROLLBACKS = 6
INT_ROLLBACKS = 0
Log buffer
NUM_LOG_BUFFER_FULL = 0
Activities aborted/rejected
ACT_ABORTED_TOTAL = 1
ACT_REJECTED_TOTAL = 0
Workload management controls
WLM_QUEUE_ASSIGNMENTS_TOTAL = 0
WLM_QUEUE_TIME_TOTAL = 0
DB2 utility operations
--------------------------------------------------------------------------------
TOTAL_RUNSTATS = 2
TOTAL_REORGS = 0
TOTAL_LOADS = 0
...
259 record(s) selected.
Return Status = 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
---------------------- ----------- ------------- ----------------------------
236104 96 9 467984
Application performance by connection
--------------------------------------------------------------------------------
APPLICATION_ TOTAL_CPU_TIME TOTAL_ TOTAL_APP_ ROWS_READ +
HANDLE per request WAIT_TIME % COMMITS ROWS_MODIFIED
------------- ------------------- ----------- ------------- -------------
656020 0 0 0 0
1310972 0 0 0 0
1310992 0 67 0 59488
1311033 0 98 0 712152
1311047 0 4 0 0
1311113 0 1 0 58199
1311151 0 0 0 218
1311154 0 0 0 0
1311172 0 0 0 0
1311173 0 0 0 0
1311194 0 98 0 3129
1311195 0 0 0 0
1311216 0 4 0 0
1311223 0 100 0 0
1311224 0 0 0 0
1311230 0 1 0 1308
1311232 0 100 0 0
1311234 0 0 0 0
1311245 0 0 0 0
1311247 1799 44 0 0
1311250 0 0 0 0
1311252 27565 78 1 35
1311256 0 100 0 0
1311257 0 0 0 0
1311258 0 1 0 500
1311262 0 0 0 0
1311266 0 0 0 0
1311276 0 0 0 0
1311278 0 0 0 0
1311279 0 0 0 136755
1311281 0 0 0 0
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 267301 97 8 469839
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 341591 94 8 972780
================================================================================
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
------ ---------------------- ----------- ---------------- -----------------
10 10448 97 318 2101
20 30267 96 218 715
30 9661 97 155 148
40 6353 97 168 259