Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Posts
    8

    Unanswered: Displaying Elapse Time

    Hello everybody..

    I have a problem when i do my final project from my campus.
    my Question is: How to display the elapse time when we execute some query(selection, updating, deleting, ect) in DB2??

    For example, when i execute a query for selection (Select....from...), i can not get the elapse time. So, how to display it?

    I am a beginner for DB2 and i need your help..

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That certainly depends on what tool you use to execute the query. You could try "time db2 select whatever" on Linux or Unix, or db2batch (on any platform).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2009
    Posts
    8
    How to get the "time db2 select whatever" on Linux or Unix, or db2batch (on any platform)" as you told just now?

    is there any menu to choose? or by execute query??

    I'm sorry because I am a beginner so it is difficult for me..

    best regarsd...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can get the current time as follows:

    db2 "select current_timestamp from sysibm.sysdummy1"

    However, there is overhead in running the above SQL that will affect your results if you compare the starting and ending times.

    You can get elapsed time of queries inside of DB2 (not counting network traffic back to client) by running a "snapshot for dynamic SQL" (see Command Reference Manual). You will need to turn on the statement monitor in the dbm cfg.
    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
    Jun 2009
    Posts
    8
    thank u for your advice..

    i mean, how to display the elapse time when i execute a query??
    not to display the current time..

    I tried to run a snapshot for dynamic SQL, but i can not see the elapse time of queries..

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    db2 udpate monitor switches using statement on
    db2 connect to <db-name>
    db2 flush package cache dynamic
    db2 reset montitor switches

    run your sql

    db2 get snapshot for dyanmic sql on <db-name> > snapshot.out
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2009
    Posts
    8
    Thank u Marcus..

    I've tried your solution to display the elapse time..
    I have executed this query:
    GET SNAPSHOT FOR DYNAMIC SQL ON TEST

    TEST is the database's name..

    when i executed that query, the result is "Not Collected"..
    i don't understand what is the meaning of that...

    could you like to help me anymore??
    how to display the elapse time by using that query??
    is there any mistakes so why the "not collected" command is displayed on the monitor??

    Thank you very much

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure what you are doing wrong, but you must leave the session open for the commands I gave you. Do not close the session before the snapshot is taken.

    You can also permanently turn on the statement monitor with this command, which might work for you.

    Do this once:
    db2 update dbm cfg uisng DFT_MON_STMT ON
    db2stop
    db2start

    For each snapshot:
    db2 connect to TEST
    db2 flush package cache dynamic
    db2 reset montitor switches

    run your sql

    db2 get snapshot for dyanmic sql on TEST > snapshot.out
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2009
    Posts
    8
    oh i'm sorry marcus..

    I've tried to do all your solution, but the result is always "not collected"

    i don't understand why this happen..

    This is the result when i execute : get snapshoot...
    Number of executions = 2
    Number of compilations = 1
    Worst preparation time (ms) = 3
    Best preparation time (ms) = 1
    Internal rows deleted = Not Collected
    Internal rows inserted = Not Collected
    Rows read = Not Collected
    Internal rows updated = Not Collected
    Rows written = Not Collected
    Statement sorts = Not Collected
    Statement sort overflows = Not Collected
    Total sort time = Not Collected
    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Buffer pool xda logical reads = Not Collected
    Buffer pool xda physical reads = Not Collected
    Buffer pool temporary xda logical reads = Not Collected
    Buffer pool temporary xda physical reads = Not Collected
    Total execution time (sec.microsec)= Not Collected
    Total user cpu time (sec.microsec) = Not Collected
    Total system cpu time (sec.microsec)= Not Collected
    Total statistic fabrication time (milliseconds) = Not Collected
    Total synchronous runstats time (milliseconds) = Not Collected
    Statement text = select * from t_Buku

    why the result is not collected too??

    i am very confuse...


  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Please run the following command and post the output here:

    db2 get dbm cfg

    To output the data to a file, run this:

    db2 get dbm cfg > dbm_cfg.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

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The output of "db2 get monitor switches" would also help.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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