DB2 Troubleshooting SQLCODEs: A Practical z/OS Decision Guide

A decision process that works under pressure

Start by asking whether the code is negative, zero, or positive. A negative SQLCODE means the SQL statement was unsuccessful. Zero means successful execution. A positive SQLCODE is a warning or special condition: useful work might have occurred, but the program must inspect the condition. Never remove the sign when copying a code. +100 and -100 are not interchangeable, and a message search for “805” is less precise than a search for “SQLCODE -805 Db2 for z/OS.”

• 1. Freeze the evidence. Record the job, transaction, user or authorization ID, subsystem member, timestamp, failing statement, host-variable values where safe, and the complete SQL communication area.
• 2. Classify the failure. Is it package preparation, data, locking, resource availability, authorization, connection, or an expected no-row condition?
• 3. Read the inserted tokens. Reason codes, package names, index identifiers, and resource names are often more actionable than the SQLCODE alone.
• 4. Establish transaction state. Before retrying, determine whether Db2 rolled back the statement or the entire current unit of work.
• 5. Choose the owner. A predicate bug belongs with the programmer; an unavailable table space might require the DBA; an attachment failure can require systems or security support.
• 6. Apply one evidence-based change. Then reproduce with controlled inputs and verify both the SQL result and commit behavior.

Read the SQLCA, tokens, and DSNTIAR output

Traditional embedded SQL programs receive an SQLCA. SQLCODE is the signed result and SQLSTATE is the portable five-character state. SQLERRMC contains message tokens separated in the form supplied by Db2; those tokens can identify a package, reason code, authorization operation, or unavailable resource. SQLERRD is an array of diagnostic integers whose meaning depends on the statement and result. SQLWARN0 indicates that one or more SQLWARN flags are set. Preserve these fields before issuing another SQL statement, because the next statement can replace the diagnostic information.

DSNTIAR is the standard Db2-supplied formatting routine commonly called by COBOL, PL/I, and assembler programs. The application passes the SQLCA and an output area; DSNTIAR returns readable message lines with the inserted tokens resolved. It does not fix the error and it does not replace structured logging. Store the formatted text together with the raw SQLCODE, SQLSTATE, program name, statement identifier, and unit of work context.

1
2
3
4
5
6
7
8
9
10
11
12
Diagnostic record
-----------------
PROGRAM:      PAYUPD01
SUBSYSTEM:    DB2P
SQLCODE:      -904
SQLSTATE:     57011
SQLERRMC:     
STATEMENT:    UPDATE CUSTOMER_ACCOUNT ...
PLAN/PACKAGE: 
UOW STATE:    
DSNTIAR:      
NEARBY LOGS:  

For current message wording and token definitions, use IBM documentation for the exact Db2 release. Do not parse English message text when a documented token or field is available, and do not invent a meaning for a reason code from a partial console line.

Package and program preparation failures

SQLCODE -805: package not found

SQLCODE -805 means the package named in the message could not be found for runtime use under the application plan. The message identifies location, collection, package or DBRM name, consistency token, plan, and a reason code. Common causes include a plan without the required PKLIST entry, the wrong collection, an incorrect CURRENT PACKAGESET value, a package that was never bound, a newly deployed load module whose consistency token is not present, or a missing remote package.

Capture the complete package name and reason code first. Compare the plan package list in SYSIBM.SYSPACKLIST with the collection and name in the message. Compare the consistency token with the relevant package row in SYSIBM.SYSPACKAGE. Confirm which load library and plan the job actually used. The programmer response is not “ignore -805”; it is to align the running program, package generation, collection, PKLIST, and connection location. Production deployment should promote the load module and its matching bind input as one controlled change.

SQLCODE -818: timestamp mismatch

SQLCODE -818 means the precompiler-generated timestamp carried by the running load module does not match the timestamp built into the package or plan from the DBRM. The usual mistake is mixing generations: today's object module was linked while yesterday's DBRM was bound, or a new DBRM was bound while an old load module still runs. Recompile alone is not a reliable cure. Locate the DBRM produced by the same precompile as the object code in the load module, bind that DBRM as required by the site's package design, and make sure execution selects that matching load.

