db2top Tables Screen (Part 1)

Have you ever wanted to see a (near) real-time view of how much data is being read from or written to your tables? How about your temporary tables? The db2top Tables screen does all of this and more.

In today’s post, you will learn how to:

identify which tables are most heavily being impacted by reads or by writes
identify which tables have the greatest storage requirements
monitor the progress of any tables being reorganized and see the impact of compression
identify the users and applications of temporary tables

You launch the db2top Tables screen by holding down the Shift key and pressing the ‘T’ key. The table information is presented, not surprisingly, in a table. It is shown below for a screen that is 80 columns wide:

We will examine the table one screen’s worth of columns at a time:

Column Name	Definition
Table Name	The fully qualified name of the table. For permanent tables, it takes the following form: table_schema.table_name and is truncated to 40 characters. On systems using the Data Partitioning Feature (DPF), in which each row is a table partition instead of a table, it takes the following form: table_schema.table_name[data_partition_id] and is truncated to 40 characters.
Delta mode: Delta RowsRead/s Actual mode: Actual RowsRead	Delta mode: The number of rows read per second from the table during the interval (rows_read). Actual mode: The number of rows read from the table since the last snapshot reset or database activation (rows_read).
Delta mode: Delta RowsWritten/s Actual mode: Actual RowsWritten	Delta mode: The number of rows changed (inserted, deleted or updated) per second in the table during the interval (rows_written). Actual mode: The number of rows changed (inserted, deleted or updated) in the table since the last snapshot reset or database activation (rows_written).
Data Pages	The number of disk pages consumed by a table (data_object_pages). This size represents the base table size only.

Column Name	Definition
Index Pages	The number of disk pages consumed by all indexes defined on a table (index_object_pages).
Page Reorgs	Delta mode: The number of page reorganizations executed for the table during the interval (page_reorgs). Actual mode: The number of page reorganizations executed for the table since the last snapshot reset or database activation (page_reorgs).
Delta mode: Rows Overflows/s Actual mode: Rows Overflows	Delta mode: The number of accesses (reads and writes) per second to overflowed rows of this table during the interval (overflow_accesses). Actual mode: The number of accesses (reads and writes) to overflowed rows of this table since the last snapshot reset or database activation (overflow_accesses).
XDA_Page Reorgs	Delta mode: 0 Actual mode: 0
Table Type	The type of table (table_type). It can have one of the following values: User (SQLM_USER_TABLE) Drop (SQLM_DROPPED_TABLE) Temp (SQLM_TEMP_TABLE) Catlg (SQLM_CATALOG_TABLE) Reorg (SQLM_REORG_TABLE) A number corresponding to an SQLM constant
Reorg% Progress	If this table is in the process of being reorganized, this shows the percentage of reorganization work that has been completed (reorg_current_counter / reorg_max_counter × 100%). Shows a "-" if there is no reorganization of this table in progress.
Delta mode: Rows Compressed/s Delta mode: Rows Compressed	Delta mode: During reorganization, this shows the number of rows compressed per second in the table during the interval (reorg_rows_compressed). Actual mode: During reorganization, this shows the number of rows compressed in the table since the last snapshot reset or database activation (reorg_rows_compressed).

Column Name	Definition
Delta mode: Rows Rejected/s Delta mode: Rows Rejected	Delta mode: During reorganization, this shows the number of rows per second that were not compressed due to the record length being less than or equal to the minimum record length during the interval (reorg_rows_rejected_for_compression). Actual mode: During reorganization, this shows the number of rows that were not compressed due to the record length being less than or equal to the minimum record length since the last snapshot reset or database activation (reorg_rows_rejected_for_compression).
Tablespace Name	The name of the table space of this table (tablespace_name). If no table space can be found for this table, it shows “tbspid x” where x is the table space ID (tablespace_id).
Table Size	The size of the table in some multiple of bytes, such as KB, MB, etc. (data_object_pages + index_object_pages) × tablespace_page_size When tablespace_page_size cannot be determined for the table’s table space, 4096 is used.
Appl Handle	Application handle extracted from the table_schema for temporary table spaces. Otherwise, it shows “-”.
DB User	Database user ID extracted from the table_schema for temporary table spaces. Otherwise, it shows “N/A”.
Recent RowAcc[1 to 30]	The number of rows read or written for this table 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. rows_read + rows_written

In part 2, we will see all the additional information that appears when you increase the screen width past 140 columns.

Related posts...