DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
The CONNECTION procedure gathers monitor data for each connection and produces a text-formatted report.
The following examples demonstrate various ways to call the CONNECTION procedure.
call monreport.connection(30); call monreport.connection(0, 34);call monreport.connection(DEFAULT, 34);call monreport.connection; Result set 1
--------------
TEXT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Monitoring report - connection
--------------------------------------------------------------------------------
Database: SAMPLE
Generated: 04/06/2010 13:36:52
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 180 0 0 0 0 0
2 65711 116307 675 1 410 9884
3 131323 116624 679 1 717 12895
================================================================================
Part 2 - Details for each connection
connection #:1
--------------------------------------------------------------------------------
--Connection identifiers--
Application identifiers
APPLICATION_HANDLE = 180
APPLICATION_NAME = db2bp
APPLICATION_ID = *N0.jwr.100406173420
Authorization IDs
SYSTEM_AUTHID = JWR
SESSION_AUTHID = JWR
Client attributes
CLIENT_ACCTNG =
CLIENT_USERID =
CLIENT_APPLNAME =
CLIENT_WRKSTNNAME =
CLIENT_PID = 29987
CLIENT_PRDID = SQL09081
CLIENT_PLATFORM = LINUXX8664
CLIENT_PROTOCOL = LOCAL
-- Other connection details --
CONNECTION_START_TIME = 2010-04-06-13.34.20.635181
NUM_LOCKS_HELD = 9
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 = 0
TOTAL_CPU_TIME per request = 0
Row processing
ROWS_READ/ROWS_RETURNED = 0 (0/0)
ROWS_MODIFIED = 0
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 0 0/0
For activities 0 0/0
-- 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 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 0
Section execution
TOTAL_SECTION_PROC_TIME 0 0
TOTAL_SECTION_SORT_PROC_TIME 0 0
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 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 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 = 0 (0/0)
FCM_RECV_VOLUME per receive = 0 (0/0)
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
connection #:2
--------------------------------------------------------------------------------
--Connection identifiers--
Application identifiers
APPLICATION_HANDLE = 65711
APPLICATION_NAME = db2bp
APPLICATION_ID = *N1.jwr.100406173430
Authorization IDs
SYSTEM_AUTHID = JWR
SESSION_AUTHID = JWR
Client attributes
CLIENT_ACCTNG =
CLIENT_USERID =
CLIENT_APPLNAME =
CLIENT_WRKSTNNAME =
CLIENT_PID = 30044
CLIENT_PRDID = SQL09081
CLIENT_PLATFORM = LINUXX8664
CLIENT_PROTOCOL = LOCAL
-- Other connection details --
CONNECTION_START_TIME = 2010-04-06-13.34.31.058344
NUM_LOCKS_HELD = 0
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 1
ACT_COMPLETED_TOTAL 0 1
APP_RQSTS_COMPLETED_TOTAL 0 2
TOTAL_CPU_TIME = 116307
TOTAL_CPU_TIME per request = 58153
Row processing
ROWS_READ/ROWS_RETURNED = 0 (8/0)
ROWS_MODIFIED = 5
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 58 410/696
For activities 58 398/675
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 9884
CLIENT_IDLE_WAIT_TIME per second = 988
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 410
I/O wait time
POOL_READ_TIME 5 23
POOL_WRITE_TIME 28 116
DIRECT_READ_TIME 0 1
DIRECT_WRITE_TIME 0 4
LOG_DISK_WAIT_TIME 11 48
LOCK_WAIT_TIME 2 11
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 1
IPC_RECV_WAIT_TIME 0 0
FCM_SEND_WAIT_TIME 0 0
FCM_RECV_WAIT_TIME 1 5
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 17 73
RECLAIM_WAIT_TIME 23 96
SMP_RECLAIM_WAIT_TIME 4 20
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 286
Section execution
TOTAL_SECTION_PROC_TIME 96 276
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 0 2
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 1 4
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 91 72/6
Index 100 46/0
XDA 0 0/0
COL 0 0/0
Temp data 0 0/0
Temp index 0 0/0
Temp XDA 0 0/0
Temp COL 0 0/0
GBP Data 60 (10 - 6)/10
GBP Index 0 (8 - 0)/8
GBP COL 0 (0 - 0)/0
LBP Data 52 (34 - 0)/72
LBP Index 0 (46 - 0)/46
LBP COL 0 (0 - 0)/(0 + 0)
I/O
--------------------------------------------------------------------------------
Buffer pool writes
POOL_DATA_WRITES = 36
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 0
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 1
DIRECT_READ_REQS = 1
DIRECT_WRITES = 4
DIRECT_WRITE_REQS = 1
Log I/O
LOG_DISK_WAITS_TOTAL = 13
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 11 11
LOCK_WAITS 100 1
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 = 54 (108/2)
IPC_RECV_VOLUME per receive = 69 (138/2)
Fast communications manager
FCM_SEND_VOLUME per send = 0 (0/0)
FCM_RECV_VOLUME per receive = 432 (2592/6)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 1
PKG_CACHE_INSERTS = 2
PKG_CACHE_LOOKUPS = 2
Catalog cache
CAT_CACHE_INSERTS = 3
CAT_CACHE_LOOKUPS = 8
Transaction processing
TOTAL_APP_COMMITS = 1
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
connection #:3
--------------------------------------------------------------------------------
--Connection identifiers--
Application identifiers
APPLICATION_HANDLE = 131323
APPLICATION_NAME = db2bp
APPLICATION_ID = *N2.jwr.100406173452
Authorization IDs
SYSTEM_AUTHID = JWR
SESSION_AUTHID = JWR
Client attributes
CLIENT_ACCTNG =
CLIENT_USERID =
CLIENT_APPLNAME =
CLIENT_WRKSTNNAME =
CLIENT_PID = 30510
CLIENT_PRDID = SQL09081
CLIENT_PLATFORM = LINUXX8664
CLIENT_PROTOCOL = LOCAL
-- Other connection details --
CONNECTION_START_TIME = 2010-04-06-13.34.52.398427
NUM_LOCKS_HELD = 0
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 1
ACT_COMPLETED_TOTAL 0 1
APP_RQSTS_COMPLETED_TOTAL 0 2
TOTAL_CPU_TIME = 116624
TOTAL_CPU_TIME per request = 58312
Row processing
ROWS_READ/ROWS_RETURNED = 0 (18/0)
ROWS_MODIFIED = 4
Wait times
--------------------------------------------------------------------------------
-- Wait time as a percentage of elapsed time --
% Wait time/Total time
--- ----------------------------------
For requests 82 717/864
For activities 80 549/679
-- Time waiting for next client request --
CLIENT_IDLE_WAIT_TIME = 12895
CLIENT_IDLE_WAIT_TIME per second = 1289
-- Detailed breakdown of TOTAL_WAIT_TIME --
% Total
--- ---------------------------------------------
TOTAL_WAIT_TIME 100 717
I/O wait time
POOL_READ_TIME 2 16
POOL_WRITE_TIME 18 136
DIRECT_READ_TIME 0 3
DIRECT_WRITE_TIME 0 2
LOG_DISK_WAIT_TIME 10 77
LOCK_WAIT_TIME 3 27
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 21 157
WLM_QUEUE_TIME_TOTAL 0 0
CF_WAIT_TIME 9 66
RECLAIM_WAIT_TIME 12 92
SMP_RECLAIM_WAIT_TIME 16 119
Component times
--------------------------------------------------------------------------------
-- Detailed breakdown of processing time --
% Total
---------------- --------------------------
Total processing 100 147
Section execution
TOTAL_SECTION_PROC_TIME 89 131
TOTAL_SECTION_SORT_PROC_TIME 0 0
Compile
TOTAL_COMPILE_PROC_TIME 4 6
TOTAL_IMPLICIT_COMPILE_PROC_TIME 0 0
Transaction end processing
TOTAL_COMMIT_PROC_TIME 1 2
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 91 47/4
Index 100 78/0
XDA 0 0/0
COL 0 0/0
Temp data 0 0/0
Temp index 0 0/0
Temp XDA 0 0/0
Temp COL 0 0/0
GBP Data 26 (15 - 4)/15
GBP Index 0 (9 - 0)/9
GBP COL 0 (0 - 0)/0
LBP Data 6 (44 - 0)/47
LBP Index 48 (40 - 0)/78
LBP COL 0 (0 - 0)/(0 + 0)
I/O
--------------------------------------------------------------------------------
Buffer pool writes
POOL_DATA_WRITES = 3
POOL_XDA_WRITES = 0
POOL_INDEX_WRITES = 35
POOL_COL_WRITES = 0
Direct I/O
DIRECT_READS = 15
DIRECT_READ_REQS = 4
DIRECT_WRITES = 6
DIRECT_WRITE_REQS = 1
Log I/O
LOG_DISK_WAITS_TOTAL = 18
Locking
--------------------------------------------------------------------------------
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 27 27
LOCK_WAITS 200 2
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 = 1
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 = 54 (108/2)
IPC_RECV_VOLUME per receive = 73 (146/2)
Fast communications manager
FCM_SEND_VOLUME per send = 0 (0/0)
FCM_RECV_VOLUME per receive = 1086 (10864/10)
Other
--------------------------------------------------------------------------------
Compilation
TOTAL_COMPILATIONS = 1
PKG_CACHE_INSERTS = 2
PKG_CACHE_LOOKUPS = 2
Catalog cache
CAT_CACHE_INSERTS = 0
CAT_CACHE_LOOKUPS = 9
Transaction processing
TOTAL_APP_COMMITS = 1
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
628 record(s) selected.
Return Status = 0