Personal tools
You are here: Home DB2 Problem Resolution General errors SQL0805N Package NULLID.SYS* was not found.
Log in

Forgot your password?
Document Actions

SQL0805N Package NULLID.SYS* was not found.

There might be instances where applications do not close their cursors after they are done with them.

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.



The error message -805 was received from an application stating that the package SYSLH21E was not found.


The following are examples of the symptom that could be observed.

SQL0805N Package "SYSLH21E" not found. DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: DB2PROD.NULLID.SYSLH21E.5359534C564C3031;DISTSERV;04




The SQL0805 NULLID.SYSL[HN]xyy error normally shows up when an
application runs out of statement handles. The number of statement
handles available to a CLI application depends on the number of large
packages the application has defined and is limited by overall system
resources. By default, 3 small and 3 large packages are created. Each
small package allows a maximum of 64 statement handles per connection,
and each large package allows a maximum of 384 statements per
connection, giving a total of 1,344 statement handles.

If your application requires more than 1,344 handles, you will need to
increase the number of large packages by setting the CLIPKG keyword
up to a value up to 30, which you have already done.

Some definitions for the package name SYSL[HN]xyy:
[HN] - One or the other. H means with hold, N means without hold.
x - Isolation level: 0=NC, 1=UR, 2=CS, 4=RS, 8=RR
yy - Package iteration 00 through FF.

For yy, the max is 1D, which is hex for 29. This means that you can have
a maximum of 30 (packages are numbered 0-29) large packages, for a grand
total of (64*3)+(384*30)=11,712 statement handles.
The message received is error for package SYSLH21E.

Thus the cause of the SQL0805N error is when the application is trying to use a CLIPKG value greater than 30 which is 1E in hex.

Resolving the problem

The preferred way to address this issue is to modify the application code so that it properly closes statement objects when finished using them.
The application may be failing to close statements when they are no longer needed. Modify the application to specifically call the Statement.close() method on Statements, PreparedStatements, to avoid running out of resources. The application should not rely on the garbage collector to close the resources.

It is good programming practice for the application to specifically close its resources when no longer needed. Currently the driver must maintain a list of references to open statements and therefore statements are not automatically garbage collected when application references go out of scope.

The JCC Type 4 driver functions differently than the JDBC Type 2 legacy driver, in that statement handles were being reused with the Type 2 legacy driver, but not reused with type Type 4 JCC driver.
Adding additional Statement.close() calls to the application code will resolve this issue.



Security Awareness
Would you like your company to implement gamification into your security awareness program?