Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    25

    Unanswered: Measure Execution time of executed query

    Hi!
    First of all: i'm a real newbie in DB2!

    I have a question on measuring the exectuion time of a "select" query. Is there any command to measure it, and, furthermore ,doesn't print out the result?
    I'm using DBVisualizer (free edition).

    Hope someone knows some help

    Martin

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    go to db2 command window
    update monitor switches statement and timestamp
    get snapshot for dynamic sql (see infocenter for detail)
    will list all executed sql with detailed info
    explain will only indicate the cost and not execution time...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    if using Windows as DB2 client you can use free program DB2 Monitor instead of "get snapshot" command. Download: http://members.tripod.com/chuzhoi_files/index.html
    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Feb 2008
    Posts
    25
    Hi Groafty,
    thanks for the advice.
    Starting the programm I just get the error message, that that DB2APP.dll istn't found (the DB2 distirubtion istn't installed on this computer).

    Is there no other easy way to get the execution time of a query?
    Sorry, but I don't get this with "get snapshot"...

    Regards,
    Martin

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    db2batch is a tool that comes with DB2 to do permformance analysis.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2008
    Posts
    25
    When using db2batch, i have the parameters "Elapsed time" and "CPU Agent time".
    Is "CPU Agent Time" the time, which is needed to execute the query without the printout of the resulting tuples?

    Regards,
    Martin

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would turn off the outputting of rows using the following in the SQL script:
    Code:
    --#SET ROW_OUT 0
    You can also turn off fetch itself with this:
    Code:
    --#SET ROW_FETCH 0
    But note that DB2 may actually have to do some work for the fetch if it didn't compute the result set up front. (Computing things up front is not always the best choice, especially if not all rows are retrieved.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Posts
    25
    Thanks for the advice!

    But which of the two times is then the correct one for me?

    Or does there exist still another way to get the pure execution time of a query?

    Regards,
    Martin

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    That depends on what you want to know. The "CPU Agent time" may not be correct if your query waits for synchronous I/O, for example.

    I would use the "elapsed time" and do away with ROW_OUT to not disturb measurements with writing results to standard output.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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