Using DB2 for z/OS Real-Time Statistics for Smarter Database Management

Unless you are somehow beyond the reach of advertising, you're probably familiar with IBM's "smarter planet" campaign. It's all about leveraging analytics -- the purposeful analysis of timely, relevant information -- to improve decision-making outcomes. If you administer a mainframe DB2 database, you can work smarter by taking advantage of a DB2 feature that, while familiar to many DBAs in an "I've heard of that" sense, is under-exploited to a surprising degree. I'm talking about real-time statistics (aka RTS). Understand real-time stats -- what they are, where you find them, and how you can use them -- and you're on your way to enhancing the efficiency and effectiveness of your DB2 database administration efforts.

I'm sure that most everyone who reads this blog entry is familiar with the database statistics found in the DB2 catalog. These statistics are updated by way of the RUNSTATS utility, and they can be useful for things like identifying tablespaces and indexes in need of reorganization. Still, from a "work smarter" perspective, they are less than ideal. For one thing, they are only updated when you run the RUNSTATS utility (or when you gather and update statistics as part of a REORG or a LOAD utility operation -- more on that in a moment). How often do you do that? Maybe not too frequently, if you have a whole lot of tablespaces in your database. Suppose you run RUNSTATS, on average, once a month for a given tablespace. Could that tablespace end up getting pretty disorganized in the middle of one of those one-month periods between RUNSTATS jobs? Yes, and in that case you wouldn't be aware of the disorganization situation for a couple of weeks after the fact -- not so good.

As for updating catalog stats via REORG and/or LOAD, that's all well and good, but consider this: when you do that, the stats gathered will reflect perfectly organized objects (assuming, for LOAD, that the rows in the input file are in clustering-key sequence). They won't show you how the organization of a tablespace and its indexes may be deteriorating over time.

Then there's the matter of dynamic cache invalidation. ANY time you run the RUNSTATS utility -- no matter what options are specified -- you invalidate SQL statements in the dynamic statement cache. For a while thereafter, you can expect some extra CPU consumption as the statement cache gets repopulated through the full-prepare of dynamic queries that otherwise might have resulted in cache hits.

So, there's goodness in getting frequently updated catalog statistics to help you determine when objects need to be reorganized, but running RUNSTATS frequently will cost you CPU time, both directly (the cost of RUNSTATS execution) and indirectly (the CPU cost of repopulating the dynamic statement cache following a RUNSTATS job). You could avoid these CPU costs by not using catalog stats to guide your REORG actions, relying instead on a time-based strategy (e.g., REORG every tablespace and associated indexes at least once every four weeks), but that might lead to REORG operations that are needlessly frequent for some tablespaces that remain well-organized for long stretches of time, and too-infrequent REORGs for objects that relatively quickly lose clusteredness. And I haven't even talked about tablespace backups. Getting a full image copy of every tablespace at least once a week, with daily incremental copies in-between, is a solid approach to recovery preparedness, but what if you're daily running incremental image copy jobs for objects that haven't changed since the last copy? How could you get smarter about that? And what about RUNSTATS itself? How can you get stats to help you make better decisions about updating catalog statistics?

Enter real-time statistics. This is the name of an item of functionality that was introduced with DB2 for OS/390 Version 7. That was almost 10 years ago, and while the feature has been effectively leveraged by some DBAs for years, it's remains on the edge of many other DBAs' radar screens, largely for two reasons:

