The CONNECTION procedure gathers monitor data for each connection and produces a text-formatted report.
The CONNECTION procedure is available starting with DB2® Version 9.7 Fix Pack 1.
call monreport.connection;
call monreport.connection(30);
call monreport.connection(0, 34);
call monreport.connection(DEFAULT, 34);
--------------------------------------------------------------------------------
Monitoring report - connection
--------------------------------------------------------------------------------
Database: WLMDISP1
Generated: 07/09/2010 12:50:58
Interval monitored: 10
-- Command options --
APPLICATION_HANDLE: All
================================================================================
Part 1 - Summary of connections
--------------------------------------------------------------------------------
APPLICATION TOTAL_ TOTAL_ ACT_COMPLETED TOTAL_WAIT CLIENT_IDLE
# _HANDLE CPU_TIME ACT_TIME _TOTAL _TIME _WAIT_TIME
--- ----------- ------------ -------- ------------- ---------- -----------
1 656020 0 0 0 0 0
2 1310972 447 52006 0 30000 0
3 1310992 702356 89716 0 77893 0
4 1311033 0 0 0 0 0
5 1311047 2006289 692031 0 0 0
6 1311113 52 30000 0 30000 0
7 1311151 719234 11118 0 0 0
8 1311154 0 0 0 0 0
9 1311172 0 0 0 0 0
10 1311173 6838157 798434 0 30131 0
11 1311194 1519805 69075 0 61599 0
12 1311195 51475 680696 0 218147 0
...
6487 record(s) selected.
Return Status = 0
================================================================================
Part 2 - Details for each connection
...
connection #:12
--------------------------------------------------------------------------------
--Connection identifiers--
Application identifiers
APPLICATION_HANDLE = 1311195
APPLICATION_NAME = db2jcc_application
APPLICATION_ID = *N10.svtpdb3.1007091632196
Authorization IDs
SYSTEM_AUTHID = SVTPDB3
SESSION_AUTHID = SVTPDB3
Client attributes
CLIENT_ACCTNG =
CLIENT_USERID =
CLIENT_APPLNAME =
CLIENT_WRKSTNNAME = rundown.torolab.ibm.com
CLIENT_PID = 0
CLIENT_PRDID = JCC03580
CLIENT_PLATFORM = AIX64
CLIENT_PROTOCOL = LOCAL
-- Other connection details --
CONNECTION_START_TIME = 2010-07-09-12.32.13.342062
NUM_LOCKS_HELD = 346
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 0
ACT_COMPLETED_TOTAL 0 0
APP_RQSTS_COMPLETED_TOTAL 0 0
TOTAL_CPU_TIME = 51475
TOTAL_CPU_TIME per request = 0
Row processing
ROWS_READ/ROWS_RETURNED = 0 (476/0)
ROWS_MODIFIED = 0
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 32 218147/680697
For activities 32 218147/680696
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 0
CLIENT_IDLE_WAIT_TIME per second = 0
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 218147
I/O wait time
POOL_READ_TIME 0 39
POOL_WRITE_TIME 0 0
DIRECT_READ_TIME 0 0
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 71 156689
FCM_RECV_WAIT_TIME 28 61419
WLM_QUEUE_TIME_TOTAL 0 0
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 462550
Section execution
TOTAL_SECTION_PROC_TIME 100 462550
TOTAL_SECTION_SORT_PROC_TIME 0 5
Compile
TOTAL_COMPILE_PROC_TIME 0 0
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 Reads (Logical/Physical)
--------------- --------------- ----------------------------------------------
Data 63 58/21
Index 0 0/0
XDA 0 0/0
Temp data 0 0/0
Temp index 0 0/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 = 0
DIRECT_READ_REQS = 0
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 0 0
TOTAL_ROUTINE_TIME 0 0
TOTAL_ROUTINE_TIME per invocation = 0
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 0
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 = 0 (0/0)
IPC_RECV_VOLUME per receive = 0 (0/0)
Fast communications manager
FCM_SEND_VOLUME per send = 2898 (124633/43)
FCM_RECV_VOLUME per receive = 4079 (40798/10)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 0
PKG_CACHE_INSERTS = 0
PKG_CACHE_LOOKUPS = 0
Catalog cache
CAT_CACHE_INSERTS = 0
CAT_CACHE_LOOKUPS = 0
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