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.