Dear Esteemed Listers,
On Db2 for z/OS (we are at V11 NFM), the Real Time Statistics (RTS) table SYSIBM.SYSINDEXSPACESTATS has a LASTUSED column.
Per the SQL Reference, it is defined as:
The date when the index is used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints. For a data-partitioned secondary index, this column is only updated for one partition, even though more than one partition is accessed. The default value is NULL.
I see some production indexes which, based on LASTUSED appear to be unused.
I'm wondering how reliable this column is, as one would like to drop unused indexes, but of course would like to avoid a performance degradation.
I've read that there is the possibility that when DB2 externalizes the RTS data to disk that if it is unable to do so, some data could be lost. I think this would be a relatively rare occurrence.
I also see there is a column in the same table named REORGINDEXACCESS.
This field is defined as:
The number of times the index was used for SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce referential integrity constraints, or since the object was created. For hash overflow indexes, this is the number of times DB2 has used the hash overflow index. A null value indicates that the number of times the index has been used is unknown.
Excluding partitioned indexes for now, if I find an index that has an old LASTUSED date (e.g. 6 or 12 months old or more) or a NULL LASTUSED, AND I find a REORGINDEXACCESS value of zero for the same index, could I be reasonably assured that this index could be dropped?
Thanks in advance for any assistance.