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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!!!!
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...