Usage lists

A usage list is a database object that records each DML statement section that references a particular table or index. A section is the executable form of the query. Statistics are captured for each statement section as it executes. Use usage lists when you want to determine which DML statements, if any, affected a table or index.

Data is collected in a usage list only when the usage list is active. Each entry in a usage list contains data for every DML statement that references the table or index for which the usage list was created. Each entry includes information about the number of times that the section executed and aggregate statistics indicating how the section affected the table or index across all executions.

References in the list can be aggregated by the values that are listed in the following table.
Table 1. Aggregation values for usage list references
Value Description
executable_ID Identifies the SQL statement that was executed.
mon_interval_ID Identifies the monitoring interval at the time that the executable_ID was added to the usage list.

Consider the following example of using usage lists. As part of routine monitoring, you see a high value for the rows_read monitor element for a specific table in the output for the MON_GET_TABLE table function. You can use a usage list on that table to identify which DML statements contributed to the high value. If you determine that a problem exists, you can use the statistics from the usage list to determine which specific statements might require further monitoring or tuning.

You can create more than one usage list for a table or index. However, activating more than one usage list at a time might negatively affect database performance and memory usage.


The following restrictions apply to usage lists:
  • Usage lists can capture information about only DML statements.
  • You can create a usage list only for untyped tables. The following table types and objects are not supported:
    • Aliases
    • Created temporary tables
    • Detached tables
    • Hierarchy tables
    • Nicknames
    • Typed tables
    • Views
  • You can create a usage list only for the following types of indexes:
    • Block indexes
    • Clustering indexes
    • Dimension block indexes
    • Regular indexes
  • The db2look utility does not extract the DDL statements that are required to create copies of usage lists.