Uncategorized

SQL0805N Package “” was not found.

Posted by

So my posts on speific error codes that are frequently seen in commerce databases, and what to do about them seem pretty popular. My #1 and #2 posts in # of views (after “Home Page” ) are ones that describe specific errors and what to do about them. So this was one of the areas I came up with when thinking about that type of post.

How this error looks

This error happens most frequently with BODL or with custom code that is meant to do something with a large number of rows, usually using cursors in a stored procedure or statement handles in a Java application. At least once every six months a developer comes to me with this error. It typically looks something like this:

2010-04-23 15:32:56,675 [Thread-263069] INFO - com.ibm.db2.jcc.b.SqlException: DB2 SQL Error: SQLCODE=-805, SQLSTATE=51002,
 SQLERRMC=NULLID.SYSLH21E 0X5359534C564C3031, DRIVER=3.50.152 2010-04-23 15:32:56,675 [Thread-263069] INFO - at
 com.ibm.db2.jcc.b.wc.a(wc.java(Compiled Code))

One of the important things to look for beyond the “SQLCODE=-805” is the package name – in this case “NULLID.SYSLH21E”. You may also be presented with the error in this format:

SQL0805N  Package NULLID.SYSLH21E was not found.

The package name will always start out with either NULLID.SYSLH or NULLID.SYSLN. If it is something completely different, then the error being encountered is NOT the one being discussed here(and you may just need to do a simple bind on db2ubind and/or db2cli). To get a bit over technically detailed, the H means the cursors are declared with hold and the N means they are declared without hold. There will then be three numbers. The first represents the isolation level, and is one of the following:

  • 0 – NC
  • 1 – UR
  • 2 – CS
  • 4 – RS
  • 8 – RR
The last two numbers are a sequential two-digit hexadecimal number with a maximum value of 1D (which is 29 in base 10).
So you didn’t really need all that detail, but I’m always fascinated by the details.
Anyway, what it all means is usually that an application is not properly closing statement handles. By default when you do the cli binds (at fixpak or in other situations), DB2 creates 3 packages. These three packages can handle 384 statement handles each, for a total of 1,344 open cursors at any one time.

The Band-Aid

So if you’re getting this error on SYS**03 or anything below 1D, then you can attempt to slap a band-aid on the problem by increasing the number of packages available. This is as simple as doing a cli bind:
cd ~/sqllib/bnd
db2 connect to dbname
db2 bind @db2cli.lst blocking all grant public clipkg 30
Obviously, there’s a limit here, and almost every time I’ve added the additional cli packages in a few weeks or months, they outgrow them, and there’s nothing more I can do at that point. You simply cannot go higher than 30. 30 cli packages represents 11,712 concurrently open statement handles – does the application seriously require that many?

The real fix

So the real fix is not at the DBA level. The real fix for this is in the code. In every situation, the developers have eventually discovered the heart of the issue – cursors or statement handles that were not being properly closed. The key is in communicating this properly with developers. I find listing the number of cursors/statements that they have open to receive the error useful. This is not a database engine error, but a code error.

Reference:

Ember Crooks
https://datageek.blog/
ember.crooks@gmail.com
Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

5 comments

  1. Pingback: DB2:Setting open Cursor - dBforums
  2. Pingback: Binding Packages
  3. Pingback: What Is dummy bind in mainframe? [duplicate] | XL-UAT

  4. Hello,

    “db2 bind @db2cli.lst blocking all grant public clipkg X” <== where can I find the current value of X before increasing it?

    Thank you.

    Reply

    1. There may be an easier way, but this will get it for you. In the example output below, I have the default of 3:
      db2 "select substr(PKGNAME,1,6) as pkgname, count(*) from syscat.packages
      where pkgschema='NULLID'
      and pkgname like 'SYSL%'
      group by substr(PKGNAME,1,6)"

      PKGNAME 2
------- -----------
SYSLH1            3
SYSLH2            3
SYSLH3            3
SYSLH4            3
SYSLN1            3
SYSLN2            3
SYSLN3            3
SYSLN4            3

  8 record(s) selected.
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.