75 ways to demystify DB2: #9 : Tech Tip: DB2 CLI Packages demystified!!

Body

We often deal with PMRs on "SQL0805N Package not found" error and get questions about DB2 Packages from customers.

We do have lot of technotes and white papers on DB2 packages. I just wanted to consolidate all the information and give you a quick overview of DB2 CLI packages and help you to solve SQL0805N errors.

Packages: Packages in DB2 are control-structure database objects that contain executable forms of SQL statements or placement holders for executable forms or access plans.

Section: A dynamic section inside the package is the actual executable object that contains the logic needed to satisfy a dynamic SQL request.

DB2 CLI Packages:  The DB2 Call Level Interface (DB2 CLI) is a callable SQL interface to the DB2 family of database servers. A callable SQL interface is an application program interface (API) for database access, which uses function calls to invoke dynamic SQL statements. DB2® CLI packages are automatically bound to databases when the databases are created or migrated, or a fix pack is applied to either the client or the server. Each statement handle allocated in a CLI application will occupy one section within a CLI package.

By default:

i) DB2 packages for CLI are created in the NULLID collection (or library).
ii)  Three small and three large packages for each isolation levels( 4 isolation levels) & Cursor holdability (2) are created. (3*4*2 + 3*4*2= 48 packages in total).
iii) Each small package allows a maximum of 64 statement handles per connection, and each large package allows a maximum of 384 statements per connections, giving a total of 1,344 statement handles.
 

Naming Convention for CLI packages:
SYSSxyy and SYSLxyy

'S' represents a small package, and 'L' represents a large package
'H' represents WITH HOLD, and 'N' represents NOT WITH HOLD
'x' is the isolation level: 0=NC, 1=UR, 2=CS, 3=RS, 4=RR
'yy' is the package iteration 00 through FF
 

By default following DB2 CLI packages get created in your database.

db2 "select pkgname from syscat.packages where pkgSchema='NULLID' and pkgname like '%SYS%'"

Common cases of SQL0805N PACKAGE NOT FOUND errors:

Case: 1) If you receive SQL0805N for one of the above packages, then it shows that the package is missing in your database for some reason.

 To create those packages, bind db2cli.lst from <instance dir>/sqllib/bnd.

db2 "bind @db2cli.lst blocking all sqlerror continue grant public "

Case: 2) If you receive SQL0805N for a package with package number greater than the above, say, if an application is failing with SQl0805N package not found for NULLID.SYSLH203, it shows that the application has already used SYSSH200, SYSSH201, SYSSH202 (the 3 small packages) and SYSLH200, 201, 202 (3 large packages) and looking for the next one.

If that is the case, first review the application code to see whether it is closing the statement handles that are not needed.

If it is indeed cleaning up after itself, but it is a heavily used application in a production environment, then more packages are required for the application, and you can increase the number of packages by binding it with CLIPKG keyword.

For example: db2 "bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG X"

Where "X" is any number of large packages that can be created, can be from 3 to 30 depending upon your application's requirement.

Monitor number of statement handles used by application:

The package that was being used at that time by a particular application can be monitored from the application snapshots on the DB2 database .

For example,

• Section number = 35

  Application creator = NULLID
  Package name = SYSLH206

Approximate number of statement handles can be calculated from the above information.

If the snapshot shows NULLID.SYSLH206 being used and the section number for the particular statement was 35, SYSLH206 shows that the application has already used SYSSH200, SYSSH201, SYSSH202 (the 3 small packages) and SYSLH200, 201, 202, 203, 204, 205 (6 large packages) and is currently using SYSLH206.

The small packages have about 64 sections, the large ones about 384 sections.

So 3*64 (the three small packages) + 6*384 (the first 6 large packages) + 35 (the section within the 7th large package that is being used) would give you the number of statement handles the application is using at that specific time.

Thanks for reading!

-Subbulakshmi Prabhu

References:

DB2 Packages

Knowledge collection: SQl0805N