DB2 performance monitoring with db2pd

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

Read More

How to monitor table reorgs with db2pd -reorgs (DBA DB2)

DB2 Instance Memory and dbptnmem

Check DB2 instance status