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..

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).

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...

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.

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..

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

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

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

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...

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

The output of "db2 get monitor switches" would also help.