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
The values of the LOCKMAX clause
have the following meanings:
- LOCKMAX n
- Specifies 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.
- 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.
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.