The SET USAGE LIST STATE statement manages the state of
a usage list and the associated data and memory.
This statement is not under transaction control.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. 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 DBADM or SQLADM authority.
Syntax
>>-SET USAGE LIST--usage-list-name--STATE----------------------->
.-=-.
>--+---+--+-ACTIVE--------+------------------------------------><
+-INACTIVE------+
+-RELEASED------+
'-host-variable-'
Description
- usage-list-name
- Identifies the usage list. The usage-list-name,
including the implicit or explicit qualifier, must identify a usage
list that is described in the catalog (SQLSTATE 42704).
- ACTIVE
- Indicates that the usage list is
activated for monitoring. Memory for the usage list is allocated when the table or index is first
referenced by a section. If the usage list is for a partitioned table or index then the memory is
allocated when the data partition is first referenced by a section. In a partitioned database
environment or DB2® pureScale®
environment, memory is allocated at each member. If the usage list is already in the ACTIVE
state then a warning is returned (SQLSTATE 01598).
On activation, the data in the usage list is
removed and collection starts from the beginning of the list.
- INACTIVE
- Indicates that the usage list is
deactivated for monitoring. If the state of a usage list is already set to INACTIVE then this
keyword is ignored. If the state of the usage list for a partitioned table or index is set to
INACTIVE, then the state of the usage list for each data partition is set to INACTIVE. If the state
is already INACTIVE then this keyword is ignored. Similarly, in a partitioned database
environment or DB2 pureScale environment, the
state of the usage list for each member is set to INACTIVE. If the state is already INACTIVE then
this keyword is ignored.
Data collected in the list is not removed when the state of the usage
list is set to INACTIVE.
- RELEASED
- Indicates that the memory
associated with a usage list is released. If the state of the usage list for a partitioned table or
index is set to RELEASED, then the memory associated with each data partition is released. In a
partitioned database environment or DB2 pureScale environment, the
memory associated with each member is released.
Notes
- Determining current state: The current state of
a usage list is determined by using the MON_GET_USAGE_LIST_STATUS
built-in function.
- Considerations forDB2 pureScale
or partitioned database environments: If a usage list for a partitioned table or index
is activated, memory is allocated for each data partition. Similarly, in a partitioned database
environment or DB2 pureScale environment,
memory is allocated at each active member.
- Memory allocation for unavailable members: If a
member is unavailable at the time of activation, then the memory associated
with the usage list for this member is allocated when the member is
next activated (if the state of the usage list is still active). This
also applies when a member is added to the cluster.
- Memory allocation for data partitions that are being added
or attached: For data partitions that are being added or attached,
the memory associated with the usage list for this newly added or
attached data partition is allocated when the next section that references
the partitioned table or index is executed.
- Setting INACTIVE independently: If the usage list
was created with the property, WHEN FULL DEACTIVATE, then the state
of the usage list for each data partition or member 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 ACTIVE ON START DATABASE until the usage list is explicitly deactivated
or the instance is recycled. That is, if state of the 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 implicitly reactivated.
- Definition of 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. Usage lists in the state released are not returned
by the MON_GET_USAGE_LIST_STATUS table function.
- Activation pending, active, and failed states: If
a usage list is activated (explicitly or automatically) then the state
of the usage list is set to activation pending and the memory is allocated
when the table or index is first referenced by the section. At this
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.
- 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 INACTIVE ON START DATABASE until the usage list is explicitly activated
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.
- Activating, deactivating, or
releasing a usage list for a partitioned table or index: If a usage list for a partitioned
table or index is activated, deactivated, or released then the state change applies to each data
partition. Similarly, in a partitioned database environment or DB2 pureScale environment, the
state change applies to each member.
- Usage
list size considerations: When activated, the memory associated
with 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,
the total memory allocated is approximately 6MB. 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.
- Data collection when a usage list is set to INACTIVE:
Data collected in the list is not removed when the state of the usage
list is set to INACTIVE.
- Data access and memory: The data in the list is
still accessible (using MON_GET_TABLE_USAGE_LIST and MON_GET_INDEX_USAGE_LIST
table functions) provided that the memory for the list is allocated.
- Releasing memory: The memory associated with the
usage list is released when one of the following events occurs:
- The usage list is dropped.
- The table or index on which the
usage list is defined is dropped. The memory that is associated with the usage is released for all
data partitions. In a partitioned database environmentor DB2 pureScale environment, the memory that is associated with the usage list is released for all active members.
- When a data partition is detached from a partitioned table or
index. Only the memory associated with the data partition is released.
- When a database member is deactivated. Only the memory associated
with the member is released.
- When the entire instance or database is deactivated. Usage list
data does not persist when the database is deactivated and restarted.
- When memory associated with the usage list is explicitly released
using the SET USAGE LIST STATE statement.