Throughput / Time Spent
Purpose
Timespent view shows a breakdown of where the monitored database is spending time, broken down to the full granularity provided by DB2.
—
This view shows where the database is spending its time. You should be able to see at a glance if excessive time is being spent in non-productive areas, such as waiting on locks or writing to event monitors. There are some uncommon but important scenarios that are difficult to diagnose other than by noticing that the server is spending time on something strange.
DB2 organizes the monitor elements for time spent in a hierarchy. For example, there is a monitor element for time spent waiting, which is broken down into many individual elements covering the various things that activities wait for, such as locks, prefetchers, logging, etc. The particular hierarchy shown by dsmtop is the “System Dimension” one, documented here:
For each element shown, we show what percentage it constitutes of each of its parent element. In the current implementation, we present only a summarized view across all members, which means that the stats for the Fast Communication Subsystem (FCM), which routes data and requests between members, is excluded because there is no meaningful aggregation possible for these monitor elements.
Nesting (position in the hierarchy) is shown by indenting the names of the time monitor elements.
The last column shows the raw values for the elements. So if the display mode is “count”, this is the number of milliseconds the data server spent in that category since it was activated.
Screenshot
Metrics Shown
Followings are the common variables that are used in denominator to calculate percentage:
CLIENT_IDLE_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(CLIENT_IDLE_WAIT_TIME)
TOTAL_RQST_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_RQST_TIME)
Client idle wait time
The percentage of time spent on waiting for the client to send its next request.
= CLIENT_IDLE_WAIT_TIME / (CLIENT_IDLE_WAIT_TIME + TOTAL_RQST_TIME)
Total request time
The percentage of time spent on working on requests.
= TOTAL_RQST_TIME / (CLIENT_IDLE_WAIT_TIME + TOTAL_RQST_TIME)
Total wait time
The percentage of total time spent waiting within the database server.
= TOTAL_WAIT_TIME / TOTAL_RQST_TIME
Source:
TOTAL_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_WAIT_TIME)
Agent wait time
The percentage of time spent by an application queued to wait for an agent under concentrator configurations.
= AGENT_WAIT_TIME / TOTAL_RQST_TIME
Source:
AGENT_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(AGENT_WAIT_TIME)
Workload manager total queue time
The percentage of time spent waiting on a WLM queuing threshold.
= WLM_QUEUE_TIME_TOTAL / TOTAL_RQST_TIME
Source:
WLM_QUEUE_TIME_TOTAL = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(WLM_QUEUE_TIME_TOTAL)
Time waited on locks
The percentage of time spent waiting for locks.
= LOCK_WAIT_TIME / TOTAL_RQST_TIME
Source:
LOCK_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOCK_WAIT_TIME)
Lock wait time global
The percentage of time spent on global lock waits.
= LOCK_WAIT_TIME_GLOBAL / TOTAL_RQST_TIME
Source:
LOCK_WAIT_TIME_GLOBAL = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOCK_WAIT_TIME_GLOBAL)
Log buffer wait time
The percentage of time an agent spends waiting for space in the log buffer.
= LOG_BUFFER_WAIT_TIME / TOTAL_RQST_TIME
Source:
LOG_BUFFER_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOG_BUFFER_WAIT_TIME)
Log disk wait time
The percentage of time an agent spends waiting for log records to be flushed to disk.
= LOG_DISK_WAIT_TIME / TOTAL_RQST_TIME
Source:
LOG_DISK_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOG_DISK_WAIT_TIME)
Tcp/ip received wait time
The percentage of time spent waiting for an incoming client request over TCP/IP excluding idle time.
= TCPIP_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
TCPIP_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TCPIP_RECV_WAIT_TIME)
Tcp/ip send wait time
The percentage of time spent blocking on a TCP/IP send to the client.
= TCPIP_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
TCPIP_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TCPIP_SEND_WAIT_TIME)
Interprocess communication received wait time
The percentage of time spent by an agent receiving an incoming client request using the IPC communications protocol.
= IPC_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
IPC_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(IPC_RECV_WAIT_TIME)
Interprocess communication send wait time
The percentage of time spent blocking on an IPC send to the client.
= IPC_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
IPC_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(IPC_SEND_WAIT_TIME)
Fcm received wait time
The percentage of time spent waiting to receive data through FCM.
= FCM_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_RECV_WAIT_TIME)
Fcm table queue received wait time
The percentage of time spent waiting to receive the next buffer from a table queue.
= FCM_TQ_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_TQ_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_TQ_RECV_WAIT_TIME)
Fcm message received wait time
The percentage of time spent by an agent waiting for an FCM reply message containing the results of a previously sent FCM request message.
= FCM_MESSAGE_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_MESSAGE_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_MESSAGE_RECV_WAIT_TIME)
Fcm send wait time
The percentage of time spent blocking on an FCM send operation.
= FCM_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_SEND_WAIT_TIME)
Fcm table queue send wait time
The percentage of time spent waiting to send the next buffer through a table queue.
= FCM_TQ_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_TQ_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_TQ_SEND_WAIT_TIME)
Fcm message send wait time
The percentage of time spent blocking on an FCM message send.
= FCM_MESSAGE_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
FCM_MESSAGE_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(FCM_MESSAGE_SEND_WAIT_TIME)
Audit subsystem wait time
The percentage of time spent waiting for space in audit buffer.
= AUDIT_SUBSYSTEM_WAIT_TIME / TOTAL_RQST_TIME
Source:
AUDIT_SUBSYSTEM_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(AUDIT_SUBSYSTEM_WAIT_TIME)
Audit file write wait time
The percentage of time spent waiting to write an audit record.
= AUDIT_FILE_WRITE_WAIT_TIME / TOTAL_RQST_TIME
Source:
AUDIT_FILE_WRITE_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(AUDIT_FILE_WRITE_WAIT_TIME)
Diagnostic log file write wait time
The percentage of time spent waiting on a write to the db2diag log file.
= DIAGLOG_WRITE_WAIT_TIME / TOTAL_RQST_TIME
Source:
DIAGLOG_WRITE_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIAGLOG_WRITE_WAIT_TIME)
Total buffer pool physical read time
The percentage of time spent reading in data and index pages from the table space containers (physical) for all types of table spaces.
= POOL_READ_TIME / TOTAL_RQST_TIME
Source:
POOL_READ_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_READ_TIME)
Total buffer pool physical write time
The percentage of time for each asynchronous write to complete.
= POOL_WRITE_TIME / TOTAL_RQST_TIME
Source:
POOL_WRITE_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_WRITE_TIME)
Direct read time
The percentage of time required to perform the direct reads.
= DIRECT_READ_TIME / TOTAL_RQST_TIME
Source:
DIRECT_READ_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_READ_TIME)
Direct write time
The percentage of time required to perform the direct writes.
= DIRECT_WRITE_TIME / TOTAL_RQST_TIME
Source:
DIRECT_WRITE_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_WRITE_TIME)
Event monitor wait time
The percentage of time that an agent waited for an event monitor record to become available.
= EVMON_WAIT_TIME / TOTAL_RQST_TIME
Source:
EVMON_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(EVMON_WAIT_TIME)
Total extended latch wait time
The percentage of time spent in extended latch waits.
= TOTAL_EXTENDED_LATCH_WAIT_TIME / TOTAL_RQST_TIME
Source:
TOTAL_EXTENDED_LATCH_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_EXTENDED_LATCH_WAIT_TIME)
Time waited for prefetch
The percentage of time an application spent waiting for an I/O server (prefetcher) to finish loading pages into the buffer pool.
= PREFETCH_WAIT_TIME / TOTAL_RQST_TIME
Source:
PREFETCH_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(PREFETCH_WAIT_TIME)
Communication exit wait time
The percentage of time spent waiting for the return from a communication exit library API function.
= COMM_EXIT_WAIT_TIME / TOTAL_RQST_TIME
Source:
COMM_EXIT_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(COMM_EXIT_WAIT_TIME)
Time spent waiting to send data
The percentage of time spent waiting to send data to an in-database analytics process.
= IDA_SEND_WAIT_TIME / TOTAL_RQST_TIME
Source:
IDA_SEND_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(IDA_SEND_WAIT_TIME)
Time spent waiting to receive data
The percentage of time spent waiting to receive data from an in-database analytics process.
= IDA_RECV_WAIT_TIME / TOTAL_RQST_TIME
Source:
IDA_RECV_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(IDA_RECV_WAIT_TIME)
Cluster caching facility wait time
The percentage of time spent communicating with the cluster caching facility.
= CF_WAIT_TIME / TOTAL_RQST_TIME
Source:
CF_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(CF_WAIT_TIME)
Reclaim wait time
The percentage of time spent waiting on page locks, where the lock request caused a page to be reclaimed.
= RECLAIM_WAIT_TIME / TOTAL_RQST_TIME
Source:
RECLAIM_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(RECLAIM_WAIT_TIME)
Space map page reclaim wait time
The percentage of time spent waiting on page locks for pages related to internally maintained object space management where the lock request caused a reclaim from another member.
= SPACEMAPPAGE_RECLAIM_WAIT_TIME / TOTAL_RQST_TIME
Source:
SPACEMAPPAGE_RECLAIM_WAIT_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(SPACEMAPPAGE_RECLAIM_WAIT_TIME)
Total compile processing time
The percentage of processing (non-wait) time spent performing explicit compiles on the database server.
= TOTAL_COMPILE_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_COMPILE_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_COMPILE_PROC_TIME)
Total synchronous runstats processing time
The percentage of non-wait time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering.
= TOTAL_SYNC_RUNSTATS_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_SYNC_RUNSTATS_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_SYNC_RUNSTATS_PROC_TIME)
Total statistics fabrication processing time
The percentage of non-wait time spent on statistics fabrications by real-time statistics gathering.
= TOTAL_STATS_FABRICATION_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_STATS_FABRICATION_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_STATS_FABRICATION_PROC_TIME)
Total implicit compile processing time
The percentage of processing (non-wait) time spent performing implicit compiles on the database server.
= TOTAL_IMPLICIT_COMPILE_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_IMPLICIT_COMPILE_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_IMPLICIT_COMPILE_PROC_TIME)
Total routine user code processing time
The percentage of non-wait time spent processing user code in routines.
= TOTAL_ROUTINE_USER_CODE_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_ROUTINE_USER_CODE_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_ROUTINE_USER_CODE_PROC_TIME)
Total section processing time
The percentage of processing time agents spent performing section execution.
= TOTAL_SECTION_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_SECTION_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_SECTION_PROC_TIME)
Total section sort processing time
The percentage of processing (non-wait) time spent performing sorts while executing a section, which is the execution of the compiled query plan generated by the SQL statement that was issued by the client application.
= TOTAL_SECTION_SORT_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_SECTION_SORT_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_SECTION_SORT_PROC_TIME)
Total column-organized processing time
The percentage of non-wait processing time spent accessing column-organized synopsis tables.
= TOTAL_COL_SYNOPSIS_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_COL_SYNOPSIS_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_COL_SYNOPSIS_PROC_TIME)
Total commits processing time
The percentage of processing (non-wait) time spent performing commit processing on the database server.
= TOTAL_COMMIT_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_COMMIT_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_COMMIT_PROC_TIME)
Total rollback processing time
The percentage of processing (non-wait) time spent performing rollback operations on the database server.
= TOTAL_ROLLBACK_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_ROLLBACK_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_ROLLBACK_PROC_TIME)
Total runtime statistics processing time
The percentage of processing (non-wait) time spent performing runstats operations on the database server.
= TOTAL_RUNSTATS_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_RUNSTATS_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_RUNSTATS_PROC_TIME)
Total reorganization processing time
The percentage of processing (non-wait) time spent performing reorg operations on the database server.
= TOTAL_REORG_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_REORG_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_REORG_PROC_TIME)
Total load processing time
The percentage of processing (non-wait) time spent performing load processing on the database server.
= TOTAL_LOAD_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_LOAD_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_LOAD_PROC_TIME)
Total non-wait time for online backups
The percentage of processing (non-wait) time that was spent doing online backups.
= TOTAL_BACKUP_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_BACKUP_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_BACKUP_PROC_TIME)
Total non-wait time creating / re-creating indexes
The percentage of processing (non-wait) time that is spent building indexes due to index creation or re-creation. This time includes the time that is spent by subagents when the index creation or re-creation operation is parallelized.
= TOTAL_INDEX_BUILD_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_INDEX_BUILD_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_INDEX_BUILD_PROC_TIME)
Total connection or switch user processing time
The percentage of processing (non-wait) time spent processing a connection or switch user request.
= TOTAL_CONNECT_REQUEST_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_CONNECT_REQUEST_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_CONNECT_REQUEST_PROC_TIME)
Total connection authentication processing time
The percentage of processing (non-wait) time spent performing connection or switch user authentication.
= TOTAL_CONNECT_AUTHENTICATION_PROC_TIME / TOTAL_RQST_TIME
Source:
TOTAL_CONNECT_AUTHENTICATION_PROC_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_CONNECT_AUTHENTICATION_PROC_TIME)