DB2 Version 9.7 for Linux, UNIX, and Windows

MON_GET_TABLE table function - get table metrics

The MON_GET_TABLE table function returns monitor metrics for one or more tables.

Syntax


>>-MON_GET_TABLE--(--tabschema--,--tabname--,--member--)-------><

The schema is SYSPROC.

Table function parameters

tabschema: An input argument of type VARCHAR(128) that specifies a valid table schema name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all tables in all schemas in the database. If the argument is specified, metrics are only returned for tables in the specified schema.
tabname: An input argument of type VARCHAR(128) that specifies a valid table name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all the tables in the database.
member: An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the MON_GET_TABLE function.

Example

List the activity on all tables accessed since the database was activated, aggregated across all database members, ordered by highest number of reads.

SELECT varchar(tabschema,20) as tabschema, 
       varchar(tabname,20) as tabname, 
       sum(rows_read) as total_rows_read, 
       sum(rows_inserted) as total_rows_inserted, 
       sum(rows_updated) as total_rows_updated, 
       sum(rows_deleted) as total_rows_deleted 
FROM TABLE(MON_GET_TABLE('','',-2)) AS t 
GROUP BY tabschema, tabname 
ORDER BY total_rows_read DESC

The following example is a sample output from this query.

TABSCHEMA            TABNAME              TOTAL_ROWS_READ      ... 
-------------------- -------------------- -------------------- ... 
SYSIBM               SYSHISTO                              113 ... 
SYSIBM               SYSWORKL                               22 ... 
SYSIBM               SYSROUTI                               13 ... 
SYSIBM               SYSSERVI                               13 ... 
SYSIBM               SYSTHRES                                6 ... 
SYSIBM               SYSTABLE                                3 ... 
SYSIBM               SYSCONTE                                2 ... 
SYSIBM               SYSDBAUT                                2 ... 
SYSIBM               SYSEVENT                                2 ... 
SYSIBM               SYSPLAN                                 1 ... 
SYSIBM               SYSSURRO                                1 ... 
SYSIBM               SYSVERSI                                1 ... 
SYSIBM               SYSXMLST                                1 ... 
SYSIBM               SYSAUDIT                                0 ... 
SYSIBM               SYSROLEA                                0 ... 
SYSIBM               SYSROLES                                0 ... 
SYSIBM               SYSTASKS                                0 ... 
SYSIBM               SYSWORKA                                0 ... 
SYSIBM               SYSXMLPA                                0 ... 

  19 record(s) selected.

Output for query (continued).

... TOTAL_ROWS_INSERTED  TOTAL_ROWS_UPDATED   TOTAL_ROWS_DELETED  
... -------------------- -------------------- --------------------
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0
...                    0                    0                    0

Usage notes

The MON_GET_TABLE table function returns one row of data per database table and per database member. If range-partitioned tables are being used, one row is returned for each table partition per database member. No aggregation across database members is performed. However, aggregation can be achieved through SQL queries as shown in the example.

Metrics are returned only for tables accessed since the database was activated.

Metrics are always enabled. You do not need to turn on any system monitor switches to access table metrics through this function.

Information returned

Table 1. Information returned for MON_GET_TABLE
Column Name	Data Type	Description
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
MEMBER	SMALLINT	member- Database member
TAB_TYPE	VARCHAR(14)	table_type - Table type. This interface returns a text identifier based on defines in sqlmon.h, and is one of: USER_TABLE TEMP_TABLE CATALOG_TABLE
TAB_FILE_ID	BIGINT	table_file_id - Table file ID
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier
TBSP_ID	BIGINT	tablespace_id - Table space identification
INDEX_TBSP_ID	BIGINT	index_tbsp_id - Index table space ID
LONG_TBSP_ID	BIGINT	long_tbsp_id - Long table space ID
TABLE_SCANS	BIGINT	table_scans - Table scans
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_INSERTED	BIGINT	rows_inserted - Rows inserted
ROWS_UPDATED	BIGINT	rows_updated - Rows updated
ROWS_DELETED	BIGINT	rows_deleted - Rows deleted
OVERFLOW_ACCESSES	BIGINT	overflow_accesses - Accesses to overflowed records
OVERFLOW_CREATES	BIGINT	overflow_creates - Overflow creates
PAGE_REORGS	BIGINT	page_reorgs - Page reorganizations
DATA_OBJECT_L_PAGES^{1, 2}	BIGINT	data_object_l_pages - Table data logical pages
LOB_OBJECT_L_PAGES²	BIGINT	lob_object_l_pages - LOB data logical pages
LONG_OBJECT_L_PAGES²	BIGINT	long_object_l_pages - Long object data logical pages
INDEX_OBJECT_L_PAGES²	BIGINT	index_object_l_pages - Index data logical pages
XDA_OBJECT_L_PAGES²	BIGINT	xda_object_l_pages - XML storage object (XDA) data logical pages
DBPARTITIONNUM	SMALLINT	In a partitioned database environment, this is the numeric identifier for the database member. For DB2® Enterprise Server Edition and in a DB2 pureScale® environment, this value is 0.
ADDITIONAL_DETAILS	BLOB(100K)	Reserved for future use.
Notes: This value might be less than the amount of space allocated for the object. This can happen when you use the RECLAIM EXTENTS ONLY option with the REORG TABLE command. In this case, reclaimed extents are included in the logical number of pages returned by MON_GET_TABLE. This value might be less than the amount of space physically allocated for the object. This can happen when you use the REUSE STORAGE option of the TRUNCATE statement. This option causes storage allocated for the table to continue to be allocated, although the storage will be considered empty. In addition, the value for this monitor element might be less than the amount of space logically allocated for the object, because the total space logically allocated includes a small amount of additional meta data. To retrieve an accurate measure of the logical or physical size of an object, use the ADMIN_GET_TAB_INFO_V97 function. This function provides more accurate information about the size of objects than you can obtain by multiplying the number of pages reported for this monitor element by the page size.