Everything is all very well and good during the development process when you are creating new programs and adding indexes to your DB2 tables to support your SQL queries. But over time, new applications and systems are added and business requirements change causing database and program changes. What was needed before may no longer be necessary. Programs that once were valuable become less so… and eventually lay dormant. Indexes that once were needed to ensure optimal performance become obsolete as tables, queries, and programs change.

Wouldn’t it be nice to be able to clean up your system? To remove old programs that are no longer needed? To drop old indexes that no longer provide value? But how can we confirm that “things” are no longer needed? That has always been difficult and is probably the reason that many DB2 environments have packages and indexes laying around collecting dust, impeding performance, and offering nothing in return.

It is time to clean house and DB2 for z/OS provides us with the mop and broom to do so.

Did you know that DB2 V9 added a new column to the Real Time Statistics to help identify unused indexes? The LASTUSED column in the SYSINDEXSPACESTATS table contains a date indicating the last time the index was used. Any time the index is used to satisfy a SELECT, FETCH, searched UPDATE, searched DELETE, or to enforce a referential constraint, the date is updated.

This helps to solve the problem of determining whether or not an index is being used. Standard operating advice is to DROP or delete anything that is not going to be used. But that advice never offered much helpful guidance in the way of determining whether something is actually used or not.

In the past, you might have chosen to query your PLAN_TABLEs or the plan and package dependency tables in the DB2 Catalog for static SQL. But what about dynamic SQL? That was more difficult. But as of DB2 V9, you can simply query the LASTUSED column to see when the index was last used. The LASTUSED date is by partition. So, for a partitioned index, the last used date of each partition in the index should be checked.

Of course, you will have to give it some time because you might have an index supporting a rarely used query. Many shops have queries and programs that run quarterly, or even annually, but nevertheless are very important... and you wouldn't want to drop indexes on those queries even though they do not run frequently because when they do run, they are important... And let’s not forget those queries that are not predictable. You know what I’m talking about… the query run by your boss or the CEO but is not scheduled. It may not be run for long periods of time, even a year or two, but when it is run the big cheese expects it to run quickly… so be careful before dropping those indexes.

Examine the LASTUSED column over time to determine which indexes are truly not being used, and then DROP the unused indexes. The time period must be dictated by the situation, user and practices of your organization.

Next let’s talk about a recurring problem for programmers and IT folks in general: and that is trying to determine whether or not a particular program is still required. As your organization grows and the number of programs increases, keeping track of them all can become quite difficult. As administration and management burdens increase, a common desire is to get rid of programs that are no longer being used. But it can be difficult to determine which programs are no longer used.

You can always "ask around," but few IT professionals would be willing to delete anything based on such a general tactic. Another common method is to review performance reports or extracts from a performance warehouse. But perhaps your performance traces are not turned on all the time.

The question is probably more common in DB2 environments because of the plans and packages that consume storage and "sit around" taking up space if their associated program is no longer being used.

Well, for DB2 professionals this type of question becomes easier to answer once you migrate to DB2 10 for z/OS. DB2 maintains a new column, LASTUSED, in the DB2 Catalog. The column exists in both SYSIBM.SYSPACKAGE and SYSIBM.SYSPLAN and is defined as a DATE data type. The date is changed when the package header is requested from EDM. The column is also maintained for triggers and stored procedures.

Of course, just like with indexes, you will have to give it some time before your free up those “unused” plans and packages… because you might have a program that is used only rarely, yet still used. Remember those queries and programs that run quarterly, annually, or sporadically, but nevertheless are very important.

Armed with the LASTUSED column in the DB2 Catalog for indexes, plans, and packages you can perform your own Spring cleaning of unused objects… just be careful and make sure you have backed up copies of the source (program code, index DDL) before cracking the ENTER key on those DROP and FREE statements!