DB2 SQLCODE +000 and +100

SQLCODE 0 — successful execution

IBM’s successful-execution topic is numbered 000. The integer in the SQLCA is 0. Programmers write +000 in tables so it sits next to +100. There is no different “plus zero” value.

• SQLWARN0 blank — success, no warning flags
• SQLWARN0 = W — success with warning; at least one other SQLWARN flag is W or Z
• SQLSTATE 00000 for unqualified success

System action: the statement completed. INSERT/UPDATE/DELETE changed the rows that qualified (count in SQLERRD(3) / GET DIAGNOSTICS ROW_COUNT). FETCH filled host variables. OPEN positioned the cursor before the first row (the first FETCH still has to run).

Programmer response: if SQLWARN0 is W, do not ignore it in programs that care about truncation (SQLWARN1), nulls dropped from AVG (SQLWARN2), or extra result columns (SQLWARN3). GET DIAGNOSTICS MESSAGE_TEXT is empty when SQLCODE is 0 even if SQLSTATE indicates a warning—use the SQLWARN flags or CONDITION items, not MESSAGE_TEXT, for that case.

1
2
3
4
5
6
7
8
9
10
11
       EXEC SQL
            UPDATE EMP
               SET SALARY = SALARY * 1.03
             WHERE DEPTNO = :HV-DEPT
       END-EXEC.
       IF SQLCODE = 0
           DISPLAY 'ROWS UPDATED ' SQLERRD(3)
           IF SQLWARN0 = 'W'
               PERFORM REVIEW-WARNINGS
           END-IF
       END-IF.

SQLCODE +100 — no row found

IBM text: ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE. SQLSTATE 02000. System action: no data was retrieved, updated, or deleted—with the rowset exception below.

FETCH loops

The pattern is OPEN, FETCH, process while SQLCODE is 0, FETCH until +100, CLOSE. Testing +100 after PROCESS is too late if you process first. Never treat +100 as “use the hosts anyway.”

1
2
3
4
5
6
7
8
9
10
       EXEC SQL OPEN C1 END-EXEC.
       EXEC SQL FETCH C1 INTO :HV-EMPNO, :HV-LNAME END-EXEC.
       PERFORM UNTIL SQLCODE = +100
           IF SQLCODE NOT = 0
               PERFORM SQL-ERROR
           END-IF
           PERFORM WRITE-REPORT-LINE
           EXEC SQL FETCH C1 INTO :HV-EMPNO, :HV-LNAME END-EXEC
       END-PERFORM.
       EXEC SQL CLOSE C1 END-EXEC.

Singleton SELECT versus cursor

SELECT INTO with one row expected: 0 means you have the row, +100 means it does not exist, -811 means more than one row (use a cursor or tighten the predicate). Those three codes are the whole singleton story.

UPDATE and DELETE

A searched UPDATE that matches nothing is +100, not an error. SQLERRD(3) is 0. That is different from -803 (duplicate) or -530 (RI). Your business rule decides whether zero rows is acceptable.

Rowset FETCH

If you asked for a rowset of 20 and only 7 remain, IBM documents +100 with a partial rowset: data is returned only for the rows actually fetched, and SQLERRD(3) (or ROW_COUNT) is 7. Process those 7, then end the loop. Do not discard the partial set.

SPUFI and LOBs

In SPUFI, +100 on a SELECT is normal completion. IBM also documents +100 when LOB data cannot be returned (for example isolation UR while the LOB table space is locked). That is easy to misread as “empty table” when the base row exists—check isolation and LOB locks if a LOB SELECT INTO surprises you with +100.

Successful execution versus no row

Both 0 and +100 are non-negative. WHENEVER SQLERROR does not catch +100; WHENEVER NOT FOUND does. SQL PL CONTINUE HANDLER FOR NOT FOUND is the +100 handler. Do not DECLARE HANDLER FOR SQLEXCEPTION expecting to see empty FETCH—that is NOT FOUND, class 02, not an exception.

GET DIAGNOSTICS after +100: ROW_COUNT is 0 for a failed singleton or empty searched UPDATE; for a partial rowset it is the short count. DB2_RETURNED_SQLCODE is +100. MESSAGE_TEXT describes the not-found condition.

Explain It Like I'm Five

You ask for a cookie. SQLCODE 0 is “here is your cookie” (sometimes the cookie is slightly crumbled—that is a warning flag, still a cookie). SQLCODE +100 is “the jar is empty.” Empty is not the same as “I dropped the jar and we have to sweep the kitchen” (a negative code). A FETCH loop is reaching into the jar until the empty answer, counting only the cookies you actually got.

Exercises

1. Write a COBOL FETCH loop that prints rows and stops cleanly on +100 without printing a ghost extra line.
2. Contrast SQLCODE 0, +100, and -811 for SELECT INTO FROM EMP WHERE EMPNO = :HV.
3. A batch UPDATE returns +100. Should the job step RC be 0, 4, or 8? Defend a shop standard.
4. Describe how a rowset FETCH of size 10 ending with SQLERRD(3)=3 and SQLCODE +100 should be processed.
5. Why does WHENEVER SQLERROR miss a missing employee on SELECT INTO?