DSNZPARMs

   
    One DSNZPARM specifies the maximum number of locks a user may acquire while his
program or transaction is running. If a user tries to exceed that number, the lock request will receive a -904 SQLCODE (resource unavailable). The unavailable resource is
another lock.

    Another DSNZPARM limits the number of locks a user is allowed to acquire on any one
table space. If a user attempts to exceed that number, DB2 will try to allow the job to
continue (and attempt to avoid a long ROLLBACK) by taking over the next higher lock
level (TABLE or PARTITION, depending upon the type of table space). This process is
called "lock escalation." If the escalation succeeds, the lower-level page locks can be
released.

    Let’s say the user is doing UPDATEs to a table in a segmented table space. This user first acquired an IX lock on the table space and on the table before acquiring an X lock on each page on which an UPDATE has occurred. The X locks on the pages have
accumulated and reached the DSNZPARM maximum. Instead of rolling back the
statement, DB2 recognizes that all the X page locks can be released if this user can
acquire an X table lock to replace the existing IX table lock. In order for the request for
the X lock on the entire table to be successful, no other user can have any kind of lock on the table. If another user does, the attempt to escalate will fail and the greedy user will receive a -911 (TIMEOUT) SQLCODE. If the escalation is successful, the user will have an IX lock on the table space and an X lock on the table; all the other locks will have been released. Lock escalation rarely succeeds. When it does, it usually causes problems because the X lock on the table causes all other users to time out.

    When lock limits are being reached and users are receiving -911s and -904s caused by asking for too many locks or by a failed attempt to escalate, don’t increase the limits.

Doing so rewards "the bad guy" — and could eventually bring DB2 down. Instead,
examine the greedy jobs to see if appropriate, lock-releasing COMMIT logic is in use.

The system value for the maximum number of locks per table space may not be
appropriate for some table spaces. Therefore, you can override it in the table space DDL
with the LOCKSMAX parameter.

    A third DSNZPARM sets a limit on the amount of time users will wait for an unavailable lock. Suppose the parameter is set to 60 seconds (the default). When a user requests a lock on a resource and an incompatible lock exists (our user needs an X lock on a page and another user has an S lock on that page), the user will wait...but not forever. The user will wait under a clock. If the S lock is released, the user will immediately acquire the needed X lock. If the S-lock isn’t released, our user will time out in the following manner:

The clock has an alarm that goes off every 60 seconds (per the DSNZPARM). Each time
the alarm goes off, any user that was waiting the last time the alarm went off (in other
words, has waited at least 60 seconds) will time out. If the user has waited less than 60 seconds, he will continue to wait until the next time the alarm goes off. Our user times out the second time the alarm goes off and receives a -911 SQLCODE.