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 then use DB2 the way it is designed to be used then things will be very efficient! A well-tuned DB2 environment with properly coded 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. IT also should include training on Stage 1 and Stage 2 predicates, indexable and 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 (or more rows than are required) 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.

No comments: