Specifying the maximum number of locks that a single process can hold

Start of change The LOCKS PER USER field of installation panel DSNTIPJ specifies the maximum number of page, row, LOB, or XML locks that can be held by a single process at any one time, but after the SYSIBMADM.MAX_LOCKS_PER_USER global variable is set, subsequent changes to LOCKS PER USER have no effect on the application.This field includes locks for both the Db2 catalog and directory and for user data. However, it does not include catalog locks for bind operations.End of change

About this task

FL 507

Begin program-specific programming interface information. When a request for a page, row, LOB, or XML lock exceeds the specified limit, it receives SQLCODE -904: resource unavailable (SQLSTATE '57011'). The requested lock cannot be acquired until some of the existing locks are released.

The default value is 10000.

The default should be adequate for 90 percent of the workload when using page locks. If you use row locks on very large tables, you might want a higher value. If you use LOBs or XML data, you might need a higher value.

Procedure

To determine the maximum number of locks that a process requires:

  • Review application processes that require higher values to see if they can use table space locks rather than page, row, LOB, or XML locks.
    The accounting trace shows the maximum number of page, row, LOB, or XML locks a process held while an application runs.
  • Remember that the value specified is for a single application.
    Each concurrent application can potentially hold up to the maximum number of locks specified. Do not specify zero or a very large number unless that number is required to run your applications.End program-specific programming interface information.