Thursday, November 24, 2016

Happy Thanksgiving

Today, in the United States of America, we celebrate Thanksgiving by gathering together with our loved ones and giving thanks for all we have. This includes celebrations with food, traditionally a big turkey dinner with mashed potatoes and gravy, as we watch a parade and football games...



With this in mind, I give thanks for all of the reader's of my blog, wherever you may be, and wish you a very happy day!

Tuesday, November 15, 2016

The Tao of DB2 - Part 12: Dealing with Change

It is time, once again, to look in on our friends, the intern DBA and his mentor. After spending so much time on performance issues, the mentor decides to check on his apprentice and make sure that he is not overwhelmed.

“Well, now that you mention it, the users want to change the data type of several columns,” stated the intern.

“The only constant is change,” replied his mentor. “Remember the Tao; as Lao Tzu tells us through experience the sage becomes aware that all things change. We must be prepared to support many different types of change requests. Some will be simple, others will be more complex. Never the less, all changes require careful planning to implement.”

“I noticed that the ALTER statement can be used to change data types; that doesn’t seem to be all that hard to me?” queried the intern.

“Read that manual carefully,” countered his mentor. “Keep the words of Lao Tzu in mind; he tells us ‘The difficult and the easy complement each other.’”

“Hmmm… I see. Looks like I can switch the data type of a column as long as things stay within the same family of data types. Like character to character... or numeric to numeric. But if I have to change a numeric column to a character or something like that, it get complex very quickly.”

“Very astute,” replied the Taoist DBA.

“So I can make some of these changes relatively easily, but it looks like all I have to do for the others is to drop the table and re-create it specifying the new data type for the column they want to change from CHAR to DATE?” asked the intern.

“Yes, that is all,” replied the Taoist DBA, shaking his head sarcastically.

The lesson here is that dropping an object drops all related objects and authority. If you drop a table, all of its indexes are dropped. And all of the grants are removed. Not to mention the data itself... It is important to understand the connectedness of all DB2 objects and what it means when we change those objects. Without this understanding, catastrophes are inevitable.

An additional lesson to be learned is that changes may need to be undone; you must be ready. A DBA should be prepared to be able to undo any change that is implemented – and then redo it again. Seriously consider a change management tool that will keep track of changes for you and automate the process. Without such a tool you will need to manually track and store your DROP and CREATE statements somehow.

Realization dawning upon him, the intern mutters “Dealing with change seems like it will be a big part of my job.”

With a grin, the Taoist DBA replies, “You have no idea how true that is.”

Friday, November 04, 2016

The Tao of DB2 - Part 11: Calm Inaction: a Difficult Lesson to Learn

The young intern DBA and his mentor were developing training for the new version of DB2 when the intern became distracted by a hubbub just outside their cubicles.

"What is going on out there?" asked the intern, out loud, to nobody in particular.

"What?" replied his mentor. "I don't hear anything." Remember, he is a practitioner of the Tao and is adept at concentration and blocking out distraction.

The intern sticks his head outside of his cubicle and sees a lead programmer/analyst he has worked with in the past scurrying around from cubicle to cubicle. He also sees a VP and somebody he doesn't recognize talking heatedly in the corner.

"Something is going on" said the intern.

"Something is always going on" said his mentor. "Get back to work."

"But I overheard somebody saying that DB2 wasn't working. Maybe we should look into that?" asked the intern.

"Do you trust the alerts you set up to warn you when there are DB2 problems?" asked the mentor.

"Sure," he replied, "you helped me set them up and we modified them just last month. I'm sure they are working."

"When all around you is in flux, sip your coffee and contemplate the situation. Not every problem requires a solution. Recall the wise words of Lao Tzu..."



"But what if they come in here and demand a solution?" the intern asked.

"Then we'll give it to them," replied the Taoist DBA, somewhat merrily.

The intern peaked outside his cubicle once more and things seemed to be more calm. "I guess it wasn't a problem with DB2, after all," he shrugged.

"No... and that should not surprise you. Sometimes the proper course of action is to do nothing. Some 'problems' solve themselves... Once again, I offer you the wise words of Lao Tzu..."


Tuesday, November 01, 2016

The Tao of DB2 - Part 10: Dispelling a DB2 Performance Myth

Once again we join our intrepid duo – the seasoned DBA who is a practitioner of Tao and his young apprentice to whom he is trying to teach the way of DB2…

“I have to say,” uttered the young intern, “that there sure is a lot to learn about DB2. Especially when it comes to performance.”

“And perhaps much to unlearn, too,” murmured his mentor cryptically.

“What do you mean?”

“Well, I bet you’ve heard things like ‘DB2 is a system hog’ and ‘We only need such a big CPU to make sure that DB2 works’ haven’t you?” queried the DBA.



“Sure, but isn’t that true? “Doesn’t a DBMS like DB2 mean that we have to buy more hardware to support it?” questioned the intern.

“Only if you plan not to work,” replied his mentor curtly. “If you learn how it works and use DB2 the way it is designed to be used then things will be very efficient! A well-tuned DB2 environment with properly coding programs will not require the constant addition of CPU power or memory.”

“Hmmmm… we have some work to do to change people’s attitude around here about DB2, don’t we?” asked the intern.

“A very astute observation!” countered his mentor. “We are the custodian of the DBMS. As such, it is our duty to teach those who wish to use it how to do so effectively and efficiently.”

Things that should be part of this task include:
  • Setting up training for each new developer who has never coded SQL against a relational database. This must include guidance on SQL coding, the importance of indexes, and how DB2 access differs from a flat file system. This must include training on Stage 1 and Stage 2 predicates, indexable/nonindexable predicates and the importance of coding joins properly.
  • Furthermore, programmers need to be trained to code every predicate necessary into their WHERE clauses. Many performance problems are caused by reading every row into the program and checking the data like you would a master file.
  • And be sure to train the developers not to be shy about asking for an index to help out their queries.

“If we educate every DB2 user on how DB2 works and proper SQL coding techniques, then we can help to alleviate the misguided perception that DB2 is a hog!” said the DBA.

“That makes sense. As long as we use DB2 as it was meant to be used things should be efficient. I’m ready to dig in and help get out the good word…” said the intern.


“Perhaps there is hope for you yet…” whispered his mentor.

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 area a lot of different statistics and hte 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 stats 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 difference approaches we could take, but I think the best approach is 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! 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 RUNSTATS after or during 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 setup 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...