1
2
3
4
5
6
One consistent generation
PRECOMPILE source
  ├─ modified source → COMPILE/LINK → load module
  └─ matching DBRM   → BIND PACKAGE / BIND PLAN
 
-818 appears when the runtime load and bound DBRM came from different precompiles.

Data cardinality and key failures

SQLCODE -811: more than one row where one was required

SQLCODE -811 occurs when an operation that requires a single row produces more than one row. A familiar example is SELECT INTO with predicates that are not unique. It can also arise from a scalar fullselect that returns multiple rows. Inspect the actual input values and run a diagnostic SELECT that exposes all qualifying rows. Then decide whether the data violates a business rule or the SQL omitted a key predicate.

1
2
3
4
5
6
7
8
9
10
11
-- Risk: LAST_NAME is not guaranteed to be unique
SELECT EMPNO, FIRSTNME
INTO :HV-EMPNO, :HV-FIRSTNME
FROM EMP
WHERE LASTNAME = :HV-LAST-NAME;
 
-- Better when the business request supplies the unique employee number
SELECT FIRSTNME, LASTNAME
INTO :HV-FIRST-NAME, :HV-LAST-NAME
FROM EMP
WHERE EMPNO = :HV-EMPNO;

Adding FETCH FIRST 1 ROW ONLY merely to suppress -811 can select an arbitrary row. Use it only when choosing one row is truly the requirement, and pair it with ORDER BY that expresses which row is wanted. If multiple rows are valid business results, use a cursor rather than SELECT INTO.

SQLCODE -803: duplicate value for a unique key

SQLCODE -803 means an INSERT or UPDATE would create duplicate values prohibited by a unique index or unique constraint. Use the message tokens and catalog information to identify the enforcing index or constraint, then capture the key values. Check for a repeated business request, flawed number generation, incorrect restart processing, an UPDATE moving a row onto an existing key, or two concurrent tasks attempting the same logical insert.

Do not convert every -803 into a successful result. Sometimes it does mean “the request was already completed,” but only an explicit idempotency rule can establish that. Otherwise issue a meaningful application error or correct the key-generation design. A prior existence check followed by INSERT is still vulnerable to a race; the application must handle the unique constraint as the final authority.

Unavailable resources and locking

SQLCODE -904: resource unavailable

SQLCODE -904 says the SQL statement could not run because a required resource was unavailable. The reason code, resource type, and resource name in the message are essential. They can point to an object in a restrictive state, a stopped database, a physical resource, or another documented availability condition. Save all three tokens. Check relevant DSN messages and DISPLAY output, then involve the DBA or system programmer when the resource state is outside application control.

The programmer should avoid a blind retry loop. Some conditions are temporary; others remain until START DATABASE, recovery, a utility, or another administrative action changes the state. Determine the documented response for the exact reason code and confirm transaction state before retrying.

SQLCODE -911 and -913: deadlock or timeout

Both codes indicate unsuccessful execution caused by a deadlock or timeout condition, with the reason code distinguishing the specific condition. Their most important programming difference is rollback scope. For -911, Db2 rolls back the current unit of work. For -913, the unsuccessful execution does not include that unit-of-work rollback; the application must decide whether to issue ROLLBACK and how to continue. Attachment environments can influence which code the application receives, so always program to the documented behavior of the code that was returned.

A safe retry policy has a small limit, delay or backoff, logging, and a complete restart point. Reduce lock duration by committing at correct business boundaries, accessing objects in a consistent order, using suitable indexes so fewer rows are examined, and avoiding user think time inside a transaction. Never commit partial business work just to silence locking symptoms.

Authorization and connection failures

SQLCODE -922: authorization failure

SQLCODE -922 reports an authorization failure involving the indicated authorization ID and operation. Capture the tokens, including the authorization type or operation, and establish the runtime primary and secondary authorization IDs. The ID used by batch, TSO, CICS, IMS, or a distributed connection might not be the ID a developer expected. Verify the required system or connection authority with security and Db2 administration; do not grant broad authority merely to make the message disappear.

SQLCODE -923: connection not established

SQLCODE -923 means the application could not establish the Db2 connection. Its reason and type tokens guide diagnosis. Check that the correct subsystem is available, that the attachment facility is functioning, and that the authorization ID is permitted to connect. Review nearby attachment and subsystem DSN messages. Changing application SQL text will not repair a connection that was never established.

