Monday, April 04, 2016

The Most Misunderstood Features of DB2 – Part 7: It Depends!

"It depends" is probably the most famous phrase out there when it comes to DB2 performance. Some call it the cardinal rule. You can answer just about any question that anybody asks at any time with it. Try it out. It depends! Always works... that is, it always works if you are trying to avoid answering the question!

Most DBAs and SQL experts resist giving a straight or simple answer to a general question because there is no simple and standard implementation that exists. Every situation is different, and every organization is unique in some way. So answering "it depends" to most questions can make a lot of sense. But "it depends" should never be the end of the answser!

Don’t be discouraged when you ask the local expert which statement will perform better, and the answer is “It depends.” The expert is just doing his or her job. The secret to optimizing DB2 performance is being able to answer the follow-up question to “It depends”—and that is “What does it depend on?

The key to effective SQL performance tuning is to document each SQL change along with the reason for the change. Follow up by monitoring the effectiveness of every change to your SQL statements before moving them into a production environment. Over time, trends will emerge that will help to clarify which types of SQL formulations perform best.

So the misunderstanding in this case is thinking that "it depends" is a complete answer to any type of question. It isn't... it is just the beginning of most answers. But it takes time, experience, and study to be able to answer what it depends upon. And that is why you might not get that answer unless you press for it... and attempt to find the answer yourself without just leaning on others for the answer all the time.

There is a corollary to the “It depends” rule that also is important. Hard and fast rules that rigidly enforce or forbid usage are not a good idea. In other words, this corollary is simply stated: “Almost never say always or never.” Notice that even the wording of this corollary embraces flexibility. 

So be flexible, but embrace answers that help... because "it depends" helps nobody unless you tell them what it depends upon! 


Anonymous said...

Can you please explain the steps to rename a db2 database. We have already moved the data from source to target.

Craig S. Mullins said...

Well, it is not simple to rename a DB2 for z/OS DATABASE. You'd have to DROP it, which drops everything associated with the database (table spaces, tables, indexes, authorization, etc.). Then you'd have to recreate everything. If, as you say, the data is not important as it has already been moved, then you do not have to worry about unloading and reloading the data.

I have to ask, though: why do you want to do this? The database name is not really an important consideration other than internally and for naming data sets.