This month I wish to run through the big three utilities that you are *all* using and make sure that you are up to date and modern with regards to the parameters that we are all using in them.
Automate this!
I know that once I have set up a batch job to automate something I rarely touch it again. And so it often happens that we have written something years ago that works fine but could work much, much better!
Which ones?
Pretty obviously COPY, RUNSTATS and REORG are the meat-and-potatoes of any Db2 Database maintenance scenario and nearly every shop out there has a bought-in Utility generator like SEG’s RealTime DBAExpert, or they have “rolled their own” over the years.
SHARE your Experiences!
What I mean here is that all modern utilities should, with very few (100% understood and 100% required) exceptions, be running only with SHRLEVEL CHANGE. Naturally, there is a “problem” or two here…
- RUNSTATS has historically *not* updated the Real-Time Statistics (RTS) if you run with SHRLEVEL CHANGE. This is very annoying and I have complained for years – but it is what it is. If this bites you the best approach is to add an EXEC SQL to manually update the RTS after every RUNSTATS which our very own RealTime DBAExpert does.
- COPY is *not* recommended if you run with SHRLEVEL CHANGE for RECOVER TOCOPY purposes as it is, by definition, a dirty COPY. This is where FlashCopy and System Level Backup (SLB) technology can come to the rescue. If you use FlashCopy and/or SLB, why do normal sequential COPYs at all?
COPY
Taking an image COPY is one of the absolute standard things we do day in and day out without even truly thinking about it! We *must* safeguard our data and we *must* take regular copies of *everything* but if you have modern disk sub-systems, and we all do these days, why are you not taking System Level Backups (with the VTOC catalog data!) and then just the occasional COPY using FlashCopy with CONSISTENT? Once these FlashCopies have been done you can still copy them to sequential files, like tape, if they must be transported off site – but that is then a secondary process that does not interrupt or hurt real work.
Downside?
There is *always* a downside … For SLBs you need at least *double* the space of your productive Db2 sub-system … Recommended is three times the size … You must make sure that the VTOC Catalog data is also dumped or you cannot use the SLB to recover after a REORG for example. Why so? Well, if the tablespace has “moved” from one disk to another problems used to happen here! This was fixed ages ago by dumping the VTOC Catalog at the same time and should no longer be a concern. The only concerns should be “Can you meet your Service Level Agreements (SLAs) and Recovery Time Objectives (RTOs)?” and “Do I *not* have a single point of failure?” If both can be answered with YES then you are good to go!
RTS is your Buddy!
For COPY the usual rules apply – Do not copy everything everywhere everyday unless SLBs are being used, of course. For the “during the day” copies, FlashCopy CONSISTENT based on updated pages & row count changes in the RTS are the triggers you should use. Sometimes you also need to copy non-updated, and even read-only, data especially if the oldest copy is being threatened with being PURGEd. Basically, you have to work hand-in-hand with your SLAs and RTOs to guarantee that you are up and running again in record time! You should also be running regular checks to guarantee that you are 100% recoverable.
RUNSTATS
RUNSTATS is *only* required when the data has changed significantly, thus affecting the statistics that the Db2 Optimizer uses, or a new index has appeared or been altered and you must “inform” the Db2 Optimizer of these new statistics to choose the best, cheapest and fastest access path.
RTS is your Friend!
Base all RUNSTATS decisions, if no schema changes, on the RTS counters and *not* on any Db2 Catalog column and definitely not on the “every month” or “once a week” style plan!
To BIND or Not To BIND? That is not actually a Question!
Remember that running RUNSTATS without doing a REBIND is relatively pointless as no access path change will ever happen if you do not REBIND. If you are afraid of access path regression, and who isn’t, then always REBIND with APREUSE(ERROR) and APCOMPARE(ERROR) to lock you in to the last “good” access path. But “Buyer beware”, you will never get a better access path either!
What does “relatively pointless” mean, exactly?
Well, it is naturally not 100% true as this completely ignores Dynamic SQL which probably makes up 90% of your SQL workload these days. These *will* use the new statistics *immediately* at the next PREPARE and can thus cause access path problems. This is why RUNSTATS execution times should be carefully chosen and not “willy-nilly” every weekend or second Sunday of the Month!
RUNSTATS Rescued?
There are tools out there, our very own RUNSTATS Rescue for example, that enable you to fall back to a previous set of “good” statistics if the RUNSTATS has indeed killed you. There are bugs in RUNSTATS and sometimes simply bad data in the SYSCOLGROUP as well, both of which can cause the Db2 Optimizer grief!
RUNSTATS went wrong?
Yes, indeed dear readers, RUNSTATS can go wrong… or even worse someone might attempt to manipulate the statistics to try and force some special access path. This is, of course, dangerous and possibly no longer needed, perhaps this update was done years ago and nowadays is not needed. You can download our Statistics HealthCheck which will, very quickly and comprehensively, run though all your Db2 Catalog statistics and point out the bad guys!
How big is yours?
Here I am talking about the widest set of columns that you have in SYSCOLGROUP for a given table. Remember that RUNSTATS must SORT all of these and it uses just *one* sort for *all* of your column groups … Makes me cry when I type this … This has led, and does still lead, to massive amounts of sort work being requested due to excessive SYSCOLGROUP usage which, in turn, is normally fed by overzealous SYSSTATSFEEDBACK usage. In one case there were 23 colgroups all of which were over 1,000 bytes wide on a five-partition tablespace with 120 million rows. A complete waste of time and money to even execute such a beast – even if you had the TBs of sort work handy!!! Please check your colgroups!
REORG
Back in the good old days we had to execute RUNSTATS on every single tablespace just to see if you needed a REORG? Remember that? I really hope you are *not* still doing that!!!
RTS is your Friend, really!
Base all REORG decisions on the RTS counters and *not* on any Db2 Catalog column, unless you have schema changes or DBET requirements for the REORG. If doing a REORG then it is probably also the time to refetch your statistics so an INLINE RUNSTATS is highly recommended. Sampling is allowed these days to save a bit of CPU. There is one large exception here and that is that a REORG INDEX should, normally, never get inline statistics. Why? Well, normally, we REORG INDEX far more often than the base tablespace and this can very quickly lead to statistics drift, up to the point where the Db2 Optimizer starts losing faith in the numbers it is getting. This is then a “very bad thing” and not a place you want to go to!
What’s in an Index anyway?
One extra problem that we have is all the different index types… A PI (Partitioning Index) is no longer required or probably even there anymore, a DPSI (Data Partitioning Secondary Index) is used quite a lot these days but must contain at least the partitioning columns, and then comes the Ugly Big Brother of them all – the NPSI (Non-Partitioned Secondary Index) aka NPI – and is partition independent. This means if you are doing partition-based REORGs you must execute them serially one behind another. Yes, you can use the PART(n, nn:nn, n) style syntax up to a point, but it still does not stop all the trouble! This is a pain and there is no real fix for this. My Rule of Thumb is: 25% or more of all partitions being REORGed then flip it to a TS level REORG. This is probably faster than 20 Parts and 20 times the NPSI being REORGed. As always, your mileage may vary and the cheque is in the post, but you can probably measure this at your site with your own data and easily see where the “break-even” point is. This is also fully supported by our RealTime DBAExpert as well, by the way.
REORG Parameters
Right, hard hats on – here comes the checklist portion. To check that you are running with modern REORG parameters, run through all the following items:
- Verify that your ZPARM REORG_DROP_PBG_PARTS is set to DISABLE (The default). Why? If you allow REORGs to prune unused PBG Partitions at the end then that will completely stop Point in Time (PiT) recovery from working. If, on the other hand, you *never* want to, or will need to, do a PiT then it does not matter!
- Verify that your (MAXRO * DRAIN_WAIT parameters) are greater than (ZPARM IRLMRWT * four). This is to try and force the REORG to die before any transactions, which is what you would normally want! Better to have wasted some CPU and I/O on a dud REORG than killed your CEO doing a transaction!
- Check that the following REORG parameters are set: DRAIN ALL, LASTLOG NO, DROP_PART NO, RETRY less than 30 and RETRY_DELAY > 10. Note that for LASTLOG NO you must have DRAIN ALL and be using SHRLEVEL CHANGE, by the way.
- Check that your SORTDEVT is set and a good SORTNUM value in the range 2 – 255 to maximize sort processing – unless you have the ZPARM IGNSORTN set to YES in which case the SORT program does the allocation much better than we can!
- Inline statistics should only be used for TS/TP and not for IX/IP level.
To BIND or Not To BIND? Still not a Question!
Remember that after running an inline RUNSTATS or REORG without doing a REBIND is relatively pointless as no access path change will ever happen if you do not REBIND. The same APREUSE(ERROR) and APCOMPARE(ERROR) advice applies here as it did for standalone RUNSTATS – lock yourself in to the last “good” access path if access path regression frightens you.
What does “relatively pointless” mean, again?
Here, the answer is different and more interesting! For a REORG it is naturally not 100% true, because a REORG *can* drastically improve performance, especially a reorg of an index, *without* a rebind being required. Performance indexes are restored to health after a REORG INDEX and as for the tablespace, well, all the data is put back in clustering sequence and all empty space is tidied up, thus making sequential access and prefetch much, much faster. The CLUSTERING index drives the data order in the tablespace, of course.
REORG Avoidance
The best REORG is simply no REORG, and you can avoid REORGs in various ways. If the clustering is always going wrong, it could well be that the current CLUSTERING index is not actually the best choice! Mostly, the data design was done decades ago and never changed. It can pay handsome dividends to check all the index definitions and see if it is possible to juggle the CLUSTERING index around. Failing that you can always play with the SEGSIZE and/or PCTFREE and PCTFREE FOR UPDATE values to alleviate pain in this area at the cost of requiring more disk space. There is always a price to pay, isn’t there?
No REORG Strategy
If you have a workload monitoring system, like our SQL WorkloadExpert, you can see before-and-after REORG snapshots showing you the performance improvement. If you are *not* getting a dramatic reduction in access times after a REORG then you should probably forget the REORG altogether!
REORG Index Strategy
If the indexes are getting too many splits it may well be time to change the index page size and possibly even go COMPRESS YES, however please remember COMPRESS YES indexes do *not* work at all with FlashCopy and SLB as they work at the disk sub-system level and do *not* access via the bufferpool – which is where Index compression and decompression is all done. To recap: COMPRESS YES for indexes is fine, but not if you are depending on FlashCopy / SLB to do the back-up!
Thresholds?
There are so many but a brief list of my faves:
- General: Any DBET requirements (ARBDP, AREO*, AREOR, RBDP, RBDP*, RBDPM, REORP and PSRBD) or Extents > 200.
- Tablespace/Tablepart: > 25% deleted rows, > 5% indirect refs, > 10% unclustered inserts or any MASS DELETES.
- Indexspace/Indexpart: > 30% deleted rows, > 20% appended rows, > 10% leaf far, >5% pseudo deleted , >5% empty leaf, any Level changes or any MASS DELETES.
- Lob: > 50% disorganized.
and Finally…
One last Utility that often gets overlooked is MODIFY. It is hugely important to clean up the tablespaces DSNDB01.SYSLGRNX and the DSNDB06.SYSTSCPY (formerly known as DSNDB06.SYSCOPY) as well as enabling you to delete old, unusable backups. The problem here (there is *always* a problem, isn’t there) is that the RTS has no counters for the DSNDB01 data and so you must “tandem” REORG these. If you ever generate a REORG for SYSTSCPY then always generate a REORG for SYSLGRNX as well and then you are good! Naturally, you can add them to your automation system as it is “just a normal REORG these days”, but remember: no INLINE STATISTICS for SYSLGRNX as it is in the Db2 Directory and you cannot run RUNSTATS on Db2 Directory objects!
Over to you!
Have you any recommendations, best practices or Rules of Thumb that I have missed, trampled upon or completely ignored? If so, then please drop me a line!
TTFN,
Roy Boxwell