How would you as a DBA (DB2) answer this question?

Dear gurus,

A db2 user reports to the DBA team that there is a very noticeable degradation of a critical application - response time taking longer than usual, application not finishing on time.

Usually when I get this type of report from my user clients, I begin by asking the user exactly what the user is experiencing and take notes, check to see that my db2 is up and running by logging in to the instance and DB2 environment, check to see if I have utilities running that may have impact to the application(s), check the state of my tablespace/tables by doing some db2 displays, check the db2diag.log and the db2 notification log for warnings and errors such as contentions and locks, run some tools such as db2top, db2pd, if there is no real-time tools available and try to find what could be causing the slow-down the user is reporting.

My question to this esteemed gurus is this...how would you go about finding the cause and the resolution to such a question as reported by my user?

I would appreciate your comments and insights.

Thanks

Enyimba
EnyimbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Have the user provide the SQL that is being run.  Without it, you're guessing (though some pretty good guesses are available).

Run an explain plan on the SQL.  Note where the plan indicates the query to be the most resource intensive.

If the slow-down is in a semi-static table (slow or no growth) check that the appropriate indexes are available.  Updating the statistics on this table (and its indexes) may help.

If the slow-down is in a dynamic table (steady growth, or lots of INSERT, UPDATE or DELETE activity) start by updating the statistics.  It's likely that the normal table activity has resulted in rows being in non-optimal locations so a REORG may be the way to go.

That's where I'd start.


Good Luck,
Kent
waynezhuCommented:
What's your db2 version?
EnyimbaAuthor Commented:
waynezhu,

DB2 version is 10.5.3, getting ready to upgrade to 10.5.5
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

EnyimbaAuthor Commented:
Kdo,

Thanks for your response and as always I appreciate your logic. But let me ask you this...what if the slow down is perhaps not SQL related? What/Where would you tend to look for possible clues? What type of tools  would you want to use to triage the problem and if possible, why would you want to use that specific tools/utility (db2top,db2pd, SAR, NMON comes to mind). I am interested in your thought process about the issue...so help me and share the way you see it..

Enyimba
waynezhuCommented:
I'd like you to check out MONREPORT which is the next generation of DB2 monitoring and tuning tools.
MONREPORT share similar spirit as Oracle's AWR report.
Despite the fact that MONREPORT is way less powerful as AWR report,
MONREPORT still can provide lots useful information specific to database.

You can combine DB and OS monitoring, and pinpoint exactly what is happening within your system
for any given time interval.

For your reference, here are a few links:
http://www.idug.org/p/bl/et/blogaid=33
http://db2commerce.com/2014/12/02/db2-basics-monreport/
https://xcoolwinds.wordpress.com/2012/06/01/diagnosing-sql-performance-slowdowns-using-monreport-module-reports/
http://it.toolbox.com/blogs/db2-for-luw/db2-for-luw-continues-to-mature-monreport-reporting-module-53075

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kent OlsenDBACommented:
>> what if the slow down is perhaps not SQL related?

That suggests a network or hardware error somewhere between the database server and the user desktop.  That's pretty normal desktop troubleshooting and not really in scope with trying to determine why a query's performance has degraded.
waynezhuCommented:
Just noticed your question is in the Unix topic.
For OS monitoring, if sar data available, you will have pretty good picture of general health, any pattern change or abnormal.
If sar data not available, I would gather vmstat, iostat, etc on a regular basis for the same.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.