May 28 ’08

z/Data Perspectives: Use Real Time Statistics to Automate Your Database Maintenance

by Editor in z/Journal

Real Time Statistics (RTS) have been available for several years now, but they’re still only nominally implemented. Given all the benefits that can be accrued by using RTS, it’s flabbergasting they haven’t been more widely embraced by DB2 administrators.

With RTS, DB2 gathers performance and maintenance statistics about database objects “on the fly,” without having to run a utility program. DBAs are accustomed to scheduling the RUNSTATS utility to gather database statistics. You can think of RTS as similar to RUNSTATS, but without having to worry about when and how to run it.

RTS can’t completely replace RUNSTATS because RTS are never used by the optimizer to determine access paths. But the RTS statistics can be used by DBAs to determine when to administer and maintain their DB2 databases.

If you think about this for a moment, RTS can help you minimize RUNSTATS CPU consumption. You use RUNSTATS for two reasons:

• To gather statistics used by the optimizer

• To gather statistics to be used by the DBA.

After enabling RTS, you can eliminate any RUNSTATS jobs that are being run simply to obtain statistics that help you determine when to reorganize your databases. Thereafter, RUNSTATS needs to be run only for SQL optimization purposes. Indeed, by specifying UPDATE ACCESSPATH to direct RUNSTATS to collect only optimization statistics, you never need to collect any other statistics using RUNSTATS once RTS has been implemented.

Some shops may be able to dramatically reduce the number of RUNSTATS jobs required. If you rarely, or never, REBIND your plans and packages, and you don’t use dynamic SQL in production, then RUNSTATS will rarely need to be run. Of course, I’m not advocating the avoidance of rebinding your applications—in truth, I would recommend just the opposite! I’m merely pointing out an opportunity to save CPU by minimizing RUNSTATS executions if you use this admittedly suboptimal approach to rebinding.

An additional benefit of RTS over RUNSTATS is that they’re more up-to-date. Heck, the phrase “real time” is right in their name. That means the queries you run against the RTS tables will be more accurate than those you run against the standard DB2 catalog tables, which will be only as recent as your last RUNSTATS. So your decision criteria will improve when they’re based on RTS instead of RUNSTATS statistics.

Optimally, you would use one of the products on the market that automates maintenance tasks based on RTS, but with some work you could “roll your own” by writing some code that feeds a scheduler. Of course, the cost of a product can be offset by the savings and accuracy it offers; most DBAs have enough to do without worrying about maintaining additional code in a roll-your-own solution.

Keep in mind, too, that DB2 9 for z/OS brings improvements to RTS. First, the RTS tables are moved to the DB2 catalog. Previously, users were responsible for creating and managing these tables outside the scope of the DB2 catalog. Additionally, some new statistics are provided in DB2 9, the most important of which offers usage information for indexes.

Let’s talk about that in a little more detail. The LASTUSED column in the SYSINDEXSPACESTATS table contains a date indicating the last time this 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.

A recurring historical problem in DB2 has been determining whether or not an index is being used. You can always query your PLAN_TABLEs for static SQL, but what about dynamic? That’s more difficult. Now, you can simply query the LASTUSED column to see when the index was last used. Of course, you will have to give it some time, as you might have an index supporting a rarely used query. Most shops have queries and programs that run quarterly, or even annually, but nevertheless are very important. So, don’t just start dropping indexes a month after you’ve migrated to DB2 9!

In DB2 9, RTS also takes advantage of the new data types, so RTS will better document tablespace and index storage. The TOTALENTRIES column (for indexes) and SPACE, TOTALROWS, DATASIZE, and UNCOMPRESSED_DATASIZE columns (for tablespaces) all use the BIGINT data type, which can handle values as large as 9,223,372,036,854,775,807. That’s nine quintillion, if you’re counting.

However, the biggest advantage of RTS is the same as it was on day one—the ability to use accurate, up-to-date statistics about DB2 objects to make administration decisions. The newer features have only improved our ability to do so. Continuing to ignore RTS will only make your administration efforts more difficult … and less accurate! Z