CONNECTION procedure - Generate a report on connection metrics
The CONNECTION procedure gathers monitor data for each connection and produces a text-formatted report.
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 table functions, waits 30 seconds and calls the table functions again. The routine 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).
- application_handle
- An optional input argument of type BIGINT that specifies an application handle that identifies a connection. If the application_handle argument is not specified (or if null is specified), the report includes a section for each connection. The default is null.
Authorization
The following privilege is
required:
- EXECUTE privilege on the MONREPORT module
Examples
The following examples demonstrate various ways to call the CONNECTION procedure.
This example
produces a report for all connections, with data displayed corresponding
to an interval of 30 seconds:
call monreport.connection(30);
This
example produces a report for a connection with an application handle
of 34. Data is displayed based on absolute totals accumulated in the
source table functions (rather than based on the current interval):
call monreport.connection(0, 34);
This
next example produces a report for a connection with an application
handle of 34. Data is displayed corresponding to an interval of 10
seconds.
call monreport.connection(DEFAULT, 34);
The
final example produces the default report: for all connections, with
data displayed corresponding to an interval of 10 seconds:
call monreport.connection;
Here
is an example of the report output for the default procedure call
(all connections, 10 second interval): 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