DB2 SQL Error SQLCODE=-805 | Fixes You Need to Know

If you are getting the SQLCODE -805 in DB2, chances are the query you are trying to run is referencing a missing package in the DB. The creation of such packages consists of binding a set of SQL statements with the SQL database. Generally, such binding offers a more optimized solution than the general statement approach. It even lets you use an application program to run the packages stored locally on the DB.

What Is DB2 SQLCODE 805 SQLSTATE 51002?

As mentioned earlier, the DB2 SQLCODE -805 means that you used a specific package name in your query that does not comply with any other package name in your database. However, this error is pretty easy to avoid once you know the naming convention of packages and the correct way of referencing.

Typically, there will be two portions in the name of a package, one for the collection and one for the package itself. While the former represents a batch of packages affiliated with a particular plan, the latter helps identify a single package in a collection.

Package names will usually follow the <collection_name>.<package_name> format. If you want to refer to a package, the correct way is to include the collection name followed by the package name and separate them by a dot (i.e. PLAN.PACKAGE).

How to Resolve SQLCODE 805?

Here’s what the complete error message may look like:

-805 DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code

If we break this down, the “location-name.collection-id. dorm-name. consistency-token” portion refers to the package that was not found. And the “plan name” identifies the name of the plan to which that package belongs to. Lastly, the “reason code” is either blank or a number in the range of [01-04]. 

A blank reason code means the lengths of the location name, the collection-id, and the dorm-name are unequal. For example, the location-name might have a length of 16 while that of the dorm-name is 8. Usually, the SQLERRMT length is the cause for this mismatch.

Each of the numeric values indicates a distinct reason for the error.

Reason-Code 01

The code 01 means that the specified package cannot be found due to there being no package list for that particular plan. You can execute the query below in the local location to find the problem.

SELECT LOCATION, COLLID, NAME
FROM SYSIBM.SYSPACKLIST
WHERE PLANNAME = 'plan-name';

If running this query does not return any rows, it means there was no package list corresponding to the plan.

Solution: Rebind the plan shown in the error message by adding the PKLIST option with the proper package list to the REBIND command.

Reason-Code 02

If your error message includes reason-code 02, there was no match found for the dbrm-name in the package list. This may occur because of BIND conditions of Application conditions.

BIND Conditions

When binding the plan-name in the error message, either the collection-id or the location-name in the package list was incorrect. Run the following query to be sure of the problem.

SELECT LOCATION, COLLID, NAME
FROM SYSIBM.SYSPACKLIST
WHERE PLANNAME = 'plan-name';

Solution: Make sure the collection ID and the location of the entry in PKLIST are correct. Then rebind the plan-name using the REBIND subcommand.

Application Conditions

Using the accurate encoding scheme that matches with that in the subsystem parameters is necessary while using SET CURRENT PACKAGESET =: HV. Otherwise, the user may face reason-code 02. Another reason could be the application being connected to the wrong location.

Solution: Correct the SET CURRENT PACKAGESET instruction and connect to the proper location.

Reason-Code 03

This code identifies the problem with one or more entries being found for the dorm-name in the SQLCODE -805 error message. It also indicates that the package is missing from all of the matching entries. Besides that, the causes behind reason-code 02 also apply to reason-code 03. Other reasons also include:

An unbound DBRM version of the application is getting executed. To determine this problem, you can use the following queries.

If the message’s collection-id value is empty:

SELECT COLLID, NAME, HEX(CONTOKEN), VERSION
FROM location-name.SYSIBM.SYSPACKAGE
WHERE NAME = 'dbrm-name'
AND HEX(CONTOKEN) = 'consistency-token';

Otherwise:

SELECT COLLID, NAME, HEX(CONTOKEN), VERSION
FROM location-name.SYSIBM.SYSPACKAGE
WHERE NAME = 'dbrm-name'
AND HEX(CONTOKEN) = 'consistency-token'
AND COLLID = 'collection-id';

If you don’t get any rows as output, know that the bound package is of an incorrect version.

Solution: Make sure to bind the DBRM that has the same version as the application program with the collection-id. Or run the application program that has the proper version. The consistency token of the bound package should and the application should be the same.

Reason-code 04

The location indicated by the location-name does not contain the package. The discovery of this problem will require you to run the same query used to determine the reason-code 3. 

Conclusion

You might want to contact your database administrator or application provider if none of the above solutions work. Here’s one last tip to bypass the DB2 SQLCODE -805 error. If a regeneration is required due to a change in the native SQL procedure, bind the additional package to the copy as well.