Oppolzer - Informatik / Blog

Look at the CURRENTDATA bind option, if it is at the default (YES), there may be
locks with SELECT processing, if the cursors are not read only due to ORDER BY,
JOIN or FOR FETCH ONLY clauses (this may not be the case with WITH UR). I always
recommend to change the default to CURRENTDATA NO.

And: I don't think that buffer pool pages get externalized at commit. They stay
in memory. The externalization of buffer pool pages is independent of logical
transactions and depends of log activity etc. (number of log buffers written and
maybe time since last externalization). It is possible that data gets
externalized that has not yet been committed, and vice versa. The commit
activity is recorded on the log, and in the case of a DB failure, the consistent
state of the DB is reconstructed during DB recovery (undo and redo processing).

I hope my explanations are correct; maybe others could comment on this.

Kind regards

Bernd



Am 05.04.2012 21:05, schrieb R.C.:
>
> Hello Team,
>
> We have a bunch of application programs running in parallel and doing
> huge amount of processing.
>
> They are using onlu SELECTS that too with UR clause.
>
> I know a commit can release locks and claims.And also any page in
> buffer pool after a commit, gets externalized, there by making room
> for a new page..reducing chances of page thrashing.
>
> Usually people use commits in inserts and updates, but i've a feeling
> that it will help even in simple SELECT query also.
>
> Please share your thougths and experiences.
>
> Thanks!!!
>
>
> -----End Original Message-----