In a couple of entries that I posted to the blog I maintained while working as an independent DB2 consultant (one written this past summer, and the other in the spring of 2009), I mentioned the LASTUSED column of the DB2 for z/OS catalog table SYSIBM.SYSINDEXSPACESTATS. This column was introduced with DB2 9 for z/OS (indeed, the SYSINDEXSPACESTATS table itself was introduced with DB2 9 -- it's the moved-to-the-catalog version of the formerly user-defined INDEXSPACESTATS table, which holds index-related real-time statistics). LASTUSED provided -- finally -- an easy way to identify indexes that are just taking up space and which, therefore, should be candidates for being dropped (and potentially replaced with indexes that actually help query performance). You don't think that finding useless indexes was a challenging task before LASTUSED? It wasn't, if all of your SQL was static -- you'd just look at the SYSPACKDEP catalog table to see if any packages were dependent on a given index. That technique, however, didn't help in environments in which dynamic as well as static SQL statements were executed. How could you determine whether or not an index helped the performance of dynamic SQL statements? Believe me, this was no easy thing. LASTUSED changed that situation. It shows the last date on which an index was used by an SQL DML statement (a static or dynamic SELECT, FETCH or "searched" UPDATE or DELETE -- the latter term referring to UPDATE and DELETE statements containing one or more predicates) or for referential integrity constraint enforcement purposes (you wouldn't want to be searching for foreign key matches without an index on said foreign key). The column has a default value, and if you see that default value in a row for an index in SYSINDEXSPACESTATS, chances are that index is not boosting the performance of any SQL statements.
It's that default value that I want to talk about. See, a whole lot of mainframe DB2 people (including myself, up until about two days ago) think that the default value of the LASTUSED column is '0001-01-01' (i.e., January 1 of the year 1). Believe it or not, the default value of LASTUSED has NEVER been '0001-01-01'. It is, and always has been, NULL. So, if you're running DB2 9 in New Function Mode, and you do a SELECT on SYSIBM.SYSINDEXSPACESTATS, and you see '----------' in the LASTUSED column for an index (a string of dashes is the typical representation of the null value when you query the catalog using a tool such as SPUFI), that's an index that hasn't been used lately to performance-enhancing effect (but give it a little more time, and then do a little checking around before dropping the seemingly useless index, just to make sure that it really isn't needed).
I'll close this entry with an interesting piece of related information: DB2 9.7 for Linux, UNIX, and Windows delivered the LASTUSED catalog column for users of that DB2 platform (it's a column of several catalog views, including SYSCAT.INDEXES). The default value of the column? '0001-01-01'. Gotta love it.
