Usage list memory considerations and validation dependencies

After a usage list is activated, the database manager allocates memory to store the collected data the first time that a section references the object for which the usage list is defined. Throughout the life of the usage list, various actions might affect this memory, invalidate the usage list, or both.

General memory considerations are as follows:
  • Usage list size considerations: Select a reasonable list size or 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.

The following table shows more specifically how various actions affect the allocated memory.

Action Effect Effect if usage list is for a partitioned table or index Effect in a partitioned database environment or Db2® pureScale® environment
After you activate a usage list for the first time, a section references the object for which the usage list is defined. Memory is allocated for the usage list. Memory is allocated for each data partition. For example, if the usage list requires 2 MB of memory, and three data partitions exist, 6 MB of total memory is allocated. Memory is allocated for each member. For example, if the usage list requires 2 MB of memory, and three members exist, 6 MB of total memory is allocated.
You change the size of the usage list. The amount of memory that is associated with the usage list changes the next time that the usage list is activated. The amount of memory that is associated with the usage list for each data partition changes the next time that the usage list is activated. The amount of memory that is associated with the usage list for each member changes the next time that the usage list is activated.
You add or attach a new data partition to the table or index for which the usage list is defined. Does not apply. Memory is allocated for the new data partition the next time that a section references the table or index. Does not apply.
You drop the usage list. The memory that is associated with the usage list is freed. The memory that is associated with the usage list is freed for all data partitions. The memory that is associated with the usage list is freed on all members.
You drop the table or index for which the usage list is defined. The memory that is associated with the usage list is freed and the catalog entry for the usage list is invalidated. The catalog entry can be validated again by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. The memory that is associated with the usage list is freed for all data partitions and the catalog entry for the usage list is invalidated. The catalog entry can be validated again by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. The memory that is associated with the usage list is freed on all members and the catalog entry for the usage list is invalidated. The catalog entry can be validated again by using the ADMIN_REVALIDATE_DB_OBJECTS procedure.
You deactivate the instance or database. The memory that is associated with the usage list is freed. The memory that is associated with the usage list is freed for all data partitions. The memory that is associated with the usage list is freed on all members.
You use the SET USAGE LIST STATE statement to free the memory that is associated with the usage list. The memory that is associated with the usage list is freed. The memory that is associated with the usage list is freed for all data partitions. The memory that is associated with the usage list is freed on all members.
You detach a data partition from the table or index for which the usage list was created. Does not apply. The memory that is associated with the data partition that you detached is freed. Does not apply.
You drop or deactivate a database member. Does not apply. Does not apply. The memory that is associated with the member that you dropped or deactivated is freed.