DB2 Performance Monitoring for Load Test

DB2 10.5 on Windows

I need to do some load testing for our system and am having issues setting up DB2 to record what is going on.  In MSSQL Server it is pretty simple using SQL Profiler but I can't seem to find a similar thing for DB2.  Here is what I would like to do.

1. Capture all SQL Statements ran during the test.
2. Capture all the response times associated with the SQL Statements from #1.
3. Be able to see the explain plans for any of the SQL Statements from #1.
4. Capture all the stats for DB2 during the test (memory usage, buffer hits, hard reads, etc).
5. Display this all in a nice format that can be used to explain what was happening to DB2 during the test (not a must as I can always whip that up myself).

Since this is on a windows platform, I would be using Perfmon in conjunction to watch the windows parameters like IO, CPU, etc.

So how do most people do this in DB2?  I am hoping there is a performance command that is built in that will give me what I need but so far I do not see one.

Thank you!!

Jim
Jim YoumansSr Database AdministratorAsked:
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:
Hi Jim,

As I recall, you're using TOAD for DB2.  Have you checked out the IBM Data Studio?  It's free from IBM, though I don't think it does everything you're looking for.

IBM's tried and true method for most analysis revolves around calls to GET_SNAPSHOT and offline tools to analyze the results.  It's a bit of a pain...
0
Jim YoumansSr Database AdministratorAuthor Commented:
How do you do it?

We are testing upgrades to an existing system so we have 3 levels we need to monitor.  

1. Service Fabric (Azure backend stuff).  Network team monitors it.
2. Web servers (network team).
3. Database (me).

In the past (at other jobs) the network team has always (almost always) blamed the database for any bottlenecks or slowness.  Only by having the queries and stats for each have I been able to defend myself.  Sometimes it has been the database fault, but normally I have found that a page loads in  22 seconds because it calls the same stored procedure over 1000 times per page load (true story).  So I really need that kind of detail for the load test

Thanks!

Jim
0
Kent OlsenDBACommented:
Hi Jim,

I think you can do most of what you want with DB2's Activity Event Monitors.  It's not as easy to set up as the SQL Server monitoring tools, but it's not too bad, either.

Here's an IBM DeveloperWorks page with a pretty good description.  (Much better than I can provide in a couple of paragraphs....)

  https://www.ibm.com/developerworks/data/library/techarticle/dm-1401sqltrace/index.html
0

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
Jim YoumansSr Database AdministratorAuthor Commented:
Thank you!!!!
0
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
Windows OS

From novice to tech pro — start learning today.