DB2 SQLCODEs -007 to -119: syntax and semantics

How to read this range

Syntax class SQLSTATE 42xxx means “change the statement.” Nothing was inserted or updated. Precompile errors often show the same SQLCODE in DSNH messages before the program even runs. Dynamic SQL surfaces them at PREPARE or EXECUTE IMMEDIATE. GET DIAGNOSTICS DB2_LINE_NUMBER helps when the source has new-line characters.

-007 illegal character

IBM: STATEMENT CONTAINS THE ILLEGAL CHARACTER invalid-character. The statement cannot be processed. Programmer response: correct the syntax and resubmit. SQLSTATE 42601.

Beginner causes: a curly quote from a PC editor instead of a straight apostrophe; a tab or unprintable byte in a dynamic string; a character that is legal in COBOL but not in SQL; concatenating host text that includes & or ! from JCL. The token in SQLERRMC is the offending character. Fix the source; do not RETRY the same string.

1
2
3
4
5
-- Wrong: word-processor quotes
SELECT LASTNAME FROM EMP WHERE EMPNO = ‘000010’;
 
-- Right: SQL apostrophes
SELECT LASTNAME FROM EMP WHERE EMPNO = '000010';

-020 and -054 (not in current z/OS Codes)

The Db2 12 for z/OS Codes volume lists -007, then -010, -011, -029, -051, … It does not list -020 or -054. Other IBM SQL products (LUW, Db2 for i) use overlapping numbers with different text. If a wiki says “-020 host variable count” or “-054 keyword ALL,” treat that as unverified for z/OS 12 until you find it in your subsystem’s Codes PDF.

On z/OS, a host-variable count mismatch at execute time is more often -804 / SQLSTATE 07002 (SQLDA) or a precompiler error, not a documented -020. Misuse of ALL in some statements is diagnosed with other 42-class codes. If you receive -020 or -054 on your system, capture SQLERRMC, SQLSTATE, and DSNT408I and look them up for your version rather than copying LUW text.

Nearby syntax codes that show up first

-010: string constant not terminated (missing quote). SQLSTATE 42603. One missing apostrophe can also cause a cascade of later -007-looking junk.

-029: embedded SELECT in an application must have INTO; dynamic SELECT must not. SQLSTATE 42601. Beginners write SELECT … FROM EMP in COBOL like SPUFI.

1
2
3
4
5
6
7
8
9
      * Wrong in COBOL (no INTO) — SQLCODE -029
      *    EXEC SQL SELECT LASTNAME FROM EMP WHERE EMPNO = :HV END-EXEC.
      *
           EXEC SQL
                SELECT LASTNAME
                  INTO :HV-LNAME
                  FROM EMP
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.

-084 unacceptable SQL statement

IBM lists several situations under UNACCEPTABLE SQL STATEMENT:

• PREPARE or EXECUTE IMMEDIATE of a statement that cannot be prepared
• Embedded SQL that Db2 does not support
• Statement referenced an undeclared cursor
• PREPARE of ALLOCATE CURSOR when the statement identifier is already tied to a declared cursor
• A remotely executed statement referenced an array variable or element (with documented CALL/FETCH/SET exceptions)

System action: the statement cannot be processed. Programmer response depends on the case: fix the string you are preparing, remove unsupported SQL, DECLARE the cursor, use a free statement name for ALLOCATE, or keep array references local. This is not “illegal character” (-007) and not “wrong number of INSERT values” (-117).

1
2
3
       MOVE 'COMMIT' TO DYN-STMT
       EXEC SQL PREPARE S1 FROM :DYN-STMT END-EXEC.
      * COMMIT is not a preparable dynamic statement — expect -084

-117 value count does not match columns

IBM: THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED COLUMNS. Either the INSERT value list does not match the object columns, or the right side of a SET assignment / UPDATE SET does not match the left side. System action: no data inserted. Programmer response: specify one and only one value for each object column. SQLSTATE 42802.

Implied columns means INSERT INTO T VALUES (…) with no column list: you must supply every column that INSERT requires (identity/row-change-timestamp defaults still follow the table’s rules—count what the statement actually provides). A trailing comma, a missing NULL, or aligning COBOL host lists by eye are the usual bugs.

1
2
3
4
5
6
7
8
-- -117: two columns, one value
INSERT INTO EMP (EMPNO, LASTNAME) VALUES ('000010');
 
-- OK
INSERT INTO EMP (EMPNO, LASTNAME) VALUES ('000010', 'SMITH');
 
-- -117 on UPDATE SET
UPDATE EMP SET (SALARY, BONUS) = (50000);

-119 HAVING clause not valid

IBM: a column or expression in HAVING, possibly inside a scalar function, does not appear in GROUP BY. HAVING items must be in aggregate functions or also in GROUP BY. Grouping expressions in HAVING must match GROUP BY aside from blanks. Also issued when an expression starting with GROUPING appears in SELECT and there is no GROUP BY. SQLSTATE 42803.

Semantics, not a typo: SQL does not know which LASTNAME to show for a DEPTNO group unless LASTNAME is grouped or aggregated. Related -120 covers aggregates in SET, VALUES, index keys, and RETURN—wrong clause, different code.

1
2
3
4
5
6
7
8
9
10
11
-- -119: LASTNAME is not a group key or aggregate
SELECT DEPTNO, LASTNAME, COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING LASTNAME = 'SMITH';
 
-- OK: filter groups on an aggregate
SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 10;

System action and programmer response (all of these)

None of these codes change data. None close a cursor that was already open unless you were preparing the next statement as part of a larger mess. Fix SQL and rerun. DSNTIAR / GET DIAGNOSTICS MESSAGE_TEXT repeats the IBM short text with tokens. Precompiler DSNH messages may cite the same SQLCODE at compile time—fix the program before bind.

Explain It Like I'm Five

-007 is “you wrote a letter with a crayon scribble that is not in the alphabet.” -084 is “you handed the librarian a grocery list and asked them to check out a book.” -117 is “the lunchbox has three slots and you packed two sandwiches.” -119 is “you asked each classroom (group) a question about one kid who is not the classroom label.” -020 and -054 are names on an old cheat sheet that do not match this library’s current dictionary—look them up before you treat them as real.

Exercises

1. Produce -117 with INSERT, then fix it. Produce -119 with HAVING, then fix it.
2. Explain why PREPARE of 'ROLLBACK' can yield -084 while EXECUTE IMMEDIATE of a searched DELETE can work.
3. A dynamic SQL string built in COBOL contains a hexadecimal zero in the middle. Which SQLCODE is more likely, -007 or -117, and why?
4. Look up -007 in IBM Docs for your version and write the SQLSTATE and programmer response in your own words.
5. If a forum post says SQLCODE -054 on z/OS 13, what two documents do you check before you believe the LUW explanation?