Monday, March 8, 2010

DB2 Performance and cost savings tip: Don't maintain unused stuff (LASTUSED)

When you enjoying a regular life, over time you usually accumulate a lot of stuff. Some (most?) of that stuff is rarely used, but you have to dust off or clean it anyway, you somehow pay for keeping it through shelves or cabinets you need, through rent or paying off credits. That's one of the reasons why Spring cleaning has been invented (and my wife repeatedly asks me to clean up my home office). Anyway, the point I wanted to make is that it is a good idea to look for unused stuff to make housekeeping simpler and cheaper. The same applies to database systems.

It seems that the DB2 team dusted off an old feature requests and shipped it in DB2 9.7 Fixpack 1: a LASTUSED column in some catalog tables. With the help of these columns and the new infrastructure in the background it is possible to determine the date of when a database object was last used. If you don't use an index, why maintain it, slow down IUD operations and pay for storage? If an MQT (Materialized Query Table) is not used, it either means you can drop it or you have query execution plans to look at and to figure out why it's not used anymore.

The LASTUSED column of type DATE is available for tables (SYSCAT.TABLES), for indexes (SYSCAT.INDEXES), data partitions (SYSCAT.DATAPARTITIONS), and packages. One thing to note is that the information is updated asynchronously, i.e., if a table is used only once a week the LASTUSED value for it isn't updated the same second or minute the table has been accessed. That's done to avoid turning the catalog into a hotspot.

So much for today, I have to clean up my desk now and sort through old stuff...