DB2 Version 10.1 for Linux, UNIX, and Windows

MON_GET_TABLE_USAGE_LIST table function - Returns information from a table usage list

The MON_GET_TABLE_USAGE_LIST table function returns information from a usage list defined for a table.

Syntax


>>-MON_GET_TABLE_USAGE_LIST--(-usagelistschema--,--------------->

>--usagelistname--,-member--)----------------------------------><

The schema is SYSPROC.

Table function parameters

usagelistschema: An input argument of type VARCHAR(128) that specifies a valid schema name in the currently connected database when calling this function. If the argument is null or an empty string, usage lists are retrieved in all schemas in the database. If the argument is specified, usage lists are only returned for the specified schema.
usagelistname: An input argument of type VARCHAR(128) that specifies a usage list defined for a table that resides in the currently connected database when calling this function. If usagelistname is null or an empty string, then all usage lists defined for a table from the schemas identified by the usagelistschema that exist are retrieved. If specified, only the usage list specified from the schemas identified by the usagelistschema is returned.
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 active database members. If the NULL value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

Retrieve the usage list USL_MON_PAYROLL from member 3

SELECT * FROM TABLE(
	MON_GET_TABLE_USAGE_LIST(NULL, 'USL_MON_PAYROLL', 3))

USAGELISTSCHEMA USAGELISTNAME   TABSCHEMA TABNAME  
--------------- --------------- --------- -------  
ISAYYID         USL_MON_PAYROLL ISAYYID   T1       
ISAYYID         USL_MON_PAYROLL ISAYYID   T1       

MEMBER  . . .  LAST_UPDATED                . . .
------  . . .  --------------------------  . . .
     3  . . .  2011-07-06-10.20.22.727803  . . .
     3  . . .  2011-07-06-10.20.58.202161  . . .

2 record(s) selected.

Usage notes

Each row returned by this function represents the total number of times (num_references) a unique section (DML statement only, executable ID) has referenced a particular object during a particular time interval (monitor interval ID) since being added to the list. The statistics collected for this row represents the total aggregated value across these executions during this time interval.

Use the num_ref_with_metrics column instead of the num_references column when computing averages, since the num_references column counts all executions of the section, regardless of whether or not the execution of the section contributed to the metrics that are reported.

Metrics collected by this function are controlled at the database level using the mon_obj_metrics configuration parameter. By default, metrics collection is enabled.

Information returned

Table 1. Information returned for MON_GET_TABLE_USAGE_LIST
Column Name	Data Type	Description
USAGELISTSCHEMA	VARCHAR (128)	usage_list_schema - Usage list schema
USAGELISTNAME	VARCHAR (128)	usage_list_name - Usage list name
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name
TABNAME	VARCHAR(128)	table_name - Table name
MEMBER	SMALLINT	member - Database member
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier
EXECUTABLE_ID	VARCHAR(32) FOR BIT DATA	executable_id - Executable ID
MON_INTERVAL_ID	BIGINT	mon_interval_id - Monitor interval identifier
LAST_UPDATED	TIMESTAMP	last_updated - Last update time stamp
NUM_REFERENCES	BIGINT	num_references - Number of references
NUM_REF_WITH_METRICS	BIGINT	num_ref_with_metrics - Number of references with metrics
ROWS_INSERTED	BIGINT	rows_inserted - Rows inserted
ROWS_DELETED	BIGINT	rows_deleted - Rows deleted
ROWS_UPDATED	BIGINT	rows_updated - Rows updated
ROWS_READ	BIGINT	rows_read - Rows read
OVERFLOW_CREATES	BIGINT	overflow_creates - Overflow creates
OVERFLOW_ACCESSES	BIGINT	overflow_accesses - Accesses to overflowed records
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
LOCK_WAIT_TIME_GLOBAL	BIGINT	lock_wait_time_global - Lock wait time global
LOCK_WAITS	BIGINT	lock_waits - Lock waits
LOCK_WAITS_GLOBAL	BIGINT	lock_waits_global - Lock waits global
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
LOCK_ESCALS_GLOBAL	BIGINT	lock_escals_global - Number of global lock escalations
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests
OBJECT_DATA_L_READS	BIGINT	object_data_l_reads - Buffer pool data logical reads for a table
OBJECT_DATA_P_READS	BIGINT	object_data_p_reads - Buffer pool physical data reads for a table
OBJECT_DATA_GBP_L_READS	BIGINT	object_data_gbp_l_reads - GBP data logical reads for a table
OBJECT_DATA_GBP_P_READS	BIGINT	object_data_gbp_p_reads - GBP data physical reads for a table
OBJECT_DATA_GBP_INVALID_PAGES	BIGINT	object_data_gbp_invalid_pages - GBP invalid data pages for a table
OBJECT_DATA_LBP_PAGES_FOUND	BIGINT	object_data_lbp_pages_found - LBP data pages found for a table
OBJECT_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool
OBJECT_XDA_L_READS	BIGINT	object_xda_l_reads - Buffer pool XDA data logical reads for a table
OBJECT_XDA_P_READS	BIGINT	object_xda_p_reads - Buffer pool XDA data physical reads for a table
OBJECT_XDA_GBP_L_READS	BIGINT	object_xda_gbp_l_reads - GBP XDA data logical read requests for a table
OBJECT_XDA_GBP_P_READS	BIGINT	object_xda_gbp_p_reads - GBP XDA data physical read requests for a table
OBJECT_XDA_GBP_INVALID_PAGES	BIGINT	object_xda_gbp_invalid_pages - GBP invalid XDA data pages for a table
OBJECT_XDA_LBP_PAGES_FOUND	BIGINT	object_xda_lbp_pages_found - LBP XDA data pages found for a table
OBJECT_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool