DB2 10.5 for Linux, UNIX, and Windows

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

Read syntax diagramSkip visual syntax diagram
>>-CREATE USAGE LIST--usage-list-name--FOR--+-TABLE-+--object-name-->
                                            '-INDEX-'                

   .-LIST SIZE 100------------.  .-WHEN FULL WRAP-------.   
>--+--------------------------+--+----------------------+------->
   '-LIST SIZE--integer-value-'  '-WHEN FULL DEACTIVATE-'   

   .-INACTIVE ON START DATABASE-.   
>--+----------------------------+------------------------------><
   '-ACTIVE ON START DATABASE---'   

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). If the usage list is explicitly qualified with a schema name, 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).
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).
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

Examples