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
>>-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
- 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