Hi All !

In db2 for z/os, db2 has the ability to lock row, page, table, and tablespace.
I believe in db2 for aix (or other non-mainframe platforrms), there are just two types of lock sizes.
default is row locking, which can be escalated to table locking.
I would like to know/read more on the differences in locking strategy implemented in db2 for z/os versus db2 for aix (or other non-mainframe platforrms). just the differences.
or maybe somebody could briefly tell me what the differences are.

thanks
Anil

DB2 fro z/OS orginally only had page and tablespace locking (except for the LOCK TABLE SQL statement). That is because DB2 originally used the IRLM (IMS Resource Lock Manager) before they rewrote a lock manager specifically for DB2. In IMS, it is easier to get away with page locking because typcially one might store a single order (for a specific customer) and all its line items in one physical page, something which is not done in a relational database.

Gerenerally you should use the highest level of locking possible that will not cause concurrency problems (no deadlocks or ecessive lock waits), because it takes more time to lock and unlock each row as an SQL statement executes. For Data Warehouse type databases or tables, there is no need for row locking since multiple read locks can coexist without any problem.

In DB2 for z/OS you can allow for lock escalation with the LOCKSIZE ANY paramter which starts out at the page and can get escalated to tablespace. If you specify PAGE, escalation will not occur.

The tendancy to escalate to tablespace locks is one reason why in DB2 for z/OS, most people use one table per tablespace (there are other reasons also).

That's how locking works for DB2 on z/OS. But now my next question is,
is it the same in DB2 for AIX ? or does db2 for aix allow only row and table locks ? if yes, does it imply that the page lock feature is 'nice to have' feature but not supported by db2 for aix.

thanks
Anil

DB2 for Linux, UNIX, and Windows does not have page locks (only row and table locks), primarily because it had row locks in the very first release (unlike DB2 for z/OS).

If you allocate enough memory in the LOCKLIST parm (db2 get db cfg for db-name), row locking is usually not a problem. I would recommend 4096 pages for most databases that do a lot updates. This will help minimize deadlocks and excessive lock wait time.

However, if the database is a data warehouse and all updates/loads are done off hours, then table locking might be preferable, or keep the LOCKLIST at the default (100) and let DB2 escalate to table locks on its own (when it runs our of memory in the LOCKLIST). The reason is that there is no lock contention if all applications are only doing selects, so the extra overhead of locking and releasing locks on each row is not necessary.