Operating Systems

Index usage tracking: For years, customers asked for an easy way to determine if an index is no longer being used. If they knew there was no access against a particular index, it would make it much easier to delete the index, therefore cleaning up the DB2 catalog, and maybe avoiding some unnecessary updates and deletes to that index. There’s now a way to accomplish this. This can be especially important for a data warehouse that frequently refreshes its tables. By removing unused indexes, DB2 avoids updating those indexes during INSERT or LOAD processing.

DB2 9 for z/OS added the column LASTUSED to the Real-Time Statistics (RTS) table SYSIBM. SYSINDEXSPACESTATS. LASTUSED contains the date when the index, described by this row in the catalog table, was last used for a SELECT, FETCH, searched UPDATE, searched DELETE, or used to enforce a referential integrity constraint. This column isn’t updated when the index is changed because of an INSERT or UPDATE. LASTUSED defaults to a value of 1/1/0001. If you find an index with the default date, that index has never been used since moving to DB2 9.

You should wait a while after upgrading to DB2 9 before taking advantage of this feature to make sure the column has the opportunity to be updated.

Also, there’s an APAR, a somewhat older fix (hiper), APAR PK44579 (maintain SYSIBM.SYSINDEXSPACESTATS. LASTUSED for all cases) that you need to apply if you plan to take advantage of this column.

The tables for RTS are now part of the DB2 catalog.

Index usage tracking is available only after you’ve upgraded your DB2 9 subsystem to NFM.

Index compression: One of the more popular solutions to a query performance dilemma is an index. Adding an index can fix many SQL issues. However, adding an index has a cost; the additional index will consume additional disk space. You can try to decide which is the lesser of two evils—the disk space consumption or the poorly running SQL statement. Moreover, DB2 9 now supports some powerful index enhancements. You may suddenly find yourself using even more disk storage for indexes in DB2 9 than you ever thought possible. Of course, DB2 9 does come with a near-perfect solution: index compression.

Even though some query types used in a data warehouse environment can significantly benefit from the addition of an index, it’s possible for a warehouse to reach a point where the indexes have storage requirements that are equal to, if not sometimes greater than, your table data storage requirements.

Index compression in DB2 9 is a possible solution to an index disk storage issue. Early testing and information obtained from first implementers indicate that significant disk savings can be achieved by using index compression. With index compression being measured as high as 75 percent, you can expect to achieve, on average, about a 50 percent index compression rate. Of course, compression carries some cost. In certain test cases, there was a slight decrease in class 1 CPU time accessing a compressed index, but you can expect your total CPU time, class 1 and class 2 SRB CPU time combined, to increase. However, it’s important to remember that a CPU cost is realized only during an index page I/O. Once the index has been decompressed into a buffer pool or compressed during the write to disk, compression adds zero cost.

Unlike data compression, with index compression there’s no performance advantage directly from the use of compression. Index compression is strictly for reducing your index disk storage. If any performance gain occurs, it will show up when the optimizer can take advantage of one of the additional indexes that now exist—an index that may never have been created because of disk space constraints prior to the introduction of index compression.

6 Pages