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

Begin program-specific programming interface information.
The values of the LOCKMAX clause have the following meanings:
LOCKMAX n
Start of changeSpecifies the maximum number of page or row locks that a single application process can hold on the table space before those locks are escalated. For LOB table spaces, this value specifies the number of LOB locks that the application process can hold before escalating. For XML table spaces, this value specifies the number of XML locks that the application process can hold before escalating. For an application that uses Sysplex query parallelism, a lock count is maintained on each member.End of change
LOCKMAX SYSTEM
Specifies that n is effectively equal to the system default set by the field LOCKS PER TABLE(SPACE) of installation panel DSNTIPJ.
LOCKMAX 0
Disables lock escalation entirely.

The default value depends on the value of LOCKSIZE, as shown in the following table.

Table 1. How the default for LOCKMAX is determined
LOCKSIZE Default for LOCKMAX
ANY NUMLKTS subsystem parameter value.
other 0
Note: For XML table spaces, the default value of LOCKMAX is inherited from the base table space.

Catalog record: Column LOCKMAX of table SYSIBM.SYSTABLESPACE.

Procedure

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 value of LOCKMAX 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 million locks.
  • If you alter a table space from LOCKSIZE PAGE or LOCKSIZE ANY to LOCKSIZE ROW, consider increasing LOCKMAX to allow for the increased number of locks that applications might require.
    End program-specific programming interface information.