Diagnose DB2 SQLCODE -805

Diagnose SQLCODE -805

At run time, DB2 must connect the executable program to a bound package. The program carries a consistency token from precompile. The plan carries a package list (PKLIST) that names collections and, optionally, locations. DB2 searches that list, honors CURRENT PACKAGESET when set, and looks for a package whose name and token match. When the search fails, you get -805 with SQLSTATE 51002.

The message text is long on purpose. It names location, collection, DBRM or package name, consistency token, plan name, and a reason code. Beginners who only remember "package not found" waste hours binding the wrong collection or rebinding a plan that already pointed at the correct place. Always copy the full token string and the reason code into the ticket before you change anything.

Related codes sit nearby. -818 means timestamps disagree between the load module and the bound DBRM. -805 means the search never found the needed package entry. You can fix a missing PKLIST and then immediately hit -818 if the package you finally find belongs to a different precompile generation. Treat the codes as neighbors, not synonyms.

Prerequisites

• The full -805 message: location, collection, package or DBRM name, consistency token, plan name, and reason code
• Authority to query SYSIBM.SYSPACKAGE, SYSIBM.SYSPACKLIST, and related catalog views your site permits
• Access to the DBRM library, load library, and bind JCL or bind product used for the failing program
• Knowledge of whether the SQL is local, remote (three-part name / CONNECT), CICS, IMS, batch, or DDF
• A test subsystem or controlled window where you can BIND or REBIND without surprising production

Steps

Step 1: Capture the message tokens and reason code

Write down every qualifier from the SQLCODE message. Collection may be blank; that is still information. A blank collection often means CURRENT PACKAGESET was blank and the search depended entirely on PKLIST entries. Note the environment: batch plan name, CICS RCT entry, IMS plan, or distributed package collection defaults differ by attach.

1
2
3
4
5
6
7
8
Example shape of -805 tokens:
  PACKAGE NAME: location.collection.package.consistency-token
  PLAN:         APPPLAN1
  REASON:       03
 
SQLSTATE: 51002
 
Action: do not bind yet — decode REASON first.

Step 2: Prove what the plan is allowed to search

Query the package list for the plan named in the message. You are looking for collections (and locations) the plan can see. If the list is empty, reason 01 is already explained. If the list exists but omits the collection your package lives in, reason 02 is likely.

1
2
3
4
SELECT LOCATION, COLLID, NAME
FROM SYSIBM.SYSPACKLIST
WHERE PLANNAME = 'APPPLAN1'
ORDER BY LOCATION, COLLID, NAME;

Also confirm whether the application sets CURRENT PACKAGESET or relies on a default collection naming standard. A package can exist and still be invisible to the plan if the runtime collection qualifier never appears in the search path.

Step 3: Prove whether the package exists and which token it has

Query SYSPACKAGE for the collection and name from the message. Compare HEX of the consistency token when your site stores or displays tokens that way. Check VERSION if your shop uses versioned packages. A row with the right name but a different token is the classic reason 03 story after a partial promote.

1
2
3
4
5
6
7
8
9
10
11
SELECT COLLID,
       NAME,
       VERSION,
       HEX(CONTOKEN) AS CONTOKEN_HEX,
       BINDTIME,
       OWNER,
       QUALIFIER
FROM SYSIBM.SYSPACKAGE
WHERE COLLID = 'APPCOLL'
  AND NAME = 'ORDENTRY'
ORDER BY BINDTIME DESC;

If no row exists, you need a BIND PACKAGE from the correct DBRM into that collection. If rows exist with other tokens, decide whether to bind a new version, replace the package, or move the matching load module that belongs with an existing token. Do not guess: match the token the failing load module actually carries.

Step 4: Separate local missing packages from remote missing packages

Reason 04 and remote location names mean the local search may be fine while the remote server lacks the package. Distributed applications, three-part names, and CONNECT TO paths require packages at the remote location as well as correct local requester setup. Bind on the remote subsystem into the collection the remote search expects, then retest the distributed path—not only a local SPUFI smoke test.

Step 5: Repair with a controlled bind sequence

Use one generation of source: precompile to produce the DBRM, compile and link the same objects into the load module, then BIND PACKAGE with that DBRM. Add or correct PKLIST and REBIND PLAN when reason 01 or 02 requires it. Prefer a change-controlled collection and version naming standard so operators can see which generation is live.

1
2
3
4
5
6
7
Typical repair sequence (site JCL names will differ):
  1. Precompile program ORDENTRY -> DBRM ORDENTRY
  2. Compile / link load module ORDENTRY
  3. BIND PACKAGE(APPCOLL) MEMBER(ORDENTRY) ...
  4. If needed: REBIND PLAN(APPPLAN1) PKLIST(APPCOLL.*) ...
  5. Refresh CICS/IMS/library allocations if the load module moved
  6. Rerun the failing transaction or batch step

For dynamic client packages (JDBC/CLI style NULLID collections on some platforms), the same idea applies: the collection and package names in the error must exist on the DB2 for z/OS server those clients call. Bind the required client package lists your site standard documents, rather than inventing a one-off collection name.

Step 6: Recheck authorization and operational promotion

A package that exists can still fail later with authority errors, but -805 itself is about discovery. Still verify that your promote path copied the DBRM, bound in the target subsystem, and activated the matching load library. Many "it worked in test" incidents are simply a production PKLIST or collection that never received the new bind.

Verify results

1. Rerun the exact failing program path and confirm SQLCODE 0 (or the expected application success path) instead of -805.
2. Requery SYSPACKAGE and confirm the collection, name, version, and consistency token match the load module generation you just installed.
3. Requery SYSPACKLIST and confirm the plan search includes that collection or location.
4. For remote cases, verify the package on the remote server and retest with the same CONNECT or three-part name path.
5. Watch the next promote: ensure bind output is kept as evidence beside the load-module change ticket.

Common errors

Another frequent error is binding MEMBER with the wrong DBRM library concatenation, so you "successfully" bind an old DBRM while the new load module carries a new token. Always confirm library names in the bind job against the compile job that produced the load module. If -805 clears and -818 appears, you have moved from discovery failure to generation mismatch—continue with a matched precompile and bind.

Explain it like I'm 5

Your program is a toy that needs a matching instruction card. The plan is a folder of drawers where instruction cards are kept. SQLCODE -805 means DB2 opened the folder and could not find the exact card with the right drawer name and the right secret stamp. Maybe the folder has no drawers listed, maybe you looked in the wrong drawer, maybe the stamp does not match, or maybe the card is at a friend's house (a remote location). Fixing it means putting the right card in the right drawer and making sure the folder knows to look there.

Exercises

1. Decode a fictional -805 with reason 02, collection PAYCOLL, plan PAYPLAN. Write the catalog queries you would run first.
2. Explain why a package can exist in SYSPACKAGE and still produce -805 for a given plan.
3. Compare reason 03 with SQLCODE -818. When would you expect each after a bad promote?
4. Draft BIND PACKAGE and REBIND PLAN statements for collection TRNCOLL and plan TRNPLAN that includes TRNCOLL.*.
5. Create a promote checklist that prevents test-only binds from being forgotten in production.