db2 package handle (sql0805n)

we were getting below error
DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002, SQLERRMC: NULLID.SYSLN20C 0X5359534C564C3031
in this case application was trying to access the package SYSLN20C which was not created in the database
to solve this error we need rebind the db2cli.lst with CLIPKG 13

db2 “bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG 13″

after this we can find the package in the database

select pkgname from syscat.packages where pkgname = ‘SYSLN20C’
PKGNAME
———-
SYSLN20C

Explanation
Certain packages like SYSLN require multiple copies due to the high
usage of them. By default there are three copies made for these. What
happens is an application is using a lot of cursors, sometimes because
of application programming where cursors aren’t being closed.

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. 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. Try this query: db2 select pkgname from syscat.packages
where pkgname = ‘SYSLN200′

The effect of this CLIPKG option is described in more detail in the Call
Level Interface Guide and Reference manual. 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 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 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. You
may wish to start by doubling the default and use CLIPKG 6 as follows,
which should provide you with a total of 2376 handles for use:

db2 “bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG
6″

This will then add SYSLN203, SYSLN204 and SYSLN205 to your database. If
you in turn use all 2376 handles, you will then find that you may obtain
a SQL0805 error on SYSLN206 in turn. At that time, you may either wish
to go back to the application code to ensure that all handles are being
released when they are no longer required, or you may wish to rebind
with yet more packages listed with the CLIPKG option. You may bind up to
a maximum of 30 dynamic packages (CLIPKG 30).

After running the bind command, disconnect the application from the
database, and if possible stop and start the instance.

2 thoughts on “db2 package handle (sql0805n)

  1. What do you mean by 2376 handle ? Is it the number of connections calculated using db2 list applications | wc -l ?
    Pls advice

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s