Saturday, April 19, 2014

why do we get DB2 sql code -904 ?

We all know, DB2 SQL of -904 stands for "resource unavailable", but here i will try to explain why we get that error in our work. Before coming to that point, let us see
What is DSNZPARM parameter?
While installing DB2 subsystem,we supply some values via the install ISPF panels, and these parameters are know as DSNZPARM. We may look upon it  as  a 'subsystem parameter load module', which contains DB2 execution-time parameters. There are many parameters which needs to be fed into the system as part of this DSNZPARM.
One of such parameter is   NUMLKUS which stands for "Locks per user". This is the maximum number of page locks  or row locks that can be concurrently held by single DB2 application. When our application runs a db2 query,it attempts to take a lock on a db2 resource (page or row lock).
If this attempt to take a new lock causes the application to cross the NUMLKUS threshold, we get SQL code of  -904. In this case the program can issue a Rollback.
The value for this NUMLKUS can vary from 0 to 100000. What if we specify a value of '0' for this parameter?. Well, that means, no limit per user; and you can run into storage problem.(DB2 uses 250 bytes for each lock)