db2top Tablespaces Screen (Part 2)

In the first post in this series, we saw gauges and aggregates of table space information across all the table spaces in your database. In today’s post, you will see how db2top presents individual table spaces and their attributes. You will see ways to gauge the efficiency of your caching by looking at individual hit ratios, check your storage space usage, and identify data skew problems in systems that use the Data Partitioning Feature (DPF).

The tables below show attributes for individual table spaces. The columns are shown as they would appear in delta mode (d=Y in the header) and actual mode (d=N in the header). This column information is shown one screen’s worth at a time, starting at the far left of the table:

Column Name	Definition
Tablespace Name	The name of the table space (tablespace_name). It is prefixed with “!” and colored red if the state (tablespace_state) is not normal. It is colored green if the table space is active.
Delta mode: Delta l_reads/s Actual mode: Actual l_reads	Delta mode: The number of requests for pages (data or index) from buffer pools per second during the last interval, measured for this table space (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads). Actual mode: The number of requests for pages (data or index) from buffer pools since the last snapshot reset or database activation, measured for this table space (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads).
Delta mode: Delta p_reads/s Actual mode: Actual p_reads	Delta mode: The number of pages per second (data or index) requested from buffer pools and read from containers of this table space during the last interval (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads). Actual mode: The number of pages (data or index) requested from buffer pools and read from containers of this table space since the last snapshot reset or database activation (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads).
Hit Ratio%	Delta mode: The percentage of requests for pages (data or index) from buffer pools for this table space that were serviced during the last interval without requiring a physical read from a table space container. Actual mode: The percentage of requests for pages (data or index) from buffer pools for this table space that were serviced since the last snapshot reset or database activation without requiring a physical read from a table space container. (1 – (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads) / (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads)) × 100%
Async Reads%	Delta mode: The percentage of those pages (data or index) read in from containers of this table space that were read in by asynchronous engine dispatchable units (EDUs) during the interval. Actual mode: The percentage of those pages (data or index) read in from containers of this table space that were read in by asynchronous engine dispatchable units (EDUs) since the last snapshot or database activation. (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) × 100%
Pages Aread	Delta mode: The average number of pages (data or index) read in from containers of this table space by asynchronous engine dispatchable units (EDUs) per request by the prefetcher during the interval. Actual mode: The average number of pages (data or index) read in from containers of this table space by asynchronous engine dispatchable units (EDUs) per request by the prefetcher since the last snapshot reset or database activation. (pool_async_data_reads + pool_async_index_reads)/(async_data_read_reqs + async_index_read_reqs)

Column Name	Definition
Delta mode: Delta Writes/s Actual mode: Actual Writes	Delta mode: The number of times per second for this table space during the interval that a buffer pool page was physically written to disk or a write operation took place that did not use the buffer pool (pool_data_writes + pool_index_writes + direct_writes). Actual mode: The number of times for this table space since the last snapshot reset or database activation that a buffer pool page was physically written to disk or a write operation took place that did not use the buffer pool (pool_data_writes + pool_index_writes + direct_writes).
Delta mode: Delta a_reads/s Actual mode: Actual a_reads	Delta mode: The number of pages (data or index) per second read in from containers of this table space by asynchronous engine dispatchable units (EDUs) during the interval (pool_async_data_reads + pool_async_index_reads). Actual mode: The number of pages (data or index) read in from containers of this table space by asynchronous engine dispatchable units (EDUs) since the last snapshot reset or database activation (pool_async_data_reads + pool_async_index_reads).
Delta mode: Delta a_writes/s Actual mode: Actual a_writes	Delta mode: The number of times per second during the last interval that a buffer pool page (data or index) was physically written to disk by either an asynchronous page cleaner, or a prefetcher (pool_async_data_writes + pool_async_index_writes). Actual mode: The number of times since the last snapshot reset or database activation that a buffer pool page (data or index) was physically written to disk by either an asynchronous page cleaner, or a prefetcher (pool_async_data_writes + pool_async_index_writes).
Delta mode: Direct writes/s Actual mode: Direct writes	Delta mode: The number of write operations per second for the table space that did not use the buffer pool during the interval (direct_writes). Actual mode: The number of write operations for the table space that did not use the buffer pool since the last snapshot reset or database activation (direct_writes).
Delta mode: Data writes/s Actual mode: Data writes	Delta mode: The number of times per second for this table space during the interval that a buffer pool data page was physically written to disk (pool_data_writes). Actual mode: The number of times for this table space since the last snapshot reset or database activation that a buffer pool data page was physically written to disk (pool_data_writes).
Delta mode: Index writes/s Actual mode: Index writes	Delta mode: The number of times per second for this table space during the interval that a buffer pool index page was physically written to disk (pool_index_writes). Actual mode: The number of times for this table space since the last snapshot reset or database activation that a buffer pool index page was physically written to disk (pool_index_writes).

