LOCKS PER USER field (NUMLKUS subsystem parameter)
The NUMLKUS subsystem parameter specifies the maximum number of page, row, or LOB locks that a single application can hold concurrently for all table spaces. 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.
| Acceptable values: | 0–104857600 |
|---|---|
| Default: | 10000 |
| Update: | option 33 on panel DSNTIPB |
| DSNZPxxx: | DSN6SPRM NUMLKUS |
FL 507 The NUMLKUS value is the initial default at the beginning of an application process, but after the SYSIBMADM.MAX_LOCKS_PER_USER global variable is set, subsequent changes to NUMLKUS have no effect on the application.
- 0–104857600
- The maximum number includes locks on data pages, LOBs, XMLs, and rows that the program acquires when it accesses table spaces. The limit applies to all table spaces that are defined with the LOCKSIZE PAGE, LOCKSIZE ROW, or LOCKSIZE ANY options. A value of 0 indicates that there is no limit to the number of data and row locks that a program can acquire.
You can enter the number of locks as an integer or you can enter a value with a suffix of K or M. If you enter a value with a suffix of K, the number of locks is that value multiplied by 1024. If you enter a value with a suffix of M, the number of locks is that value multiplied by 1,048,576.
Db2 assumes that each lock requires 540 bytes of storage. If you define referential constraints between values, you might want to select a higher value for this field.
To avoid exhausting the IRLM's storage for locks, follow these guidelines:
- Do not specify 0 or a very large value unless it is specifically required to run an application.
- Consider the design of your applications. Long-running applications, particularly those that perform row-level locking, have few or infrequent commit points, or use repeatable-read isolation may use substantial amounts of lock storage. You should perform frequent commits to release locks.
Check panel DSNTIPC to ensure that the required storage for the IRLM does not exceed the available region size for the IRLM.