“Suck it and See” development – A DB2 Support Nightmare!

The set-up

This could be strongly related to the Support Nightmare #1, but not necessarily always. We have many clients who have rigorous change and version control procedures, where nothing goes into a downstream environment until it’s been tested and signed off and where database changes to give performance improvements are handled by the DBA staff who are carefully checking for adverse impact from any changes. And we have many clients who have none of the above.

It’s easy to be critical if you’re a database purist and I think everyone recognizes that there are situations where agile and immediate changes are imperative, but the danger is that the Prod environment (or even Pre-Prod or QA if these are crucial environments where the test results are taken as being a realistic representation of what will occur in Production) is treated as a sand-box.

• This query is running really slowly
• I wonder if a new index might help (creates new index and re-submits query)
• No, that made no difference. Maybe I’ll Google it
• OK, let’s try setting Database Manager Configuration parameter obscure_and_poorly_understood_parm to YES
• No, that hasn’t helped. I wonder if I create a small table with just the bits I need and query that (submits massive INSERT INTO TABLE statement using a SELECT on the base table with no usable index to service the WHERE clause)
• Etc., etc.

At the end of this exercise not only do you not have a solution to your performance problem but you have a number of changes that may have made things worse rather than better. And it’s very rare that anyone retraces their steps and removes all the changes they attempted in an effort to solve the problem.

The Moral

The first moral of the story is don’t leave this sort of task to someone who isn’t an experienced DBA. Performance tuning is a specialist skill, some might say a Black Art, and it’s unfair to offload this sort of work on someone without the necessary skillset and the time to do it properly. I’ve been in IT for 30 years but I don’t expect someone to hand me a screwdriver and a soldering iron and ask me to rewire their mainframe.

The second moral is don’t leave this task so late in the cycle that it is occurring in Prod. Performance tuning should be part of the development cycle; occurring in the early stages of the project and forming part of the release checklist.

Download our previous series ‘Top 10 DB2 Support Nightmares and How to Avoid Them‘ here.