Ember’s Best Practices for Runstats and Reorgs

A number of new clients I’ve worked with lately have had minor or major runstats or reorg issues. I have tons of advice on runstats and reorgs across many blog entries, but I don’t have one place where all that advice is brought together. Here is that article. The truth is that reorgs and runstats are complicated, and most DBAs get them wrong or disagree on them in some way. There is no one right way, but there are a vast variety of ways to get it wrong.

Both Runstats and Reorgs

There are a few best practices that apply to both runstats and reorgs.

Dynamic Scripting

The first key thing is to make your runstats and reorg processes dynamic. Don’t write a list of runstats statements once and run that as a script. As new tables are added to your database, you may miss them. This is even more true for reorgs. Don’t reorg every table every week – use reorgchk or your own formulas to determine when reorgs are needed. I’m such a geek that I once actually wrote out SQL that at least came pretty close to calculating each of the reorg formulas myself, and I loved it. Reorgs run shorter if you’re running them regularly and only for the tables where they are needed.

System Tables

Some DBAs will tell you to avoid touching any table where the table or schema name begins with SYS. I go the opposite way. While reorging system tables can cause more contention, some of them are so frequently accessed that runstats and reorgs make all that much more difference. There are some system tables that inplace reorgs will fail on, and that is OK – let them fail. But don’t ignore SYS tables.

Order of Operations

For my weekly reorgs, this is the proper order of operations that I follow:

1. Runstats on all tables
2. Reorgchk (using REORGCHK_TB_STATS and REORGCHK_IX_STATS)
3. Reorg of tables/indexes needing it
4. Runstats on tables that were reorged or had their indexes reorged
5. db2rbind
6. (if activity allows) FLUSH PACKAGE CACHE DYNAMIC

Runstats

Use a Profile

Often to deal with a performance problem, we may need to run a specific set of runstats on a specific table. This can include using sampling as tables become large and runstats time becomes too long. It can include doing runstats on groups of columns, or a number of other things. If you always look for a profile when doing runstats and try to use it, then you’ll always catch these customizations. I wish there were profiles for reorgs so we could document indexes we want to reorg a table on in a more permanent way when we cannot make it a clustering index.

Gather Distribution Statistics

Even if many of your queries use parameter markers and therefore cannot take advantage of distribution statistics, collect them anyway – often the queries they can help are worth it. In an e-commerce database it is possible that most of your normal queries cannot make use of distribution statistics due to the use of parameter markers. However the queries that are more likely to use distribution statistics are the ad-hoc reporting queries that can be so destructive to an OLTP workload.

Let Auto Statement Statistics Run Automatically

Collect automatic statement statistics even if you use none of the other automatic maintenance. This involves setting four parameters to YES:

• AUTO_MAINT
• AUTO_TBL_MAINT
• AUTO_RUNSTATS
• AUTO_STMT_STATS

Automatic Statistics are Not Enough

Even if you’re running automatic runstats, you still need to look for tables that haven’t had runstats in a while and ensure that their stats are updated weekly or monthly. If you have a truly static table, you could skip it, but so few tables are truly static. DB2 uses a metric on how much data in a table has changed to decide if runstats are needed, and I’ve seen it make some major misses, even with DB2 10.5. Often I’ll let automatic stats run, but then have a weekly full scripted stats or have a script that looks for tables with old runstats and only does runstats on them.

Don’t Forget Statistical Views

Statistical views are a feature that may or may not be used, but runstats have to be run on these too. The syntax is slightly different than I use on tables, so it may take a bit to modify your scripts to do this properly. But it is likely worth it, as statistical views are often created to solve a specific problem. That resolution is not maintained unless the statistics are maintained on these tables.

Volatile Tables

Back on DB2 8.2, I experienced a major performance problem when runstats were collected on volatile tables. All the documentation states that the statistics on volatile tables are ignored, so this doesn’t make sense, and yet here I am, still mostly exempting volatile tables from runstats and reorgs. Some applications use the volatile tag not as it was designed (to indicate a table whose carnality varies wildly within the periods between runstats), but to push DB2 to choose index access over table scans, so you need to understand why volatile is being used before taking this approach.
I have also had a client who did runstats only if the current cardinality of the table exceeded what was in SYSCAT.TABLES. The theory there is that they want SQL to run as if the table were at its largest possible size. The issue there was that when stats were collected during the lull in the middle of the night, the table was nearly empty, but during peak volume there was far more data. Despite the volatile flag on the table, the queries performed vastly different in these situations and the worst-case statistics really fixed the problem.

Reorgs

Reorg Based on Reorgchk, but Not Everything with a Flag

The quick and dirty trick that many DBAs use is to look for any flags (*’s) in reorgchk output and reorg tables and all indexes where they are found. While this is marginally better than reorging every table every time, there are still problems with it. Much more detailed decision making needs to go on (i.e. be scripted) so that tables are reorged in the most efficient manner possible.

Consider Metrics that REORGHK Doesn’t

