SQL805N on NULLID.SYS* package

All applications are not perfect. There might be instances where applications do not close their cursors after they are done with them. This causes DB2 dynamic packages to be held and eventually gets used up.  When all the default number of packages are used up, the application will start getting -805 on NULL.SYS* packages. 

Today at my work such an incident occurred, first I start terminating applications that had the earliest connect timestamp and was waiting on application. This freed up some of the packages, but the application quickly used up all the free ones.  We ended up terminating all the threads, deactivating the database and activating the database. This seems to have eliminated the  problem (for now).   If I am not able to identify the cursor(s) that is causing it, my next step is to increase the number of default client packages.   This can be done by  :

db2 “bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG 12?

This will increase the number of available packages from 3 to 12. By default when you bind the db2cli.lst against your database there are 3 small dynamic packages and 3 large dynamic packages bound into system catalog table syscat.packages.  Applications that doesn’t close the cursors properly will use up all the three copies.  The number of small packages is fixed at 3, but the number of large packages is variable in range from the default of 3 to 30, and is controlled using the bind option CLIPKG N. For the default of CLIPKG 3, there are 3 of each dynamic package bound to the database, for example: NULLID.SYSLN200, NULLID.SYSLN201 and NULLID.SYSLN202.  Run the following query on your database to see how many SYS packages are there :

db2 select pkgname from syscat.packages where pkgname  like  ‘SYS%?

For each small package,  there are 64 handles available, while each large package contains 364 handles. This means that by default each application will have (64 * 3) + (364 * 3), or 1284 handles available. Each prepare statement and each 
cursor that is created by the application utilizes one of these handles, and the handle will not be released until the prepared statement or cursor are closed. With JDBC applications, you can not guarantee when garbage collection will occur, so you should ensure that stmt.close and cursor.close are being issued when the associated statement or cursor has completed its operation, especially if the prepare statement and/or declare cursor calls are present inside a loop or a method that are 
executed multiple times during the session. Once your application has used all of the available handles, DB2 will then attempt to access the next package name in sequence in order to access more handles. When your application has allocated all the 1284 handles and needs a 1285th, DB2 will attempt to locate NULLID.SYSLN203 for access to an additional 364 handles. Unless the db2cli.lst has been bound with at least an option of CLIPKG 4, this package will not be found, and the SQL0805N error will result. The quick solution, in this case, is to rebind the db2cli.lst with a sufficient number of packages to be  able to process the number of handles required by your application. But before doing that you should first determine whether you can get your application to close the cursor properly.  If its actually the volume thats driving the error then you should increase the number of packages.

I found the following links to be useful in resolving the issue :

Link 1

Link 2

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , , , , . Bookmark the permalink.