Other common positive SQLCODEs in DB2

Real warning SQLCODEs

+802 arithmetic exception (warning)

IBM: an exception (decimal overflow, divide, exponent overflow, fixed-point overflow, invalid operation, overflow, subnormal, underflow, zero divide) occurred while adding, subtracting, multiplying, dividing, negating, or applying a built-in function to DECIMAL, FLOAT, SMALLINT, or INTEGER data in an outer SELECT-list expression. Position-number is the select-list item.

System action: the indicator variable for that expression is -2 (null); the data host is unchanged; other columns and expressions still return; a cursor stays open. SQLSTATE 01519. Programmer response: inspect the expression and the data. Allowed numeric ranges are discussed with SQLCODE -405.

This is not “the job failed.” A report program can print the other columns and show NULL for salary. If your shop treats any arithmetic problem as fatal, test for +802 (and SQLWARN flags) and escalate. There is also a negative -802 when the arithmetic failure is not handled as a SELECT-list warning—do not assume 802 is always plus.

1
2
3
4
5
6
7
8
9
       EXEC SQL
            SELECT EMPNO, SALARY / :HV-DIVISOR
              INTO :HV-EMPNO, :HV-RATIO:HV-RATIO-IND
              FROM EMP
             WHERE EMPNO = :HV-KEY
       END-EXEC.
       IF SQLCODE = +802 OR HV-RATIO-IND = -2
           DISPLAY 'ARITHMETIC WARNING ON RATIO'
       END-IF.

+222 and +231 (scrollable cursors)

+222: while FETCHing a SENSITIVE STATIC cursor, Db2 refetched the base row and found a delete hole (row gone) or update hole (row no longer satisfies the predicate). No data is fetched; the cursor sits on the hole. SQLSTATE 02502. Raise isolation or handle holes in the program.

+231: FETCH orientation CURRENT, CURRENT ROWSET, RELATIVE 0, or ROWSET STARTING AT RELATIVE 0 when the cursor is not on a row. Nothing fetched; position unchanged. Typical after you already saw +100 or +222 and then asked for CURRENT.

+304, +445, +562, +610

+304: a fetched value does not fit the host variable’s range (for example a large DECIMAL into a SMALLINT host). SQLSTATE 01515. Fix the host type or CAST.

+445: a value was truncated. SQLSTATE 01004. Common with CHAR/VARCHAR conversions. Pair with SQLWARN1 when the destination is a host variable.

+562: GRANT was ignored because the privilege was already held or could not be applied that way. The GRANT statement still “succeeds.” Do not treat it as a security failure unless your installer requires every GRANT to change something.

+610: CREATE or ALTER touched an object whose definition change is pending (materializing REORG not done yet). The statement worked; the object may not be fully in the new shape until REORG.

+904 versus -904

IBM documents +904 as “A RESOURCE IS UNAVAILABLE BUT PROCESSING CONTINUES” (SQLSTATE 01687) and states that Db2 for z/OS does not issue that SQLCODE. The Analytics Accelerator may return +904 on SQL OPEN for some accelerated queries when CURRENT QUERY ACCELERATION WAITFORDATA is used. Processing continues on the accelerator.

The code every DBA means by “904” is -904: unsuccessful execution because a resource was unavailable (reason-code, resource-type, resource-name). SQLSTATE 57011. A FETCH that hits -904 closes the cursor; later FETCH without OPEN is -501. Look up the reason code in the Codes book; SYSLOG often has the matching DSNT message.

The minus lookalikes (+805, +818, +811, +911, +913)

-805 and -818 (packages and plans)

-805: the package location.collection.dbrm.consistency-token was not found in plan-name. Reason 01 no PKLIST, 02 name did not match a list entry, 03 matched entries but no package (wrong version / not bound), 04 missing at a remote site. Fix BIND/PKLIST/CURRENT PACKAGESET or run the matching load module.

-818: precompiler timestamp in the load module ≠ bind timestamp from the DBRM. You compiled without binding, bound without linking, or mixed a DBRM from a different precompile. Bind and link the same precompile output. SQLSTATE 51003.

-811

Embedded SELECT or UPDATE SET subselect returned more than one row, or a basic predicate subquery returned more than one value. SQLSTATE 21000. Use a cursor, add a more selective predicate, or use an aggregate. Opposite of +100 (zero rows).

-911 and -913 (locking)

Both can be deadlock (00C90088) or timeout (00C9008E); SQLERRD(3) has the reason. -911 rolls back the UR (SQLSTATE 40001)—re-enter the work. -913 does not roll back (SQLSTATE 57033)—you COMMIT or ROLLBACK, and a FETCH -913 closes the cursor. Neither is a warning. IF SQLCODE = +911 will never see a deadlock.

1
2
3
4
5
6
7
8
9
       IF SQLCODE = -911
           DISPLAY 'UR ROLLED BACK DEADLOCK/TIMEOUT'
           PERFORM RESTART-UNIT-OF-WORK
       ELSE
           IF SQLCODE = -913
               DISPLAY 'STATEMENT FAILED LOCKS - DECIDE ROLLBACK'
               EXEC SQL ROLLBACK END-EXEC
           END-IF
       END-IF.

Explain It Like I'm Five

Plus codes (other than empty-jar +100) are “you got the cookie, but look: a crumb fell off” (+802: that one chocolate chip is missing so we handed you a napkin null). Minus 805/818 are “wrong lunchbox—this sandwich does not match the label.” Minus 811 is “you asked for one cookie and twelve fell out.” Minus 911 is “food fight—we took everyone’s trays back.” Minus 913 is “food fight at your table only—you still have to decide whether to keep eating.” Writing a plus in front of 911 does not make the food fight a crumb.

Exercises

1. Write COBOL that treats +802 as a warning (null indicator) but -802 or other negatives as errors.
2. Explain why IF SQLCODE = +911 is a bug, and write the correct tests for deadlock.
3. A SENSITIVE STATIC cursor gets +222. What will FETCH CURRENT do next (+231)?
4. Look up -805 reason 03 in IBM Docs and list two BIND mistakes that cause it.
5. Contrast +904 (Accelerator warning) with -904 (resource unavailable error) for an operator who only remembers “904.”