CREATE USAGE LIST statement

The CREATE USAGE LIST statement defines a usage list. A usage list is a database object for monitoring all unique sections (DML statements) that have referenced a particular table or index during their execution.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include one of the following privileges:

DBADM authority
SQLADM authority

Syntax

Description

usage-list-name

Names the usage list. The usage-list-name, including the implicit or explicit qualifier, must not identify a usage list that is described in the catalog (SQLSTATE 42710). The schema name must not begin with the characters 'SYS' (SQLSTATE 42939).

TABLE object-name

Designates the table for which the usage list is defined. The object-name, including the implicit or explicit qualifier, must specify a table defined in the catalog (SQLSTATE 42704). The name must not specify an alias, catalog table, created temporary table, hierarchy table, detached table, nickname, typed table, or view (SQLSTATE 42809). The schema name must not begin with the characters 'SYS' (SQLSTATE 42809)

INDEX object-name

Designates the index for which the usage list is defined. The object-name, including the implicit or explicit qualifier, must specify an index defined in the catalog (SQLSTATE 42704). Indexes defined on tables other than untyped tables or materialized query tables are not supported (SQLSTATE 42809). The name must specify a physical index; Block Indexes (BLOK), Clustering indexes (CLUS), Dimension block indexes (DIM), Regular indexes (REG), and Physical indexes over XML column (XVIP). All other index types are not supported (SQLSTATE 42809). The schema name must not begin with the characters 'SYS' (SQLSTATE 42809)

LIST SIZE integer-value

Specifies that the size of this list is integer-value entries. The minimum size that can be specified is 10 and the maximum is 5000 (SQLSTATE 428B7). The default size is 100 entries.

WHEN FULL

Specifies what action is performed when an active usage list becomes full. The default is to wrap when the list becomes full.

WRAP: Specifies that the usage list wraps and replaces the oldest entries.
DEACTIVATE: Specifies that the usage list deactivates.

INACTIVE ON START DATABASE

Specifies that the usage list is not activated for monitoring whenever the database is activated. Collection must be explicitly started using the SET USAGE LIST statement. This clause is the default.

ACTIVE ON START DATABASE

Specifies that the usage list is automatically activated for monitoring whenever the database is activated.

Notes

Tracking sections with unique keys: A usage list keep tracks of all unique sections (DML statements only) that have referenced a particular object. References are aggregated within the list with the unique key of executable ID, representing the section doing the reference, and the monitor interval ID at the time of the reference. Each list entry keeps a count of section executions related to that entry and a set of statistics outlining the affect that the section had on the object across those executions.
Usage list release time: A usage list is set to released when the CREATE USAGE LIST statement is committed.
Memory allocation: Memory is allocated the first time that the object for which the usage list is defined is referenced by a section.
Memory allocation in a partitioned database environment or Db2® pureScale® environment: If the state of a usage list for a partitioned table or index is set to active, memory is allocated for each data partition when the data partition is first referenced by the section. Similarly, in a partitioned database environment or Db2 pureScale environment, memory is allocated at each active member. If a member is unavailable at the time of activation, then the memory is allocated when the member is next activated (if the state of the usage list is still set to active). This also applies when a member is added to the cluster.
State of the usage list when specifying WHEN FULL DEACTIVATE: If the usage list was created with the clause WHEN FULL DEACTIVATE, then the state of the usage list at each member is set to inactive independently. Similarly, for partitioned tables and indexes, the state of the usage list for each data partition is set to inactive independently.
Implicit reactivation of an active usage list: If the state of an INACTIVE ON START DATABASE usage list is set to active in a partitioned database environment or Db2 pureScale environment, then its behavior is similar to the ACTIVE ON START DATABASE clause until the state of the usage list is explicitly set to inactive or the instance is recycled. That is, if the state of a usage list is active when a database member is deactivated or offline, and that database member is subsequently reactivated, the usage list for this member is also implicitly reactivated.
Inactive usage lists remain inactive upon database member reactivation: If the state of an ACTIVE ON START DATABASE usage list is set to inactive in a partitioned database environment or Db2 pureScale environment, then its behavior is similar to the INACTIVE ON START DATABASE clause until the state of the usage list is explicitly set to active or the instance is recycled. That is, if the state of a usage list is inactive when a database member is deactivated or offline, and that database member is subsequently reactivated, the state of the usage list for this member will remain inactive.
Multiple usage lists: Multiple usage lists can be created for the same table or index, however, it is recommended that only one of them be activated. Activating all of them affects database performance and memory usage.
Activating and deactivating usage lists: See the Notes section for the SET USAGE LIST STATE statement regarding activation and deactivation of the usage list.
Usage list size considerations: When the state of a usage list is set to active, the memory for the usage list is allocated from the monitor heap. At the maximum list size setting, the usage list is approximately 2MB. For partitioned tables or indexes, memory is allocated for each data partition. For example, if a partitioned table has three data partitions defined, approximately 6MB of memory is allocated. Therefore, activating multiple usage lists imposes more memory requirements on the monitor heap. It is therefore suggested that a reasonable list size is selected, or that you set the mon_heap_sz configuration parameter to AUTOMATIC so that the database manager manages the monitor heap size.
Performance considerations: To maintain high performance, create usage lists such that they are limited to the amount required to gather the information you need. Each usage list requires system memory; system performance can degrade as additional usage lists are activated.

Examples

Example 1: Create a usage list USL_ACC for table SAYYID.ACCOUNTS with a default list size of 100 entries.
```
CREATE USAGE LIST USL_ACC FOR TABLE SAYYID.ACCOUNTS
```
Example 2: Create a usage list USL_SHOPPING_IND for index BIRD.SHOPPINGIND with a list of 50 entries that wraps when the list becomes full.
```
CREATE USAGE LIST USL_SHOPPING_IND FOR INDEX BIRD.SHOPPINGIND
   LISTSIZE 50
   WHEN FULL WRAP
```
Example 3: Create a usage list USL_PAYROLL for table MIKE.PAYROLL with a list size of 200 entries which will deactivate when the list becomes full and will automatically start collecting whenever the database is activated.
```
CREATE USAGE LIST USL_PAYROLL FOR TABLE MIKE.PAYROLL
   LISTSIZE 200
   WHEN FULL DEACTIVATE
   ACTIVE ON START DATABASE
```
Example 4: Create a usage list USL_EMP for partitioned table JACOBO.EMPLOYEES with a list size of 500 entries which will deactivate when the list becomes full.
```
CREATE USAGE LIST USL_EMP FOR TABLE JACOBO.EMPLOYEES
   LIST SIZE 500
   WHEN FULL DEACTIVATE
```
When the usage list is activated for monitoring, then a list of 500 entries will be allocated for each data partition.
Example 5: Create a usage list USL_PARTS for table SHAKTI.PARTS with a list size of 20 entries that will be activated manually on database activation and will wrap when it becomes full.
```
CREATE USAGE LIST USL_PARTS FOR TABLE SHAKTI.PARTS
   LIST SIZE 20
   INACTIVE ON START DATABASE
   WHEN FULL WRAP
```