locks and related

[Sumanth]

Hello,

My question is a follow up on a recent thread (db2_evaluncommitted)

If I have a table with say a million rows and a predicate of my update
clause matches say 8000 records,
does db2 only lock 8000 records or does it lock records that it evaluates
based on the query plan.
(db2_evaluncommitted=yes, isolation level --> cursor stability

I see locks in the order of 30,000 - 50,000 if there were no statistics
collected on the transaction table

once I executed the run stats, the locks were pretty close to around 8500.
(which is what I expect)

I was assuming only rows that match the predicate will be locked
irrespective of a runstats but i dont see it
that way based on my above observation? can anyone explain me how the
locking mechanism works?
Thanks,
Sumanth

[Phil Sherman]

Do an explain on the current query. Remove the statistics you added and
redo the explain. If you have different access plans, they you may be
able to account for the difference in locks.

Phil Sherman

Sumanth wrote:

Hello,

My question is a follow up on a recent thread (db2_evaluncommitted)

If I have a table with say a million rows and a predicate of my update
clause matches say 8000 records,
does db2 only lock 8000 records or does it lock records that it evaluates
based on the query plan.
(db2_evaluncommitted=yes, isolation level --> cursor stability

I see locks in the order of 30,000 - 50,000 if there were no statistics
collected on the transaction table

once I executed the run stats, the locks were pretty close to around 8500.
(which is what I expect)

I was assuming only rows that match the predicate will be locked
irrespective of a runstats but i dont see it
that way based on my above observation? can anyone explain me how the
locking mechanism works?
Thanks,
Sumanth