LOCKSIZE (ANY)? Or (PAGE)?  Or (ROW)?

How granular should locking be?

It depends...

If all of the programs that do inserts, updates and deletes run in less than 15 seconds (the default timeout limit) or do commits with flawless restart logic, congratulations. You have a well-written application. Your reward is that you can specify LOCKSIZE (PAGE) for your tables and prevent lock escalation from happening.

What is lock escalation and why should you want to prevent it?

Lock escalation upgrades all of your page (or row) locks to one tablespace lock. That means no one else can access the table while you are using it. DB2 does this when you have taken so many locks that you are causing the list of all locks for everyone to become too large. Exclusive use of a table usually causes issues...  Like no parallel processing...

What about LOCKSIZE (ROW)?

Don't go there. The overhead is considerable and in over 20 years of DB2 tuning, I've never seen the need for it.  Try LOCKSIZE (PAGE) before LOCKSIZE (ROW), but only if you know for certain you are not getting escalations.  If you are getting escalations, switching from ANY to either one could seriously increase the size of your locklist and affect all active DB2 processes.

What if all of the batch programs that do inserts, updates and deletes don't run in less than 15 seconds or do commits with flawless restart logic?

LOCKSIZE (ANY) is your only option. If lock escalations are happening, you cannot do parallel processing of the tables being escalated. This is (part of) the cost of not having a well-written application.

~~~~~