Did you ever encounter a SQLCODE -805 (SQLSTATE 51002) in a distributed dynamic SQL environment? A bit strange because the SQLCODE -805 says DBRM or package not found in plan, but we are dealing with dynamic SQL. Here is what happened…It all started with an email from one of our users:
Clearly, the symptom of the problem is:
Some packages are necessary in a DB2 database server in order to support CLI connections. This is applicable not only to DB2 for LUW but also for host (DB2 for z/OS) and iSeries versions.
Depending on the type of statement executed by the application, DB2 will use a particular package. When using dynamic placeholders DB2 will use a syslh2 package with the following naming convention:
- S represents a small package (65 sections). Alternative value is L indicating a large package (385 sections)
- H represents WITH HOLD, alternatively this position can contain a N indicating NOT WITH HOLD
- x indicates the isolation level:
- yy is the package iteration 00 through FF
In the example above the application is requesting the execution of the package SYSLH203: it is a large package, WITH HOLD using isolation level CS iteration 3.
Why the application cannot find it?
By default, DB2 creates 3 packages for each type of package and because the iteration starts with 00, the suffix 03 indicates that it is looking for the 4th in the series
So, a DB2 server will contain, by default, the following packages of this category:DB2D-R ————————————————— PACKAGE LIST ——– Cm Collection Id Name Ty Version Owner Bnd Date ValidOper —-v—-1—-v—-2—-v—-3—-v—-4—-v—-5—-v—-6—-v—-7—-v—-8– NULLID SYSLH100 DB2ADMN 2010-01-06 Y Y NULLID SYSLH101 DB2ADMN 2010-01-06 Y Y NULLID SYSLH102 DB2ADMN 2010-01-06 Y Y NULLID SYSLH200 DB2ADMN 2010-01-06 Y Y NULLID SYSLH201 DB2ADMN 2010-01-06 Y Y NULLID SYSLH202 DB2ADMN 2010-01-06 Y Y NULLID SYSLH300 DB2ADMN 2010-01-06 Y Y NULLID SYSLH301 DB2ADMN 2010-01-06 Y Y NULLID SYSLH302 DB2ADMN 2010-01-06 Y Y NULLID SYSLH400 DB2ADMN 2010-01-06 Y Y NULLID SYSLH401 DB2XADM 2010-01-06 Y Y NULLID SYSLH402 DB2XADM 2010-01-06 Y Y
How to solve this problem?
First look into the application: The point is that if you get a SQL0805N or -805 you are facing an application that is holding a large number of statements open. It is important to stress that an application review should be the first action in order to solve this issue: having the application behaving better will not only solve this problem but also help to improve the overall health of the system. However, this solution is not always possible or practical and you may create more packages on the server.
Create more packages in DB2: You can create more packages on the server by connecting to the database (LUW) or DB2 subsystem (z/OS) and issuing the following bind command:
db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5
The option CLIPKG 5 will create an iteration of 5 packages, so in our particular scenario the application will have available packages from SYSLH200 to SYSLH204. Valid values for this parameter range from 3 to 30.
Important: in order to enable full support of CLI programs and utilities for a host or iSeries server you need to use of the following list of bind files:
- ddcsmvs.lst –> for OS/390 or z/OS
- ddcsvse.lst –> for VSE
- ddcsvm.lst –> for VM
- ddcs400.lst –> for OS/400
For example: for z/OS you need to use the list ddcsmvs.lst. It contains the bind files included on db2cli.lst among several others, so you can consider db2cli.lst a subset of ddcsmvs.lst. For the purpose of the problem being described here, to use db2cli.lst against a DB2 for z/OS server will be enough.
Binding CLI packages from a Windows client
You may have installed more than one different version of DB2 clients in your workstation. In order to be sure that you bind the version you intend to, start by starting the appropriated CLP:
Inside CLP, enter quit:
Connect to the target database and enter the following command:
db2 bind ..email@example.com blocking all grant public sqlerror continue clipkg 5 owner DB2ADM
Review the output, warning messages can be tolerated.
Graphical method: using the Configuration Assistant
Start the DB2 Configuration Assistant from the appropriated DB2 Client version:
Select the BIND option for the target Database:
Select the <CLI/ODBC Support> and Add bind options as shown:
This figure shows how to select the CLIPKG option from the contextual panel:
Additionally use the same panel for further options, if required:
When done, enter security information and press on Bind:
This is an output example:
As an example, you should see now the extra packages:DB2D-R ————————————————— PACKAGE LIST —- Cm Collection Id Name Ty Version Owner Bnd Date ValidOper —-v—-1—-v—-2—-v—-3—-v—-4—-v—-5—-v—-6—-v—-7—-v— NULLID SYSLH100 DB2ADMN 2010-01-06 Y Y NULLID SYSLH101 DB2ADMN 2010-01-06 Y Y NULLID SYSLH102 DB2ADMN 2010-01-06 Y Y NULLID SYSLH103 DB2ADMN 2010-07-15 Y Y NULLID SYSLH104 DB2ADMN 2010-07-15 Y Y NULLID SYSLH200 DB2ADMN 2010-01-06 Y Y NULLID SYSLH201 DB2ADMN 2010-01-06 Y Y NULLID SYSLH202 DB2ADMN 2010-01-06 Y Y NULLID SYSLH203 DB2ADMN 2010-07-15 Y Y NULLID SYSLH204 DB2ADMN 2010-07-15 Y Y NULLID SYSLH300 DB2ADMN 2010-01-06 Y Y NULLID SYSLH301 DB2ADMN 2010-01-06 Y Y NULLID SYSLH302 DB2ADMN 2010-01-06 Y Y NULLID SYSLH303 DB2ADMN 2010-07-15 Y Y NULLID SYSLH304 DB2ADMN 2010-07-15 Y Y NULLID SYSLH400 DB2ADMN 2010-01-06 Y Y NULLID SYSLH401 DB2ADMN 2010-01-06 Y Y NULLID SYSLH402 DB2ADMN 2010-01-06 Y Y NULLID SYSLH403 DB2ADMN 2010-07-15 Y Y NULLID SYSLH404 DB2ADMN 2010-07-15 Y Y Note that this method will create Large packages (L in 4th position in package name) only.But again: look at the application first!