Hi guys,

Can you please let me know your thoughts on the following:

We have on update query, which simply updates on record in a table with where condition as it primary key, but it take about 0.5 to 1 sec. This particular query is excecuted so many times because it is used in sequence number generation in our application.

Q. In SQL snapshots, does the excecution time shown includes the time it takes to execution+commit or just excecution?

In our implementation we have a table with one record for every parallel sequence that we need in the application, and we store the current sequence no.
We update this current sequence no = sequence no +1 on every transaction's commit. This particular update is taking a very long time and also generating locks on the table.

Q. What can be done to avoid this?

Thanks,
Diwakar

To know the execution time of a SQL Statement, event monitors should be used ... Event monitors give a clear split up of the time taken by various processes (like execution, open cursor, prepare, close cursor, commit/rollback) including sort time, lock wait time etc.

For Q2, why not use SEQUENCE Objects which are not locked ? BTW, what version of db2 are you on ?

Cheers
Sathyaram

Satya,

We are using DB2 UDB v7.2... Will the snapshot/event monitor will give the commit time too...

Thanks,
Diwakar

Yes ...

And 7.2 does have sequences, which you can make use of ..

Cheers
Sathyaram