DB2 SQLCODEs -805 and -818: packages and plans

How to read this pair

Plans point at packages through a package list (PKLIST). Each package is identified by location, collection, DBRM/package name, and a consistency token (precompile fingerprint). -805 is “search failed.” -818 is “we found something to compare and the fingerprints disagree.” Both use SQLSTATE 51xxx (invalid application state / prepare environment).

-805 package not found in plan

IBM: PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code. Collection-id is blank if CURRENT PACKAGESET was blank for local execution.

Reason-code 01 — no package list

There is no package list for the plan. Query SYSIBM.SYSPACKLIST for the plan; if no rows, BIND/REBIND with PKLIST and the needed entries.

Reason-code 02 — DBRM name did not match PKLIST

Possible BIND issues:

• Wrong collection-id in PKLIST
• Wrong location-name in PKLIST
• Wrong CURRENTSERVER on BIND of the plan

Possible application issues:

• CURRENT PACKAGESET (or PACKAGE PATH) set incorrectly—encoding of the host on SET must match subsystem expectations
• Application not connected to the proper location

Reason-code 03 — PKLIST matched but package missing

Search found candidate entries but not the package. Same causes as 02, plus: the DBRM version was never bound (no package with that consistency token), or the wrong load module version is running. Bind the DBRM of the version you execute into the collection, or run the load that matches the bound token.

Reason-code 04 — remote package missing

Package does not exist at the remote location-name. Query SYSPACKAGE at that location. Native SQL procedures that SET PACKAGESET / PACKAGE PATH / CONNECT, or that touch remote objects, may need additional BIND COPY packages after regeneration.

Problem-determination queries (local)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Query 2: package list for the plan
SELECT LOCATION, COLLID, NAME
FROM SYSIBM.SYSPACKLIST
WHERE PLANNAME = 'plan-name';
 
-- Query 3: CURRENTSERVER on the plan
SELECT NAME, CURRENTSERVER
FROM SYSIBM.SYSPLAN
WHERE NAME = 'plan-name';
 
-- Query 4: matching package (add COLLID predicate when known)
SELECT COLLID, NAME, HEX(CONTOKEN), VERSION
FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'dbrm-name'
  AND HEX(CONTOKEN) = 'consistency-token';

System action: statement cannot be processed. SQLSTATE 51002. Do not “fix” this by ignoring SQLCODE—you must align PKLIST, collection, version, and connection.

-818 precompiler vs bind timestamp mismatch

IBM: THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z. Timestamps x and y are Db2-internal (not wall clock). z is the DBRM name.

At precompile, Db2 puts timestamp y in the DBRM and timestamp x in the application’s SQL parameter list. Bind stores the DBRM timestamp. At run time Db2 compares x for the statement with y from DBRM z. If they differ, the DBRM and the program are not from the same precompile.

Typical cases IBM lists:

• Precompiled, compiled, linked—but not bound
• Precompiled and bound—but not compiled/linked for that program
• Bound using a DBRM from a different precompile than the object module in the load module

Programmer response: bind again using the DBRM that matches the load module. SQLSTATE 51003.

1
2
3
4
5
6
7
Correct pipeline (one generation):
  1. Precompile PROG → PROG.DBRM + modified source
  2. Compile + link → PROG load module (contains timestamp x)
  3. BIND PACKAGE/PLAN with THAT PROG.DBRM (timestamp y)
  4. Run the load from step 2 under the plan from step 3
 
-818 if step 3 used yesterday's DBRM with today's load (or vice versa).

Operational tips

Versioning

Package VERSION helps when multiple consistency tokens coexist. -805 reason 03 often means you deployed a new load without binding the new DBRM into the collection your PKLIST searches. In change control, treat “promote load library” and “BIND PACKAGE COPY/REPLACE into collection X” as one change set. Splitting them across weekends is a classic Monday-morning -805/-818 storm.

Do not confuse with -807

-807 is package not enabled for a connection type/name (ENABLE/DISABLE on bind). Different SQLSTATE and fix (REBIND ENABLE, or check SYSPKSYSTEM). If CICS works but batch fails (or the reverse), compare ENABLE settings before you rebuild DBRMs.

Positive cousins

Some older materials mention “+818” style notes; on z/OS the error for timestamp mismatch is negative -818. Always confirm the sign in SQLCA.

CURRENT PACKAGESET surprises

Applications that SET CURRENT PACKAGESET to switch collections must use a collection that appears in the plan’s PKLIST (or is otherwise searchable per your bind design). A typo in the host variable, or an encoding mismatch on the SET, yields -805 with reason text that looks like “package missing” when the real bug is “wrong collection.” Dump the special register after SET and compare to SYSPACKLIST.

Remote and native SQL procedures

Distributed packages and regenerated native SQL procedures add extra bind steps (BIND COPY, remote SYSPACKAGE). When -805 reason 04 appears, verify the package at the remote location-name, not only at the local catalog. Local Query 4 with a location-qualified FROM clause is IBM’s recommended check.

What to put in the incident ticket

For -805: plan name, full package name from the message (location.collection.dbrm.token), reason-code, CURRENT PACKAGESET value, and whether the failing SQL is local or remote. Attach SYSPACKLIST and HEX(CONTOKEN) query output. For -818: precompile job name, DBRM library member timestamp/name, BIND job that produced the package, and load module library used at run time. That set usually separates “forgot to bind” from “bound the wrong generation” in one pass.

Explain It Like I'm Five

-805 is “the librarian cannot find the book your library card says you may borrow” (wrong shelf list, wrong collection, or never catalogued). -818 is “you brought a book cover from Monday and pages from Tuesday—the stamps do not match, so you cannot check out.”

Exercises

1. Run IBM Query 2 for a plan you use. Does PKLIST include your collection?
2. Explain reason-code 01 vs 03 in your own words.
3. Describe a change-management mistake that produces -818 without changing SQL source text.
4. HEX(CONTOKEN) from SYSPACKAGE—what must match the running program?
5. When would you BIND COPY for a native SQL procedure related to -805 reason 04?