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
prior to 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 overhead,
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 desired. 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