SQLCODE -805 — package not found

What happened

A batch step or CICS transaction fails on its first SQL statement. The program's error routine reports SQLCODE -805, often followed by a user abend the site has chosen. Nothing was read or updated.

DSNT408I SQLCODE = -805, ERROR:  DBRM OR PACKAGE NAME
         DB2P.PAYCOLL.PAYR0100.1A2B3C4D5E6F7A8B NOT FOUND IN PLAN
         PAYPLAN. REASON 03
DSNT418I SQLSTATE   = 51002 SQLSTATE RETURN CODE

What it means

Every Db2 program is precompiled into a DBRM, which is bound into a package. At run time the plan searches its package list for a package whose name and consistency token match the running load module. A -805 means that search found nothing. The SQLSTATE is 51002.

The message tokens in SQLERRMC tell you exactly what was searched for: location, collection, package name, consistency token, the plan name and a reason code. The reason code says whether the package was missing from the list, missing from the collection, or present with a different token. Look up its meaning in the Db2 codes documentation for your version.

Typical causes

• A load module was promoted but the matching package was never bound in this environment.
• The plan's package list (PKLIST) does not include the collection the package was bound into.
• The program was recompiled without a new BIND of its DBRM, so the consistency token no longer matches any package.
• The job or CICS DB2ENTRY uses a different plan than expected.
• A STEPLIB concatenation picks up an older or newer load module than the bound package.

Symptoms

The failure happens on the first SQL call, every time, for every input. That separates it from data errors like -803 or -180. Related code -818 (SQLSTATE 51003) means a package was found by name but its timestamp disagrees with the load module; it has the same root cause family.

Where to look

• The SYSOUT or job log line written by the program's DSNTIAR or GET DIAGNOSTICS routine.
• The Db2 catalog: SYSIBM.SYSPACKAGE (COLLID, NAME, CONTOKEN, BINDTIME) and SYSIBM.SYSPACKLIST (which collections the plan searches).
• The change record and bind job output from the last deployment.
• For CICS: the DB2ENTRY or DB2CONN definition that names the plan.

How to diagnose

1. Copy the full message text. Note collection, package, consistency token, plan and reason code.
2. Query SYSPACKAGE for that package name across all collections. Is it there at all? Under which collection? With which CONTOKEN and BINDTIME?
3. Query SYSPACKLIST for the plan. Does it include that collection, or a collection.* entry?
4. Compare the load module's link date and the package BINDTIME. A newer module than package suggests a missed bind of the new DBRM.
5. Confirm which load library the step actually used (STEPLIB, JOBLIB or CICS DFHRPL).
6. Check the change record: was a bind step part of the promotion and did it end RC 0?

How to fix

Bind the package from the same DBRM that came from the compile of the running load module, into the right collection. If the collection is missing from the plan, add it with a plan rebind. Never bind a DBRM from a different compile just to make the error go away; the token will still mismatch.

After the bind, rerun the step. If -805 hit the step's first SQL statement, nothing was updated and a straight rerun is normally safe; if it came from a called subprogram's package after earlier SQL had run, check what was committed first. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.

How to prevent

• Compile, link and bind in one controlled pipeline so module and package always move together.
• Make the bind step a mandatory, checked part of every promotion.
• Use consistent collection naming per environment and keep plan package lists simple.
• Display the full SQLCA message in every Db2 program's error routine.

Production considerations

Interview question

A program that ran fine yesterday now gets SQLCODE -805 on its first SQL call. What do you check?