Determining the date a database object was last used
The last date that an object was
used is indicated by the last referenced date (also referred to as
the last used date). The last referenced date is available for indexes,
packages, tables, table data partitions, and materialized query tables
(MQTs).
You can use the last referenced
date to identify objects which have not been used for an extended
period of time and which might be considered as candidates for removal.
The last referenced date is stored in the LASTUSED column
of the corresponding catalog table for the object and accessible through
the catalog view on the table. Usage information in the catalogs is
updated by an engine dispatchable unit (EDU), called db2lused (the
LASTUSED daemon), that runs on the database catalog partition. Every
15 minutes, the LASTUSED daemon gathers usage information for all
objects across all partitions and updates the LASTUSED column in the
corresponding catalog tables to write the information to disk. At
most, the catalog entry for a given object is updated once per day,
which means the same object will not be checked again until a 24 hour
interval has passed. The 15 minute interval was chosen to minimally
affect performance on the database server and is not user configurable.
The updates to the last referenced date are performed asynchronously
and, therefore, object access is not immediately recorded in the catalogs.
Note: If
the corresponding row in a catalog table is locked, an update of usage
information might be deferred until the next 15 minute collection
interval. Also, when a database is deactivated, any usage information
that was not gathered by the LASTUSED daemon before deactivation (for
example, any objects accessed for the first time since the last poll
was done by the daemon) cannot be written to disk. Explicitly activate
the database for this feature to behave as expected.
The
last referenced date is of interest when an object has not been used
for an extended period of time (for example, several months). The
last referenced date is useful in the following cases:
- Tables and table data partitions: can help to identify opportunities to reclaim unused space
- Indexes: can help to identify opportunities to reclaim unused space, avoid unnecessary inserts and maintenance, and can improve compile time by reducing the number of choices for an index to consider
- Packages: can help to detect unused package versions which can be freed
- MQTs: can help to detect unused MQTs, to reclaim unused space, or help to investigate and understand why an MQT is not being used
The following examples describe some specific scenarios
in which the last referenced date can be useful:
- To identify opportunities to save space and maintenance time, you can examine last used information for indexes every year by checking the LASTUSED column in the SYSCAT.INDEXES catalog view. If an index has not been used in the last year, the index can be considered as a candidate for being dropped. The final decision to drop an index remains under your control because there might be circumstances in which dropping an index is not required. For example, you might have a table which is known to be accessed only under emergency or infrequent cases where fast access is critical, or the index for a table might be unique and used to enforce the uniqueness constraint even though it is never explicitly used. The last used date information can be used as an aid in making decisions to remove indexes.
- Your company has internal applications that were deployed on the database and were either replaced or are no longer in use after a period of months or years. The retired applications have been identified as opportunities to save space. The last used date information can be used to identify database objects that are no longer in use and were not cleaned up after an application was retired. For example, these database objects might be tables storing values used to populate a GUI. The last used date for these tables can be found in the LASTUSED column of the SYSCAT.TABLES catalog view and this date can be used as a starting point in the investigation of table objects that can be removed to reclaim space.
For additional information about the LASTUSED column of
the catalog view for a specific database object, particularly which
operations result in an update, see the following topics:
- SYSCAT.DATAPARTITIONS catalog view
- SYSCAT.INDEXES catalog view
- SYSCAT.PACKAGES catalog view
- SYSCAT.TABLES catalog view