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.
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 ?
Visit the new-look IDUG Website , register to gain access to the excellent content.