Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Querry Execution time

    Hey experts

    how can display execution time for a query i.e.time lapse between start and end of executing a query
    am not using any DB2 tool for querry,,am direclty using command line,,so pelase help me to find any simple syntax
    to get execution time
    its very urgent ,am in the middle of a performance tuning,,please help me some DBa experts

    Thanks

    Nimisha

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use the "db2 get snapshot for dynamic SQL on db-name".

    You must first do the following:
    • db2 update monitor switches using statement on
    • db2 reset monitor all
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    Hello

    thank you so much ,really appreciate ur effort to reply my querry
    i tried :db2 get snapshot for dynamic SQL on (dbname)
    the problm it bring pages and pages of record,,,,so i cant fetch my latest run SQL, is there any option to get only latest run querry details get fetch or any option to write into a file

    am new to Db2,so please clear me the way to resolve this


    thanks


    Nimisha


    Quote Originally Posted by Marcus_A View Post
    You can use the "db2 get snapshot for dynamic SQL on db-name".

    You must first do the following:
    • db2 update monitor switches using statement on
    • db2 reset monitor all

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by nimisha123 View Post
    Hello

    thank you so much ,really appreciate ur effort to reply my querry
    i tried :db2 get snapshot for dynamic SQL on (dbname)
    the problm it bring pages and pages of record,,,,so i cant fetch my latest run SQL, is there any option to get only latest run querry details get fetch or any option to write into a file

    am new to Db2,so please clear me the way to resolve this


    thanks


    Nimisha
    I realize you will get a lot junk, but all the junk should have an execution time of zero (except the one SQL you run after you reset the montor).

    To make the file smaller (but not as small as you want it) you can try this:
    • db2 connect to db-name
    • db2 flush package cache dynamic
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Feb 2010
    Posts
    3
    is there any syntax to write the snapshot into a file in a speciafied path as text or some other format,,,

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Try using the time command:
    time db2 -tvf <query>

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by nimisha123 View Post
    is there any syntax to write the snapshot into a file in a specified path as text or some other format,,,
    You need to learn some basic operating system commands such as piping an output to a file (that have nothing to do with DB2):

    db2 get snapshot for dynamic SQL on db-name > snapshot.txt
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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