SQLCODE -924: internal connection error

SQLCODE -924 reports an internal connection error involving a documented function or reason code. Preserve the exact tokens, attachment environment, and preceding messages. This normally requires correlation with Db2, attachment, CICS, IMS, or z/OS diagnostic information rather than a data correction. The programmer's job is to retain the evidence and avoid masking the original failure with cleanup SQL that produces a second code.

Positive SQLCODEs still require logic

SQLCODE +100: no row found

SQLCODE +100 means no row satisfied the operation. In a cursor loop, it is the normal end-of-data signal: stop fetching and continue normal processing. For SELECT INTO it means no qualifying row was found. For searched UPDATE or DELETE, it can mean no row matched. Whether that is acceptable depends on the business operation. Initialize host variables deliberately and do not use their previous contents after +100 as if a new row had been returned.

1
2
3
4
FETCH loop decision
  SQLCODE = 0     → process the fetched row
  SQLCODE = +100  → end the loop normally
  otherwise       → capture SQLCA, format with DSNTIAR, and handle the failure

SQLCODE +802: arithmetic exception warning

SQLCODE +802 is a warning that an arithmetic exception occurred while evaluating an arithmetic expression. Examples include overflow, division by zero, or another numeric condition described by the message tokens and SQLWARN fields. Because it is positive, code that tests only “SQLCODE less than zero” can miss it. Inspect the expression, source data, result data type, null indicators, SQLSTATE, tokens, and warning flags. Correct the formula or data validation rather than silently accepting an unusable result.

1
2
3
4
5
-- NULLIF prevents division by zero when a null result is acceptable
SELECT TOTAL_AMOUNT / NULLIF(ITEM_COUNT, 0)
INTO :HV-AVERAGE :HV-AVERAGE-IND
FROM ORDER_TOTAL
WHERE ORDER_ID = :HV-ORDER-ID;

In this example, the indicator variable is part of the design because the expression can return null. That is different from ignoring +802 after it occurs. Choose the intended business result for a zero denominator, validate numeric ranges, and use DECIMAL precision and scale appropriate to the largest legitimate result.

Common troubleshooting mistakes

• Rerunning immediately and losing the original SQLCA, tokens, and subsystem messages.
• Treating every positive SQLCODE as success or every +100 as an application failure.
• Retrying -911 and -913 with identical logic even though transaction state differs.
• Rebinding randomly for -805 or -818 without proving which load module, DBRM, package, collection, and plan were used.
• Hiding -811 with an unordered one-row limit instead of correcting cardinality.
• Granting excessive authority for -922 instead of identifying the exact runtime ID and required operation.
• Reporting only “Db2 failed” when -904 supplied the resource type, name, and reason.

Explain It Like I'm Five

Imagine Db2 is a careful librarian. An SQL statement is your request. The SQLCODE is the librarian's short answer, while the tokens are the note explaining why. -805 says the requested instruction book is not on the shelf the library plan uses. -818 says the book and its checkout card came from different editions. -811 says you asked for one red book but several matched. -803 says you tried to give two books the same unique library number. -904 says a shelf is closed. -911 and -913 say two people waited on each other or waited too long, but they differ in how much work was put back. +100 says there are no more books to hand you. Good troubleshooting means reading the whole note before deciding what to do.

Exercises

1. Design a diagnostic log record containing SQLCODE, SQLSTATE, SQLERRMC, SQLWARN fields, statement identifier, package, plan, subsystem, and transaction state.
2. A batch deployment returns -805 after a new load module is promoted. List the catalog and runtime facts you would compare before requesting a rebind.
3. Rewrite a SELECT INTO that returns -811 as a cursor when multiple rows are valid. Explain why an unordered FETCH FIRST 1 ROW ONLY is not equivalent.
4. Describe separate recovery flows for -911 and -913, including when your application performs ROLLBACK and how a bounded retry is controlled.
5. Create test cases for +100 at cursor end, +100 on a singleton SELECT, and +802 from a zero denominator. State the expected host-variable and indicator handling.
6. For a sample -904 message, identify the reason code, resource type, and resource name, then decide whether the programmer, DBA, or system programmer owns the next action.