Tuesday, October 04, 2016

The Tao of DB2 - Part 8: REORG and Statistics

When last we looked in on our Taoist DBA and his young mentor they were dealing with performance issues. They were dealing with performance issues as they arose... reactively. The last question the intern had asked his mentor was a good one: "So is there more that I need to know about DB2 performance management?" 

Well, today the mentor is instructing his intern on how to be more proactive, instead of reactive.

"Today, let's talk about keeping our DB2 objects well ordered," said the mentor. "What if you were doing a home repair project and needed a specific size bolt? Would you rather look for it in the pile on the left or the tray on the right?"

"Obviously, the tray," said the intern.

"Then we must work on making our DB2 table spaces and indexes look more like the tray than the pile!" nodded the mentor.

"Oh, yes, I have read all about the REORG utility and I was thinking about creating a REORG job for every table space and index and just scheduling them to run every month," said the intern.

Shaking his head solemnly, his mentor said "Well, yes, that may be better than doing nothing. But again, maybe not. Do you really know that every object needs to be reorganized once a month? What if some get disorganized very quickly, like daily or weekly? Wouldn't it benefit your applications if those objects could be reorganized more frequently?"

"I suppose so," blinked the intern.

"And what about those that are almost never modified? Why do you want to reorganize them so frequently? Then there are those that change slowly, maybe you only need to reorganize them once or twice a year. If you just blindly reorganize everything at the same time you are wasting valuable mainframe CPU cycles... and that can be costly!" countered his mentor.

"Hmmm... So what should I do?" asked the intern.

"As with most complicated things, start by researching and making a plan. Understand each DB2 object and how it is accessed and modified. Is the amount of data growing? How fast? Basically, get an idea of the activity that is being performed on each object."

Things to consider include:

  • How frequently does the data change in the object? 
    • Changes? Rare; Variable? No = infrequent REORG
    • Variable? Yes = more frequent REORG (compression?)
  • How radical is the change?
    • Is the data simply updated in place?
    • Does it move?
    • Are there gaps in the data?
  • Is downtime possible? 
    • Online vs. offline REORG
    • How busy is the object in terms of access and modification during the time you want to reorganize it?

"Fortunately, DB2 gives us some help here!" replied his teacher.

"Oh, yes, I saw some of these things out in the DB2 Catalog... you know, like cluster ratio and near and far off distribution statistics. Right?" asked the intern.

"Ten years ago, you would have been right. And I give you credit for at least reading and learning about the statistics in the DB2 Catalog. But these days we should be using Real Time Statistics, or RTS. The DB2 Catalog stats are only modified when you run the RUNSTATS utility. The RTS are modified as their name implies, regularly in real-time as changes are made to the data," said the mentor. "Here is some documentation on RTS, take some time to understand the statistics and how you might use them to decide when you should reorganize stuff!"

"OK, thanks. I'll learn this stuff and be back to talk about what I should do after than," said the intern.

"Good plan!" said his mentor.

No comments: