Specifying the maximum number of locks that a process can hold on a table space

You can specify the LOCKMAX clause of the CREATE and ALTER TABLESPACE statements for tables of user data and also for tables in the Db2 catalog, by using ALTER TABLESPACE.

About this task

Refer to the description of the LOCKMAX clause in CREATE TABLESPACE or the ALTER TABLESPACE for information about valid values.

FL 507 The initial default value of the LOCKMAX clause in an application process is determined by the setting of the NUMLKTS subsystem parameter. After the SYSIBMADM.MAX_LOCKS_PER_TALESPACE built-in global variable is set, subsequent changes to NUMLKTS have no effect on the application and global variable determines the maximum number of locks that an application can simultaneously hold in a table space. If a single application exceeds the maximum number of locks in a single table space, lock escalation occurs.

Catalog record: Column LOCKMAX of table SYSIBM.SYSTABLESPACE.

Use one of the following approaches if you do not use the default value:

• Base your choice upon the results of monitoring applications that use the table space.
• Aim to set the maximum number of locks high enough that, when lock escalation occurs, one application already holds so many locks that it significantly interferes with others.
  For example, if an application holds half a million locks on a table with a million rows, it probably already locks out most other applications. Yet lock escalation can prevent it from potentially acquiring another half a million locks.
• If you alter a table space from LOCKSIZE PAGE or LOCKSIZE ANY to LOCKSIZE ROW, consider increasing the value of LOCKMAX to allow for the increased number of locks that applications might require.