Wednesday, October 12, 2016

The Tao of DB2 - Part 9: The Five R's

The last time we checked in on our experienced DBA and his young intern the lesson was focused on when to REORG. The intern was sent away to learn all about Real Time Stats and RUNSTATS. Let's see how he is progressing...

"So, the Real Time Stats can be used to help me figure out when it is time to run a utility, like REORG, on my DB2 objects. There are a lot of different statistics and the manuals give some good guidance on which ones to look at and when they should trigger reorganization," offered the intern.

"Well done!" exclaimed the Taoist DBA. "You have grasped this concept quickly. But now, tell me, can you think about how this might tie in with anything else you know?"

"Hmmm..." thought the intern. "If a REORG changes the composition of the table space, then a RUNSTATS should be done to get the statistics that the optimizer uses to be accurate. And maybe it could make sense to REBIND any programs that access tables in that table space."

"Yes!" said the surprised DBA. "You are getting it. It is not enough to just learn each new thing that you happen upon, but to think about how it impacts everything else you already know. You are coming along nicely! So tell me about how these things should work together."

"Well, I guess there are a couple of different approaches we could take, but I think the best approach would be to REBIND our applications over time as the data changes. We'd monitor the RTS tables using the thresholds recommended in the manual..."

The expert interrupted his apprentice "...and you can tweak them over time as we learn more about how they change.

"Yes, that makes sense. And when we REORG we can follow that with a RUNSTATS..." said the apprentice.

"Or perhaps use in-lie stats to reduce the number of runs through the data. That can reduce CPU time," chimed in his teacher.

"So we are using RTS to keep track of changes to data volume and patterns. And then we'd follow that up with a REBIND process?" questioned the intern.

"Sure, but we don't want to REBIND everything, do we?" he queried.

"No, probably not. I can use the dependency information in the DB2 Catalog to find out which packages access or change data in the objects that were reorganized. Then we can just REBIND those" said the intern.

"Good thinking, but keep in mind that the DB2 Optimizer is not perfect, so we have to be ready in case we get degraded performance after rebinding. What do you think we can do there?" asked the mentor.

"Well, if we get any problems I can always try to code up a hint to get back to the way things used to work," said the intern. 

"Sounds like you're reading old documentation to me! Sure, sometimes a hint may be needed, but what about access path stability and using the PLANMGMT options of the BIND command? That way we can have backup versions of the access paths to go back to if performance suffers." 

"I vaguely recall hearing about that. I better read up on that," said the intern.

"Yes, you should," said the mentor, "And while you're at it, noodle on this. I call what we have been discussing here The Five R's of DB2 application performance. Here is a note card that steps through The Five R's:"

"OK, I see. We start with RTS, use them to REORG, make sure we run RUNSTATS after (or during) the REORG, and then REBIND any impacted packages. But what is Recheck? Is that a DB2 utility I don't know about?" asked the intern.

"No, no, no," cautioned his mentor. "That is referring to the manual process of checking the access paths to see what changed to make sure we don't get any surprises after rebinding. And then using PLANMGMT to control things while we investigate, tweak and try again."

"Well I have a lot of work ahead of me getting this all set up," said the intern.

"Yes, you'll have some work because we've been short-staffed. But I think you'll find a lot of this already set up and running. Please look into it all and tell me how things are going," replied the mentor, confident that his young apprentice was learning the way...

No comments: