DB2 10.5 for Linux, UNIX, and Windows

MON_GET_USAGE_LIST_STATUS table function - Returns the status on a usage list

The MON_GET_USAGE_LIST_STATUS table function returns current status of a usage list.

Syntax


>>-MON_GET_USAGE_LIST_STATUS--(-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 that resides in the currently connected database when calling this function. If usagelistname is NULL or an empty string, then the status for all usage lists from the schemas identified by the usagelistschema that exist are retrieved. If specified, only the status for 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

Usage notes

The MON_GET_USAGE_LIST_STATUS function does not report information about usage lists that are in the released state. A usage list is considered to be in the released state if it is defined and has not been activated (explicitly or automatically) or has been released using the SET USAGE LIST STATE statement.

If a usage list is activated (explicitly or automatically) then the state of the usage list is set to activation pending and the memory allocation for the usage list is deferred until the table or index for which the usage list is defined is first referenced by a section. At which point the state of the usage list is set to active. If the memory for the usage list cannot be allocated, then the state of the usage list is set to failed and it must be explicitly activated using the SET USAGE LIST STATE statement.

Information returned

Table 1. Information returned for MON_GET_USAGE_LIST_STATUS
Column Name	Data Type	Description
USAGELISTSCHEMA	VARCHAR(128)	usage_list_schema - Usage list schema
USAGELISTNAME	VARCHAR(128)	usage_list_name - Usage list name
OBJECTSCHEMA	VARCHAR(128)	object_schema - Object schema
OBJECTNAME	VARCHAR(128)	object_name - Object name
OBJECTTYPE	CHAR(1)	Type of object. One of: I - For Index T - For Table
MEMBER	SMALLINT	member - Database member
DATA_PARTITION_ID	INTEGER	data_partition_id - Data partition identifier
STATE	CHAR(1)	usage_list_state - Usage list state
LAST_STATE_CHANGE	TIMESTAMP	usage_list_last_state_change - Last state change
LIST_SIZE	INTEGER	usage_list_size - Usage list size
USED_ENTRIES	INTEGER	usage_list_used_entries - Usage list used entries
LIST_MEM_SIZE	INTEGER	usage_list_mem_size - Usage list memory size
WRAPPED	CHAR(1)	usage_list_wrapped - Usage list wrap indicator