This is a little quote from Terry Purcell which I think should be understood much better than it possibly is today!
The quote is based on the idea that :
One single SQL statement can bring your entire system to its knees.
Really? Just one?
Many people think this is a huge exaggeration – However it is not!
It happened to me
This is not a friend of a friend story, but a story that really happened. Names have, of course, been changed but the story itself is 100% accurate.
The story
It was a normal Db2 PTF Maintenance night…
At this shop they always put maintenance in at midnight on Saturdays. All done automatically, and then roll the update around the data-sharing group so that, relatively quickly, all sub-systems have the same PTF level.
So far so normal
Sunday is not that busy at the site and no-one noticed any change.
Monday was different
Monday morning the machine started acting up. Customers could not login and the help desk was 100% busy. The lead DBA, George, had by around 08:00 a.m. received the first escalation to management and so the number of calls went up.
The bad guy
Using our WorkloadExpert (WLX) software together with our Bind ImpactExpert (BIX) the other DBAs, Fred and Ringo, quickly identified one single SQL statement that was taking 30,000 times more resources than in the prior week!
This was an SQL with table functions and LEFT, INNER JOINs etc.
What was it?
The comparison in BIX showed that all that had changed was one index access was now matchcols one instead of matchcols two! That was it! This one tiny change on one little SQL killed this machine…
The fix?
They decided to roll back the PTFs and quickly did this and everything returned to normal…
The lesson?
Always test any PTF before you go live – as just one SQL can kill ya!
Terry had indeed warned us all!
How can we help?
Well, thank you for asking: Our product CDDC contains an SQL replay and compare function that would have spotted this SQL in two different complementary ways. Firstly the BIX part of CDDC would have spotted it straightaway and secondly the replay itself would have thrown this out as a major outlier and bad guy candidate!
Different ways to Rome
They could possibly even have fixed this without backing out the PTF apply. They could have tried using our RUNSTATS Rescue to attempt to use older statistics and see if one of them would have given them a matchcols two access path. This time, however, with all the managers breathing down their necks it was decided – Undo all the changes!
In your shop
How would you have handled this situation in your shop?
Would/Could you have seen this before it happened?
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Architect