1. You (used to) have to create the real-time statistics objects yourself. I'm referring to the real-time statistics database (DSNRTSDB), the real-time stats tablespace (DSNRTSTS), two tables (SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS), and a unique index on each of the tables. Instructions for creating these objects were provided in the DB2 Administration Guide, but some folks just didn't have the time or the inclination to bother with this. Happily, with DB2 9 for z/OS the real-time statistics objects became part of the DB2 catalog -- they are there for you like all the other catalog tables (if your DB2 subsystem is at the Version 8 level and the real-time statistics objects have already been created, when you migrate to DB2 9 any records in the user-created RTS tables will be automatically copied to the RTS tables in the catalog).
2. People had this idea that real-time statistics drive up CPU overhead in a DB2 environment. They really don't. You see, DB2 is always updating the real-time statistics counters anyway, whether or not you make any use of them. What we know as real-time statistics involves the periodic externalization of these counters, and that's a pretty low-cost operation (the default RTS externalization interval is 30 minutes, and you can adjust that by way of the STATSINT parameter of ZPARM).

So, if you are already on DB2 9, take a few minutes and check out the data in the SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS catalog tables (in a pre-9 DB2 environment, the names of the user-defined RTS tables are -- as previously mentioned -- SYSIBM.TABLESPACESTATS and SYSIBM.INDEXSPACESTATS). You'll see that the column names are pretty intuitive (Hmmm, wonder what you'll find in the EXTENTS column of SYSTABLESPACESTATS? Or how about TOTALENTRIES in SYSINDEXSPACESTATS?). The theme is "news you can use," and a primary aim is to help you get to a needs-based strategy with regard to the execution of utilities such as REORG, RUNSTATS, and COPY, versus running these using only time-based criteria. To this end, RTS provides valuable information such as the total number of rows added to a tablespace since it was last reorganized (REORGINSERTS), the number of rows inserted out of clustering sequence since the last REORG (REORGUNCLUSTINS), the number of updates since the last RUNSTATS execution for a tablespace (STATSUPDATES), the number of data-change operations since a tablespace was last image-copied (COPYCHANGES), and the number of index leaf pages that are far from where they should be due to page splits that have occurred since the last time the index was reorganized or rebuilt (REORGLEAFFAR). Note, too, that in addition to the utility-related numbers, RTS provides, in a DB2 9 system, a column, called LASTUSED (in SYSINDEXSPACESTATS), that can help you identify indexes that are just taking up space (i.e., that aren't being used to speed up queries or searched updates or deletes, or to enforce referential integrity constraints).

How will you leverage RTS? You have several options. You can process them using a DB2-supplied stored procedure (DSNACCOR for DB2 Version 8, and the enhanced DSNACCOX delivered with DB2 9). You might find that DB2 tools installed on your system -- from IBM and from other companies -- can take advantage of real-time statistics data (check with your tools vendors). DBAs who know a thing or two about the REXX programming language have found that they can write their own utility-automation routines thanks to RTS. And of course you can write queries that access the RTS tables and return actionable information. I encourage you to be creative here, but to get the juices flowing, here's an RTS query that I've used to find highly disorganized nonpartitioned tablespaces (this particular query was run in a DB2 Version 8 system -- it should work fine in a DB2 9 subsystem if you change TABLESPACESTATS to SYSTABLESPACESTATS):

SELECT A.NAME,
A.DBNAME,
CAST(REORGLASTTIME AS DATE) AS REORGDATE,
CAST(FLOOR(TOTALROWS) AS INTEGER) AS TOTALROWS,
REORGINSERTS,
CAST((DEC(REORGUNCLUSTINS,11,2) / DEC(REORGINSERTS,11,2)) * 100
AS INTEGER) AS PCT_UNCL_INS,
REORGDELETES,
B.PCTFREE,
B.FREEPAGE
FROM SYSIBM.TABLESPACESTATS A, SYSIBM.SYSTABLEPART B
WHERE A.NAME = B.TSNAME
AND A.DBNAME = B.DBNAME
AND TOTALROWS > 10000
AND REORGUNCLUSTINS > 1000
AND (DEC(REORGUNCLUSTINS,11,2) / DEC(REORGINSERTS,11,2)) * 100 > 50
AND A.PARTITION = 0
ORDER BY 6 DESC
WITH UR;

Real-time stats are going mainstream, folks. Be a part of that. Work smart.