We know that the DB2 SQL code -904 indicates “resource unavailable”. But why does this error occur in practice? There are several possible reasons, and here I’ll explain one of them.
What is the DSNZPARM parameter?
When a DB2 subsystem is installed, values are supplied through the installation ISPF panels. These values are stored in what is called DSNZPARM, which is essentially the subsystem parameter load module. It contains DB2 execution-time parameters that control the behavior of the subsystem.
Among these parameters is NUMLKUS (number of locks per user).
NUMLKUS – Locks per User
- Definition: NUMLKUS specifies the maximum number of page locks or row locks that can be held concurrently by a single DB2 application.
- How it works:
When an application runs a DB2 query, it attempts to acquire locks on resources (pages or rows). If acquiring a new lock would cause the application to exceed the NUMLKUS threshold, DB2 issues an SQLCODE -904. - Impact: In this situation, the program may need to issue a ROLLBACK.
NUMLKUS Value Range
- Allowed values: 0 to 100000.
- If NUMLKUS = 0, it means there is no limit per user. While this may seem flexible, it can lead to storage problems, because DB2 consumes approximately 250 bytes of storage per lock.
Leave a comment