Column Name	Definition
Avg_Reads Dir_Reqs	Delta mode: The average number of direct read operations, that is, those reads that do not use the buffer pool, per direct read request during the interval (direct_reads / direct_reads_reqs). Actual mode: The average number of direct read operations, that is, those reads that do not use the buffer pool, per direct read request since the last snapshot reset of database activation (direct_reads / direct_reads_reqs).
Avg RdTime	The average elapsed time required to perform a read for this table space, whether or not it used a buffer pool. (pool_read_time + direct_read_time) / (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads + direct_reads)
Avg WrTime	The average amount of time spent physically writing a page to disk for this table space, whether or not it used a buffer pool. (pool_write_time + direct_write_time) / (pool_data_writes + pool_index_writes + direct_writes)
Tbsp Type	The type of content in a table space (tablespace_content_type). It can take on one of the following values: REG (SQLM_TABLESPACE_CONTENT_ANY) LONG (SQLM_TABLESPACE_CONTENT_LARGE) TMP (SQLM_TABLESPACE_CONTENT_SYSTEMP) UTMP (SQLM_TABLESPACE_CONTENT_USRTEMP)
# of DBP	The number of database partitions for this table space.
Avg PrefSize	Delta mode: The average number of bytes (data or index) read in from containers of this table space by asynchronous engine dispatchable units (EDUs) per request by the prefetcher during the interval. Expressed as a multiple of bytes (e.g. KB, MB, etc.). Actual mode: The average number of bytes (data or index) read in from containers of this table space by asynchronous engine dispatchable units (EDUs) per request by the prefetcher since the last snapshot reset or database activation. Expressed as a multiple of bytes (e.g. KB, MB, etc.). tablespace_page_size × (pool_async_data_reads + pool_async_index_reads) / (async_data_read_reqs + async_index_read_reqs)
Space Used	The total number of bytes that are currently used (not free) in this table space (tablespace_used_pages × tablespace_page_size). Expressed as a multiple of bytes (e.g. KB, MB, etc.).
Total Size	The total number of bytes in the table space (tablespace_total_pages × tablespace_page_size). Expressed as a multiple of bytes (e.g. KB, MB, etc.).

Column Name	Definition
# of Extents	The number of extents in the table space (tablespace_total_pages / tablespace_extent_size).
High WaterMark	For DMS table spaces, this shows the current number of bytes allocated (tablespace_page_top × tablespace_page_size). It is expressed as a multiple of bytes (e.g. KB, MB, etc.).
Max High WaterMark	For DMS table spaces, this shows the highest number of bytes allocated (tbsp_max_page_top × tablespace_page_size) since the database was activated. It is expressed as a multiple of bytes (e.g. KB, MB, etc.). For SMS table spaces, this is not applicable.
% Full	For DMS table spaces, this shows the percentage of the total number of bytes in the table space that are currently used. For SMS table spaces, a ‘-’ is shown. Colored red if over 90%.
Data Skew	A measure of the asymmetry in table space page use among partitions. 1 – ((sum(tablespace_used_pages) / node_count) / tbsp_max_page_top), where sum() is the sum across all partitions, max() is the maximum across all partitions, and node_count is the number of partitions of the table space
Tbspce Status	The current state of a table space (tablespace_state). It is shown in bold and colored red if the state is not “Normal”. Only one state is reported among the following possible values: Normal Quiesced share Quiesced update Quiesced exclusive Load pending Delete pending Backup pending Rfwd in progress Rfwd pending Restore pending Disable pending Reorg in progress Backup in progress Stordef pending Restore in progress Offline Drop pending Suspend write Load in progress Stordef allowed Stordef final version Stordef changed Rebal in progress Pstat deletion Pstat creation Unknown [x] where x is the value of a SQLB constant from sqlutil.h
Tbspce Type	The type of the table space (tablespace_type). It can have one of the following values: SMS DMS
FSC On	Indicates whether a particular table space uses file system caching (fs_caching). If file system caching is enabled, “Yes” is shown. If file system caching is disabled, “No” is shown.
Auto Stor	Describes whether the table space was created as an automatic storage table space (tablespace_using_auto_storage). If it is an automatic storage table space, “Yes” is shown. Otherwise, “No” is shown.
Auto Size	Describes whether automatic resizing is enabled for the table space (tablespace_auto_resize_enabled). If automatic resizing is enabled, “Yes” is shown. Otherwise, “No” is shown.

