07 March,2017 by Jack Vamvas
For ease of use and accessibility db2pd in DB2 LUW is my favourite for DB2 performance troubleshooting
Here are some approaches on gathering data from DB2 LUW to assist root cause analysis. This is the first post – I’ll keep adding more information over the next few days
If you want to read on a wider range of db2pd capabilities read db2pd troubleshooting guide (DBA DB2)
A typical situation is sustained high CPU – and query response times begin to degrade.
Check vmstat and get an overall picture of the CPU. Focus on the difference between sys and usr CPU. What exactly is a high CPU can be arbitrary and will depend on an understanding of the system resource usage patterns.
To keep it simple – let’s say sys is double the usr CPU.
For some vmstat details read Linux swap space and DB2
If CPU sys is high check out either IO stalls \ Network \ latches.
For latch investigation use the –latches switch . This example will run every 2 seconds 20 times
db2pd -latches -rep 2 20
Investigate the “Waiter” column.
If CPU usr is high – it means application code is the main contributor to high CPU. You’ll need to list of EDUs consuming the highest CPU
db2pd -db <dbname> -edus interval=5 top=5
Next piece of the puzzle is to identify which query is generating the bottleneck. Use the –apinfo switch to correlate
db2pd -db <dbname> -apinfo -rep 2 5