Results 1 to 5 of 5
  1. #1
    Join Date
    May 2010
    Posts
    88

    Unanswered: db2pd to find longest running query

    Hi,
    I am working on DB2 V9.1 on AIX 5.3
    Yesterday, the CPU utilization reached 100% and so db2pd was the only option I was left with to analyze the problem. I was asked to kill the longest running query. How do I find it considering the output of db2pd is huge and number of transactions running are 100+

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    Hi,

    what about
    db2pd -db <ABCDE> -activestatements
    ?

  3. #3
    Join Date
    May 2010
    Posts
    88
    But how I map it to the statement executing?

    Also, when i see the output of "db2pd -database <ABCD> -dynamic", I find more than 100+ statements executing under column text then why does it display only 6-7 statements while executing "db2pd -database <ABCD> -activestatements"

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I usually use snapshots to identify a query(ies) consuming more CPU / higher execution time than others.

    If db2pd is the only option, then I think you can do "db2pd -edu" to find a db2agent consuming lots of cpu and then using its EDU ID find the appl and the sql (db2pd -app -dyn)

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2cap View Post
    when i see the output of "db2pd -database <ABCD> -dynamic", I find more than 100+ statements executing
    The fact that a statement is in the package cache does not mean that it is being executed when you take the snapshot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •