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?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Jim YoumansSr Database AdministratorAuthor Commented:
Thank you!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.