SET USAGE LIST STATE statement

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

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 for Db2 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 environment or 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.