If you have a nice robust reorg script, you can add your own metrics and decision making on when reorgs are needed. Scott Hayes of DBI software makes an excellent case that reorgchk has not been improved in years. When (Overflows * 100)/Rows Read > 3%, REORG a table to mitigate double I/Os. Learn more and find SQL in the TBROVP section of this page from DBI.

Don’t Reorg All Indexes on a Table Based on F4

This is one of my pet peeves. The entire point of the F4 formula is to tell you how well clustered the TABLE is over each index. It is literally impossible for a table to be clustered over every index at once on many tables. And yet the DB2 Knowledge Center states that all indexes for a table should be reorged if F4 is flagged:

If the results of the calculations for Formula 1, 2 and 3 do not exceed the bounds set by the formula and the results of the calculations for Formula 4, 5 or 6 do exceed the bounds set, then index reorganization is recommended.

I don’t key off of F4 that way. I look at F4 and if it is a clustering index, I’ll reorg the table on that index to address issues with F4. Otherwise, I ignore F4. This may result in a few edge cases being missed, but that is acceptable to me. Keying off of F4 often leads to many more reorgs than are required, and a longer overall reorg duration than is expected.

Pay attention to F7 and F8

Formulas F7 and F8 each need a less invasive reorg that is specified with the CLEANUP or CLEANUP PAGES keywords. Pay attention to these and perform the least invasive reorg of indexes that meets the flags of all indexes on the table.

Remember Offline LONGLOBDATA

Especially for older versions, this can be critical. I have seen hundreds of GB of space cleared up when reorgs using LONGLOBDATA are done on databases that were created on 9.5 or before. Analyzing to determine if this is an issue is complex, but if you have a lot of deletes, you need to get to reclaimable storage for your LOB data. LONGLOBDATA reorgs certainly have their place. You can also accomplish the same thing using the ADMIN_MOVE_TABLE tool.

Tables Reorged INPLACE are Likely to Need Index Reorgs

When you perform an offline table reorg, the indexes are rebuilt as a part of that reorg. When you perform an online reorg, you can either script the reorg of indexes, or you can simply check the index statistics carefully to see if index reorgs are needed. Often they will be.

Individual Indexes Cannot be Reorged Unless…

The only valid option for reorging indexes on a non-partitioned table is to reorg all indexes. Individual index reorgs are only supported for nonpartitioned indexes on a partitioned table. The syntax diagram for reorg may be confusing in this manner, but reading the details in the IBM DB2 Knowledge Center makes this clear.

Monitoring Reorgs

Many DBAs have spent a lot of time watching reorgs. This is slightly more interesting than watching paint dry. They may be needed after significant changes or for specific reasons. Sometimes we need to run them within a specific window or be ready to restart them if they fail. Real-time monitoring is easiest to do through the SNAP_GET_TAB_REORG table function for tables. Monitoring index reorgs is incredibly frustrating, and is best done with the db2pd -reorgs index command (reorgs is plural while index is not, grr!). This will scroll screens of text you don’t need, most likely, but hidden in there are details on where DB2 actually is on running index reorgs. It is best is to have the reorgs fully scripted including managing parallel reorgs and scripted killing of the reorgs if you have a hard window-end to make, and let the human DBA review the results during business hours.

Understand What “Online” Means for Reorgs

There is no 100% online reorg. An INPLACE reorg with the NOTRUNCATE option is as close as it gets for tables, and for indexes it comes close with the ALLOW WRITE ACCESS keywords, but both may cause contention (locking phenomena such as lock timeouts and additional lock wait time). See When is a Reorg Really Online? for more information.

Also understand that reorg does NOT respect LOCKTIMEOUT, so often will wait for extended periods of time for the locks it needs. This can be positive, as reorg itself is less likely to fail due to lock timeouts, but can also lead to excessive execution time in busy databases.

Be Flexible and Learn New Things About Reorg

Things change with reorg over time. It is important to re-educate yourself from time to time and learn all you can. For example, in DB2 11.1, DB2 will finally allow reorgs of individual table partitions to be done in place (largely online). This is a great feature if you’re using table partitioning. Also recently added was the option to only reclaim extents at the table level or to only cleanup overflows on an index-by-index basis. These may be useful strategies depending on the challenges you are facing.

Other Blog Entries on Runstats

Runstats – What and Why
Never do runstats on volatile tables
Giving Automatic Maintenance a Fair Try
DB2 Administrative SQL Cookbook: Looking at How Current Statistics Are
DB2 Administrative SQL Cookbook: Finding When Statistics Were Last Collected for a List of Tables
Runstats Issue with Columns where There is a Long Common Prefix in Character Data

Other Blog Entries on Reorgs

DB2 Basics: What is a Reorg?
How to Tell When a Table Reorg is Needed
How to Tell When an Index Reorg is Needed
Do you see a ‘Congested’ State for HADR while Performing Reorgs?
When is a Reorg Really Online
Issue with Online Reorgs on Unformatted Event Monitor Tables