DB2 Version 10.1 for Linux, UNIX, and Windows

MON_GET_INDEX_USAGE_LIST table function - Returns information from an index usage list

The MON_GET_INDEX_USAGE_LIST table function returns information from a usage list defined for an index.

Syntax


>>-MON_GET_INDEX_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. The default is NULL.
usagelistname: An input argument of type VARCHAR(128) that specifies a usage list defined for an index 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 an index 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. The default is NULL.
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

Return the usage list USL_MON_IND1 from all database members

SELECT * FROM TABLE(
MON_GET_INDEX_USAGE_LIST(NULL, 'USL_MON_IND1', -2))

USAGELISTSCHEMA    USAGELISTNAME    INDSCHEMA      INDNAME         MEMBER
------------------ ---------------- -------------- --------------- ------
ISAYYID            USL_MON_IND1     ISAYYID        I1              0
ISAYYID            USL_MON_IND1     ISAYYID        I1              0


DATA_PARTITION_ID
-----------------
                -
                -


EXECUTABLE_ID
-------------------------------------------------------------------
x'0100000000000000490000000000000000000000020020110706093802577065'
x'01000000000000004B0000000000000000000000020020110706093825981548'


MON_INTERVAL_ID     LAST_UPDATED               NUM_REFERENCES
---------------------------------------------- --------------------
                   02011-07-06-09.38.15.881668                    1
                   02011-07-06-09.38.25.984147                    1  
            
                   
NUM_REF_WITH_METRICS OBJECT_INDEX_L_READSOBJECT_INDEX_P_READS 
-------------------- ---------------------------------------- 
                   1                    1                   0 
                   1                    1                   0 

       
 OBJECT_INDEX_GBP_L_READS OBJECT_INDEX_GBP_P_READS 
 ------------------------ ------------------------ 
                        0                        0 
                        0                        0 
                        
OBJECT_INDEX_GBP_INVALID_PAGES OBJECT_INDEX_LBP_PAGES_FOUND                        
------------------------------ ----------------------------
                             0                            0                           
                             0                            0
                             

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_INDEX_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
INDSCHEMA	VARCHAR (128)	index_schema - Index schema
INDNAME	VARCHAR (128)	index_name - Index 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
OBJECT_INDEX_L_READS	BIGINT	object_index_l_reads - Buffer pool index logical reads for an index
OBJECT_INDEX_P_READS	BIGINT	object_index_p_reads - Buffer pool index physical reads for an index
OBJECT_INDEX_GBP_L_READS	BIGINT	object_index_gbp_l_reads - Group buffer pool index logical reads for an index
OBJECT_INDEX_GBP_P_READS	BIGINT	object_index_gbp_p_reads - Group buffer pool index physical reads for an index
OBJECT_INDEX_GBP_INVALID_PAGES	BIGINT	object_index_gbp_invalid_pages - Group buffer pool invalid index pages for an index
OBJECT_INDEX_LBP_PAGES_FOUND	BIGINT	object_index_lbp_pages_found - Local buffer pool index pages found for an index
OBJECT_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	object_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool