db2top Database Screen (Part 2)

In the first post of this series on the db2top Database screen, we took a look at the gauges that make up the top part of the screen and saw the additional data about backups that becomes available when the screen widened to 141 columns or wider. In this second post, we will examine the four rows of database-level information presented below the gauges, including information on memory usage, active and idle connections, locks, logs, buffer pools, and sorts. You will also see the impact of the delta and cumulative modes on some of these data.

We will examine each row individually:

Row 1

Column Name	Definition
Start Date	The date of the first connection to the database or when the activate database was issued. Blank if the database is not activated. db_conn_time
Start Time	The time of the first connection to the database or when the activate database was issued. Blank if the database is not activated. db_conn_time
Status	The current status of the database. Possible values are: “Inactive”, “Active”, “Quiesce Pending”, “Quiesced”, “Rollforward”, and “Unknown [x]” where x is the API constant for the db_status monitor element. “Inactive” and “Active” are shown in a regular font and all others are shown in bold.
Shthres	The value of the instance-wide soft limit on the total amount of memory (in units of bytes, kilobytes, megabytes, etc.) consumed by private sorts at any given time across all partitions. sheapthres * num_nodes_in_db2_instance * 4096
Buffers	The pool_cur_size (in units of kilobytes, megabytes, etc.) of the buffer pool heap memory pool.
FCMBuf	The pool_cur_size (in units of kilobytes, megabytes, etc.) of the FCMBP heap memory pool.
OtherMem	The sum of the pool_cur_size (in units of kilobytes, megabytes, etc.) of all other memory pools except the buffer pool heap and FCMBP heap memory pools.

Row 2

Column Name	Definition
Sessions	The current number of connections. local_cons + rem_cons_in
ActSess	The current number of connections with executing work. local_cons_in_exec + rem_cons_in_exec
LockUsed	One hundred times the number of locks held per byte allocated to the lock list. 100 * locks_held / (locklist * 4096)
LockEscals	The number of times that locks have been escalated from several row locks to a table lock. lock_escals
Deadlocks	The total number of deadlocks that have occurred.
LogReads	Delta mode: The number of log pages read from disk by the logger (log_reads) in the last interval. Shown in reverse text and blinking when greater than zero on an active database. Actual mode: The number of log pages read from disk by the logger (log_reads) since the last snapshot reset or database activation.
LogWrites	Delta mode: The number of log pages written to disk by the logger (log_writes) in the last interval. Shown in reverse text and blinking when greater than zero on an active database. Actual mode: The number of log pages written to disk by the logger (log_writes) since the last snapshot reset or database activation.

Row 3

Column Name	Definition
L_Reads	Delta mode: The number of logical reads (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads + direct_reads) performed in the last interval. Actual mode: The number of logical reads (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads + direct_reads) performed since the last snapshot reset or database activation.
P_Reads	Delta mode: The number of physical reads (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) performed per second during the last interval. Actual mode: The number of physical reads (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) performed since the last snapshot reset or database activation.
HitRatio	Delta mode: The percentage of logical reads performed per second during the last interval that did not require a physical read. 1 – (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) / (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads + direct_reads) Actual mode: The percentage of logical reads performed since the last snapshot reset or database activation that did not require a physical read. 1 – (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) / (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads + direct_reads)
A_Reads	Delta mode: The percentage of physical reads performed per second during the last interval that were performed asynchronously. (pool_async_data_reads + pool_async_index_reads) / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) Actual mode: The percentage of physical reads performed since the last snapshot reset or database activation that were performed asynchronously. (pool_async_data_reads + pool_async_index_reads) / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads)
Writes	Delta mode: The number of direct_writes performed per second during the last interval. Actual mode: The number of direct_writes performed since the last snapshot reset or database activation.
A_Writes	Delta mode: The number of asynchronous writes (pool_async_data_writes + pool_async_index_writes) performed per second during the last interval. Actual mode: The number of asynchronous writes (pool_async_data_writes + pool_async_index_writes) performed since the last snapshot reset or database activation.
Lock Wait	The number of agents waiting on a lock (locks_waiting). Shown in reverse text and blinking whenever it is greater than zero.

Row 4

Column Name	Definition
Sortheap	The total sort memory (both shared and private) measured in units of bytes, kilobytes, megabytes, etc. sort_heap_allocated + sort_shrheap_allocated
SortOvf	Delta mode: The total number of sorts per second during the last interval that ran out of sort heap and may have required disk space for temporary storage during the last interval. sort_overflows Actual mode: The total number of sorts that ran out of sort heap and may have required disk space for temporary storage since the last snapshot reset or database activation. sort_overflows
PctSortOvf	Delta mode: The percentage of the total number of sorts that overflowed during the last interval. sort_overflows / total_sorts Actual mode: The percentage of the total number of sorts that overflowed since the last snapshot reset or database activation. sort_overflows / total_sorts
AvgPRdTime	Delta mode: The average amount of time that a physical read took during the last interval. pool_read_time / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads) Actual mode: The average amount of time that a physical read took measured since the last snapshot reset or database activation. pool_read_time / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads)
AvgDRdTime	Delta mode: The average elapsed time per direct read measured during the last interval. direct_read_time / direct_reads Actual mode: The average elapsed time per direct read measured since the last snapshot reset or database activation. direct_read_time / direct_reads
AvgPWrTime	Delta mode: The average amount of time per write spent physically writing data or index pages from the buffer pool to disk during the last interval. pool_write_time / (pool_data_writes + pool_index_writes) Actual mode: The average amount of time per write spent physically writing data or index pages from the buffer pool to disk since the last snapshot reset or database activation. pool_write_time / (pool_data_writes + pool_index_writes)
AvgDWrTime	Delta mode: The average amount of time per write for writes that did not use the buffer pool during the last interval. direct_write_time / direct_writes Actual mode: The average amount of time per write for writes that did not use the buffer pool since the last snapshot reset or database activation. direct_write_time / direct_writes

The database screen is affected by typing the ‘k’ key to switch between deltas and actuals. It is also affected by the ‘G’ key switching between local and global snapshots. It is not affected by the ‘X’ key toggling extended mode on and off.