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