Column Name	Definition
Failed Resize	Describes whether or not the last attempt to automatically increase the size of the table space failed (tablespace_last_resize_failed). If the resize failed, “Yes” is shown in bold and colored red. Otherwise, “No” is shown. If automatic resizing is disabled, “-” is shown.
Last Resize	The last time that the size of the table space was successfully increased (tablespace_last_resize_time).
Delta mode: File Closed/s Actual mode: File Closed	Delta mode: The total number of database files closed per second for the table space during the interval (files_closed). Actual mode: The total number of database files closed for the table space since the last snapshot reset or database activation (files_closed).
Delta mode: Victim Buffers/s Actual mode: Victim Buffers	Delta mode: Number of times per second that an agent did not have a preselected victim buffer available for this table space during the interval (pool_no_victim_buffer). Actual mode: Number of times that an agent did not have a preselected victim buffer available for this table space since the last snapshot reset or database activation (pool_no_victim_buffer).
Delta mode: Unread PrefPages/s Actual mode: Unread PrefPages	Delta mode: The number of pages per second that the prefetcher read in during the interval that were never used (unread_prefetch_pages). Actual mode: The number of pages that the prefetcher read in since the last snapshot reset or database activation that were never used (unread_prefetch_pages).
Delta mode: XDA l_reads/s Actual mode: XDA l_reads	Delta mode: The number of data pages per second for XML storage objects (XDAs) that have been requested from the buffer pool for this table space during the interval (pool_xda_l_reads + pool_temp_xda_l_reads). Actual mode: The number of data pages for XML storage objects (XDAs) that have been requested from the buffer pool for this table space since the last snapshot reset or database activation (pool_xda_l_reads + pool_temp_xda_l_reads).
Delta mode: XDA p_reads/s Actual mode: XDA p_reads	Delta mode: The number of data pages read in per second from the containers of this table space for XML storage objects (XDAs) during the interval (pool_xda_p_reads + pool_temp_xda_p_reads). Actual mode: The number of data pages read in from the containers of this table space for XML storage objects (XDAs) since the last snapshot reset or database activation (pool_xda_p_reads + pool_temp_xda_p_reads).
Delta mode: XDA l_writes/s Actual mode: XDA writes	Delta mode: The number of times per second that a buffer pool data page for an XML storage object (XDA) was physically written to disk for this table space during the interval (pool_xda_writes). Actual mode: The number of times a buffer pool data page for an XML storage object (XDA) was physically written to disk for this table space since the last snapshot reset or database activation (pool_xda_writes).

Column Name	Definition
Page Size	Page size used by this table space, expressed in units of kilobytes (tablespace_page_size / 1024).
Extent Size	The extent size used by this table space (tablespace_page_size × tablespace_extent_size). Expressed in units that are a multiple of bytes (e.g. KB, MB, etc.).
Prefetch Size	The maximum number of bytes the prefetcher gets from the disk at a time (tablespace_page_size × tablespace_prefetch_size). Expressed in units that are a multiple of bytes (e.g. KB, MB, etc.). If automatic prefetch size is enabled, “AUTO” is reported instead.
Minimum Recovery_Time	The earliest point in time to which a table space can be rolled forward (tablespace_min_recovery_time). If the minimum recovery time is zero, a “-” is reported.
Recent IOs[1 to 30]	The amount of logical reads, logical writes and direct writes to occur in this table space over the specified window of time. A sliding window is used to measure only the last x samples, where x is a number between 1 and 30 that the user can control using the ‘+’ and ‘-‘ keys. pool_data_writes + pool_index_writes + direct_writes + pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads

The Tablespaces 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.

Related posts...