On Feb 8, 12:56 pm, "Mark A" <nob...@nowhere.com> wrote:
> "Rahul Babbar" <rahul.babb...@gmail.com> wrote in message
>
> news:55027499-b467-4f66-a76a-11210b318ac8@s13g2000prd.googlegroups.com...
>
> > Hi,
>
> > I had the following doubts about the "For Read Only" clause.
>
> > 1. How does a "for Read only" clause improve the performance?
> > 2. How does a "for Read only" clause compare with "With UR" clause in
> > performance? Which is faster?
>
> > Can someone clarify on that?
>
> > Thanks a lot.
>
> > Rahul
>
> The "for read only" can minimize the degree of locking in certain cases to
> prevent anything higher than a share lock from being taken where DB2 thinks
> your cursor "intent" is ambiguous and it takes a lock stronger than share.
> It can also affect cursor blocking.
>

Thanks a lot.

How does the DB decide whether the cursor "intent" in ambigous?
Can you please explain that?

> WITH UR prevents even a share lock on a SELECT statement. This potentially
> might be help prevent lock contention in some cases, so long as you are
> willing to live with any issues caused by inconsistent data being read
> (where another transaction has not finished its unit of work and you are
> seeing only some of the updates in an uncommitted transaction). But keep in
> mind that multiple share locks on the same resource (row, index, table, etc)
> do not conflict (and do not cause lockwaits), so WITH UR will only help in
> "reading through" IX, U, X, etc locks that can cause lockwaits or deadlocks
> on the statement that has the WITH UR, or another transaction that is trying
> to update a row the WITH UR is selecting..

Also,

I read that "for read only" may improve the performance because the
DBM can retrieve blocks of data.
What exactly does that mean?
If it does increase the performance of the DB, will it be better "With
UR"? (i personally don't think so, i mean if the DBM can retrieve
blocks of data in a read only clause, why not it can do the same thing
in "with UR")

Thanks a Lot

Rahul