Home / server / Questions / 131649
In Process
Ichorus
Asked: 2010-04-13 08:41:16 +0800 CST

DB2 LUW tools for diagnosing issues when the stuff hits the fan

  • 772

I am no DBA and very much a novice when it comes to DB2 so even 'obvious' answers are welcome to this question:

I love db2top but sometimes I cannot get it to run if the load average is high on a db2 LUW.

This morning I was looking at an issue where load average shot up suddenly, I could not get db2top to come up and I needed to find out what was happening.

What can I do to find out who is doing what in this situation? I suspected a horribly bad query was being run by someone...is there a good way to find information on poor performing SQL on the fly in that type of situation?

Are there any good ways to collect good, actionable stats who/where bad sql is coming from in the event that load average is so high? I know about db2pd but I am not sure how to use it effectively and slogging through tens of thousands of lines of raw data is probably not the most efficient way to get at the heart of a problem.

Any tips or resources?

db2

1 Answers

  1. I am not a DB2 guy either. But my goodle fu is strong. Searching for

    force db2 LUW long running query

    Finds TONS of great data, including one that looks like exactly what you want, a well written tutorial on how to learn:

    http://www.dbisoftware.com/blog/db2_performance.php?id=122

    They also sell some crazily overpriced software that does the same thing for you. Here are the juicy bits:

    db2 "list applications show detail"

    This will give you a hint as to which connections are currently executing and if there are a number of connections in a lock wait status.

    If you observe lock waits, you should next take a deeper look into the lock contention with the command:

    db2 "get snapshot for locks on DBNAME"

    This verbose report will show you which application connections are holding locks and which are waiting. Compounding matters, some of the connections that are waiting may have other connections waiting on them. A real snowball effect can occur if you have LOCKTIMEOUT set to -1 (infinity, never time out) or set higher than 30 seconds.

    In the case of lock contention, your remedy usually involves killing the elephant with the command:

    db2 "force application (application-handle) "
    • 1