Aug 1 ’06
z/Data Perspectives: It’s Time for Real Time Stats
Real Time Statistics (RTS) is an initial step in IBM’s grand plans to bring autonomic computing to DB2 for z/OS. Introduced after the general availability of Version 7, but before Version 8, RTS provides functionality that maintains statistics about DB2 databases “on-the-fly,” without having to run a utility program.
There seems to be a general wariness “out there” precluding widespread adoption and implementation of RTS. My very unscientific polling of DB2 user group attendees indicates that only a smattering of DB2 shops are using RTS. Maybe after reading this column you’ll re-think your stance if yours is one of the shops that hasn’t yet embraced RTS.
What Are Real Time Stats?
Prior to RTS, the only way to gather statistics about DB2 database structures was by running the RUNSTATS utility. RUNSTATS collects statistical information and stores it in the DB2 Catalog. RTS, on the other hand, runs in the background and automatically updates statistics in two special tables as DB2 data is modified. RUNSTATS is hands-on, whereas RTS is hands-off.
Now don’t misunderstand; the RTS doesn’t replace RUNSTATS. Although several of the statistics are similar, RTS is never used by the optimizer to determine access paths. RTS can be used by DBAs to better administer DB2 databases. Although DB2 is always collecting RTS data, no data will be externalized until you set up the RTS database and tables. The RTS database is named DSNRTSDB and must contain one table space (DSNRTSTS) with two tables:
- SYSIBM.TABLESPACESTATS: contains statistics on table spaces and table space partitions.
- SYSIBM.INDEXSPACESTATS: contains statistics on index spaces and index space partitions.
After you create the RTS database, DB2 puts it into a stopped state. When all the objects are created you must explicitly start the database in RW mode so DB2 can begin to externalize RTS statistics.
There are numerous statistics collected by RTS; each is defined in Appendix G of the IBM DB2 Administration Guide manual. Many columns in the RTS tables show the number of times an operation was performed between the last time a particular utility was run and when the RTS stats are written. For example, STATSINSERT in TABLESPACESTATS indicates the number of records or LOBs that have been inserted after the last RUNSTATS utility was run on the table space or partition. It’s important that you initialize the RTS statistics columns. Most of these columns are nullable, and if you fail to initialize the columns, the RTS values won’t be correct. There are two ways to initialize these columns. For each object for which you want real-time statistics, run the appropriate utility (REORG, RUNSTATS, LOAD REPLACE, REBUILD INDEX, or COPY) to establish a base value from which the delta value can be calculated. Otherwise, you can create an SQL script to gather baseline statistics from the last DB2 Catalog (as set by the last RUNSTATS) and set the appropriate RTS values. You also can control the interval for when DB2 writes real-time statistics. The default interval is 30 minutes. To update the interval, modify the DSNZPARM named STATSINT. Of course, there are other circumstances that cause the externalization of the RTS statistics, including running RUNSTATS, stopping the RTS database, and stopping DB2 MODE(QUIESCE).
Using the Real Time Stats
By implementing RTS, we’ll have more information at our disposal describing our DB2 objects. How should we use it? There are several possible uses, but let’s look at a few. RTS statistics can help determine when to REORG. You could easily write a query showing table spaces with more than 100K changes, more than 25 extents, or more than 50 far indirect references. Or, consider the query in Figure 1, which returns all table spaces having more than 10 percent of their rows as near or far indirect references.
We’ve only skimmed the surface of the many useful statistics offered by RTS. If you haven’t yet deployed them in your shop, it’s time to get RTS up and running.