Skip to main contentIBM dmctop Wiki

Overview / Db Overview

Purpose

Overview view tells you at a glance how busy the monitored database is. It provides basic information about the current level of activity. For example:

  • Overview: Basic information of database.
  • Resource consumption: Indicators to show resource consumption of database such as the memory consumption.
  • Throughput: Indicators to show speed of database transaction operations.
  • Contention: Indicators of contention such as high lock wait time, lock escalations or deadlocks.
  • Time spent: Indicators of time spent on database operations.

Screenshot

image

Metric shown

Gauge

Act session

The percentage of active connections.

= ACTIVE_CONN / TOTAL_CONN

TOTAL_CONN = 

 SELECT COUNT(*) AS TOTAL_CONN 
         FROM   AGENT_TAB

ACTIVE_CONN =

SELECT COUNT(*) AS ACTIVE_CONN 
         FROM   AGENT_TAB 
         WHERE  EVENT_STATE='EXECUTING' 
         AND    EVENT_TYPE='PROCESS' 
         AND    IDLE_DURATION < 15

AGENT_TAB = 

SELECT EVENT_STATE , 
                EVENT_TYPE , 
                CAST(((CAST(JULIAN_DAY(CURRENT TIMESTAMP) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM CURRENT TIMESTAMP) * 60 * 60 + EXTRACT(MINUTE FROM CURRENT TIMESTAMP) * 60 + EXTRACT(SECOND FROM CURRENT TIMESTAMP) - (CAST(JULIAN_DAY(AGENT_STATE_LAST_UPDATE_TIME) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 * 60 + EXTRACT(MINUTE FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 + EXTRACT(SECOND FROM AGENT_STATE_LAST_UPDATE_TIME)))) AS DOUBLE) AS IDLE_DURATION
         FROM   TABLE(MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)) 
         WHERE  APPLICATION_HANDLE<>0 
         AND    AGENT_STATE = 'ACTIVE'

Source:

MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#).AGENT_STATE_LAST_UPDATE_TIME

CURRENT TIMESTAMP

Log used

The percentage of amount of active log space currently used in the database.

= TOTAL_LOG_USED / TOTAL_LOG

Source:

TOTAL_LOG_USED = MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(TOTAL_LOG_USED)

TOTAL_LOG= MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(TOTAL_LOG_USED + TOTAL_LOG_AVAILABLE)

Overview

Start date

The date of the connection to the database (at the database level, this is the first connection to the database), or when the activate database was issued.

Source: MON_GET_DATABASE(#MEMBER#).MIN(DB_CONN_TIME)

Start time

The time the monitored database was activated.

Source: CURRENT TIMESTAMP

Database status

The current status of the database. According to the Knowledge Center, the possible values for this field are:

  • ACTIVE
  • QUIESCE_PEND
  • QUIESCED
  • ROLLFWD
  • ACTIVE_STANDBY
  • STANDBY

Source: MON_GET_DATABASE(#MEMBER#).MAX(DB_STATUS)

System physical mem

The total amount of physical memory on this host

Source: SYSPROC.ENV_GET_SYSTEM_RESOURCES().MEMORY_TOTAL

Resource consumption

CPU usage %

The total cpu usage over the system.

= System CPU utilization * DB2 CPU utilization

System CPU utilization = (CPU_USER_TOTAL + CPU_SYSTEM_TOTAL)/(CPU_USER_TOTAL + CPU_SYSTEM_TOTAL + CPU_IDLE_TOTAL + CPU_IOWAIT_TOTAL)

DB2 CPU utilization = (DB2_CPU_USER_TOTAL + DB2_CPU_SYSTEM_TOTAL)/(CPU_USER_TOTAL + CPU_SYSTEM_TOTAL)

Source:

DB2_CPU_USER_TOTAL = ENV_GET_DB2_SYSTEM_RESOURCES(#MEMBER#).SUM(CPU_USER)

DB2_CPU_SYSTEM_TOTAL = ENV_GET_DB2_SYSTEM_RESOURCES(#MEMBER#).SUM(CPU_SYSTEM)

CPU_USER_TOTAL = SYSPROC.ENV_GET_SYSTEM_RESOURCES().CPU_USER

CPU_SYSTEM_TOTAL = SYSPROC.ENV_GET_SYSTEM_RESOURCES().CPU_SYSTEM

CPU_IDLE_TOTAL = SYSPROC.ENV_GET_SYSTEM_RESOURCES().CPU_IDLE

CPU_IOWAIT_TOTAL = SYSPROC.ENV_GET_SYSTEM_RESOURCES().CPU_IOWAIT

Instance mem committed

Memory committed, as opposed to used, is a better measure of the memory footprint of DB2. That is, how much memory is committed by the OS for use by DB2 and hence unavailable for other processes.

Source: MON_GET_MEMORY_SET(‘DBMS’, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_SET_COMMITTED)

Database mem committed

The portion of memory committed for just this database, as opposed to the whole instance.

Source: MON_GET_MEMORY_SET(‘DATABASE’, CURRENT_SERVER, #MEMBER#).SUM(MEMORY_SET_COMMITTED)

Bufferpool memory used

Buffer pool memory is typically the largest component of memory usage by DB2. So it is broken out separately here.

Source: MON_GET_MEMORY_POOL(‘DATABASE’,NULL, #MEMBER#).SUM(MEMORY_POOL_USED) WHERE MEMORY_POOL_TYPE=‘BP’

Shared sort memory used

Shared sort memory committed in use by this memory pool

Source: MON_GET_MEMORY_POOL(‘DATABASE’,NULL, #MEMBER#).SUM(MEMORY_POOL_USED) WHERE MEMORY_POOL_TYPE=‘SHARED_SORT’

Storage usage %

The percentage of space used on a file system pointed to by a storage path or container.

= FS_USED_SIZE / FS_TOTAL_SIZE

Source:

FS_USED_SIZE = MON_GET_CONTAINER(”, #MEMBER#).FS_USED_SIZE

FS_TOTAL_SIZE = MON_GET_CONTAINER(”, #MEMBER#).FS_TOTAL_SIZE

Log usage %

The percentage of amount of active log space currently used in the database.

= TOTAL_LOG_USED / TOTAL_LOG

Source:

TOTAL_LOG_USED = MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(TOTAL_LOG_USED)

TOTAL_LOG= MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(TOTAL_LOG_USED + TOTAL_LOG_AVAILABLE)

  • Log used

The amount of active log space currently used in the database.

Source: TOTAL_LOG_USED = MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(TOTAL_LOG_USED)

Throughput

Transactions

The total number of commit and rollback statements issued by the client application.

Source: MON_GET_DATABASE(#MEMBER#).SUM(TOTAL_APP_COMMITS + TOTAL_APP_ROLLBACKS)

Select stmts

The number of SQL SELECT statements that were executed.

Source: MON_GET_DATABASE(#MEMBER#).SUM(SELECT_SQL_STMTS)

Uid stmts

The number of UPDATE, INSERT, MERGE and DELETE statements that were executed.

Source: MON_GET_DATABASE(#MEMBER#).SUM(UID_SQL_STMTS)

Activities aborted

The total number of coordinator activities that completed with errors.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(ACT_ABORTED_TOTAL)

Activities queued

The number of times that activities or connections have been queued by a WLM threshold.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(WLM_QUEUE_ASSIGNMENTS_TOTAL)

Read efficiency

The percentage of the number of rows that have been read after they are selected and returned to the application.

= ROWS_READ/ROWS_RETURNED

Source:

ROWS_READ = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(ROWS_READ)

ROWS_RETURNED = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(ROWS_RETURNED)

Log reads

The number of log pages read from disk by the logger.

Source: MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_READS)

Log writes

The number of log pages written to disk by the logger.

Source: MON_GET_TRANSACTION_LOG(#MEMBER#).SUM(LOG_WRITES)

Logical reads

Indicates the number of data, index, and column-organized pages which have been requested from the buffer pool (logical) for regular, large and temporary table spaces.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

Physical reads

Indicates the number of data, index, and column-organized pages which have been requested from the table space containers (physical) for regular, large and temporary table spaces.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS)

Async reads

Indicates the number of data, index, and column-organized pages which have been requested from the table space containers (physical) for all types of table spaces

Source: MON_GET_DATABASE(#MEMBER#).SUM(POOL_ASYNC_DATA_READS + POOL_ASYNC_INDEX_READS + POOL_ASYNC_XDA_READS + POOL_ASYNC_COL_READS)

Writes

The number of write operations that do not use the buffer pool.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_WRITES)

Async writes

The number of times a buffer pool data, index, and column-organized pages was physically written to disk by either an asynchronous page cleaner, or a prefetcher. A prefetcher might have written dirty pages to disk to make space for the pages being prefetched.

Source: MON_GET_DATABASE(#MEMBER#).SUM(POOL_ASYNC_DATA_WRITES + POOL_ASYNC_INDEX_WRITES + POOL_ASYNC_XDA_WRITES + POOL_ASYNC_COL_WRITES)

Contention

Connections

The number of active agents.

= 

 SELECT COUNT(*) AS TOTAL_CONN 
         FROM   AGENT_TAB

AGENT_TAB = 

SELECT EVENT_STATE , 
                EVENT_TYPE , 
                CAST(((CAST(JULIAN_DAY(CURRENT TIMESTAMP) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM CURRENT TIMESTAMP) * 60 * 60 + EXTRACT(MINUTE FROM CURRENT TIMESTAMP) * 60 + EXTRACT(SECOND FROM CURRENT TIMESTAMP) - (CAST(JULIAN_DAY(AGENT_STATE_LAST_UPDATE_TIME) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 * 60 + EXTRACT(MINUTE FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 + EXTRACT(SECOND FROM AGENT_STATE_LAST_UPDATE_TIME)))) AS DOUBLE) AS IDLE_DURATION
         FROM   TABLE(MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)) 
         WHERE  APPLICATION_HANDLE<>0 
         AND    AGENT_STATE = 'ACTIVE'

Source:

MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#).AGENT_STATE_LAST_UPDATE_TIME

CURRENT TIMESTAMP

Active connections

The number of active connections.

=

SELECT COUNT(*) AS ACTIVE_CONN 
         FROM   AGENT_TAB 
         WHERE  EVENT_STATE='EXECUTING' 
         AND    EVENT_TYPE='PROCESS' 
         AND    IDLE_DURATION < 15

AGENT_TAB = 

SELECT EVENT_STATE , 
                EVENT_TYPE , 
                CAST(((CAST(JULIAN_DAY(CURRENT TIMESTAMP) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM CURRENT TIMESTAMP) * 60 * 60 + EXTRACT(MINUTE FROM CURRENT TIMESTAMP) * 60 + EXTRACT(SECOND FROM CURRENT TIMESTAMP) - (CAST(JULIAN_DAY(AGENT_STATE_LAST_UPDATE_TIME) AS DECFLOAT(34)) * 86400 + EXTRACT(HOUR FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 * 60 + EXTRACT(MINUTE FROM AGENT_STATE_LAST_UPDATE_TIME) * 60 + EXTRACT(SECOND FROM AGENT_STATE_LAST_UPDATE_TIME)))) AS DOUBLE) AS IDLE_DURATION
         FROM   TABLE(MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#)) 
         WHERE  APPLICATION_HANDLE<>0 
         AND    AGENT_STATE = 'ACTIVE'

Source:

MON_GET_AGENT(NULL,NULL,NULL,#MEMBER#).AGENT_STATE_LAST_UPDATE_TIME

CURRENT TIMESTAMP

Lock held

The number of locks currently held.

Source: MON_GET_DATABASE(#MEMBER#).SUM(NUM_LOCKS_HELD)

Lock waits

The total number of times applications or connections waited for locks.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOCK_WAITS)

Lock timeouts

The number of times that a request to lock an object timed out instead of being granted.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOCK_TIMEOUTS)

Lock escalations

The number of times that locks have been escalated from several row locks to a table lock.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(LOCK_ESCALS)

Deadlocks

The total number of deadlocks that have occurred.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DEADLOCKS)

Threshold violations

The total number of times a threshold was violated.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(THRESH_VIOLATIONS)

Hit ratio

The ratio of the number of pages which have been requested from the buffer pool (logical) to pages from the table space containers (physical) for regular, large and temporary table spaces.

= (PHYSICAL_READS + DIRECT_READS) / (LOGICAL_READS + DIRECT_READS)

Source:

LOGICAL_READS = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_L_READS + POOL_INDEX_L_READS + POOL_XDA_L_READS + POOL_TEMP_DATA_L_READS + POOL_TEMP_INDEX_L_READS + POOL_TEMP_XDA_L_READS + POOL_COL_L_READS + POOL_TEMP_COL_L_READS)

PHYSICAL_READS = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS)

DIRECT_READS = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_READS)

Timespent

Avg p read time

The average time for a page requested from the table space containers (physical) for all types of table spaces.

= POOL_READ_TIME / PHYSICAL_READS

Source:

POOL_READ_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_READ_TIME)

PHYSICAL_READS = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_P_READS + POOL_INDEX_P_READS + POOL_XDA_P_READS + POOL_TEMP_DATA_P_READS + POOL_TEMP_INDEX_P_READS + POOL_TEMP_XDA_P_READS + POOL_COL_P_READS + POOL_TEMP_COL_P_READS)

Avg d read time

The average time required to perform the direct reads.

= DIRECT_READ_TIME / DIRECT_READS

Source:

DIRECT_READ_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_READ_TIME)

DIRECT_READS = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_READS)

Sorts

The total number of sorts that have been executed.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_SORTS)

Sort overflows

The total number of sorts that ran out of sort heap and may have required disk space for temporary storage.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(SORT_OVERFLOWS)

Hash joins

The total number of hash joins executed.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_HASH_JOINS)

Hash join overflows

The number of times that hash join data exceeded the available sort heap space.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(HASH_JOIN_OVERFLOWS)

Hash grpbys

The total number of hashed GROUP BY operations.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(TOTAL_HASH_GRPBYS)

Hash grpb overflows

The number of times that GROUP BY operations using hashing as their grouping method exceeded the available sort heap memory.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(HASH_GRPBY_OVERFLOWS)

Avg p write time

The average time for an asynchronous write to complete.

= POOL_WRITE_TIME / WRITES

Source:

POOL_WRITE_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_WRITE_TIME)

WRITES = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(POOL_DATA_WRITES + POOL_INDEX_WRITES + POOL_XDA_WRITES)

Avg d write time

The average time required to perform a direct write.

= DIRECT_WRITE_TIME / DIRECT_WRITES

Source:

DIRECT_WRITE_TIME = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_WRITE_TIME)

DIRECT_WRITES = MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(DIRECT_WRITES)

Pct time queued

The time spent waiting on a WLM queuing threshold.

Source: MON_GET_SERVICE_SUBCLASS(NULL,NULL,#MEMBER#).SUM(WLM_QUEUE_TIME_TOTAL)

Previous
Getting started: Hot Keys
Next
Throughput: Workloads