SQL return codes

SQL return codes are used for the diagnosis of programming failures as a result of SQL calls by DB2 programs. An important feature of DB2 programs is the error processing. The error diagnostic containing the SQL return code is held in the field SQLCODE within the DB2 SQLCA block.

SQLCA

The SQL communications area (SQLCA) structure is used within the DB2 program to return error information to the application program. This information in the SQLCA and the SQLCODE field is updated after every API call for the SQL statement.

SQLCODE

The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive.

  0 means successful execution.
   
  Negative means unsuccessful execution with an error. 
  An example is -911 which means a timeout has occured with a rollback.

  Positive means successful execution with a warning. 
  An example is +100 which means no rows found.

  If you have an SQL code with a letter in it look at Letter Codes below.

Here is a more comprehensive list of the SQLCODEs for DB2:

Zero (Successful)

  0    Successful      

-007 STATEMENT CONTAINS THE ILLEGAL CHARACTER character
-010 String constant beginning string is NOT TERMINATED.
-029 INTO Clause Required.
-060 Invalid type Specification: "Spec"
-079 QUALIFIER FOR DECLARED GLOBAL TEMPORARY TABLE table-name MUST BE SESSION, NOT qualifier
-084 Unacceptable SQL Statement.
-097 THE USE OF LONG VARCHAR OR LONG VARGRAPHIC IS NOT ALLOWED IN THIS CONTEXT
-101 Statement is Too Long, or Too Complex.
-102 String constant is too long.
-103 Literal is an invalid Numeric Literal.
-104 Illegal Symbol token.
-105 Invalid String.
-107 The Name, "NAME" is Too Long, Maximum Allowable size is "SIZE".
-108 THE NAME name IS QUALIFIED INCORRECTLY
-109 "CLAUSE" clause is not permitted.
-110 Invalid Hexadecimal Literal Beginning "STRING"
-111 A Column Function does not include a column name.
-112 The Operand of a Column function is Another Column Function.
-113 Invalid Character found in Name: "NAME", Reason Code- "REASON-CODE".
-114 THE LOCATION NAME location DOES NOT MATCH THE CURRENT SERVER
-115 A PREDICATE Is Invalid because the comparison operator "OPERATOR" is followed by a 'parenthesized list' or "By Any" or "All" with out a Sub-query 
-117 The number of values in the INSERT does not match the number of columns.
-118 THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE
-119 A COLUMN IDENTIFIED IN A HAVING CLAUSE IS NOT INCLUDED IN THE GROUP BY CLAUSE
-120 A WHERE CLAUSE, SET CLAUSE, VALUES CLAUSE, OR A SET HOST-VARIABLE STATEMENT INCLUDES A COLUMN FUNCTION
-121 THE COLUMN name IS IDENTIFIED MORE THAN ONCE IN THE INSERT OR UPDATE OR SET TRANSITION VARIABLE STATEMENT
-122 A SELECT STATEMENT WITH NO GROUP BY CLAUSE CONTAINS A COLUMN NAME AND A COLUMN FUNCTION IN THE SELECT CLAUSE OR A COLUMN NAME IS CONTAINED IN THE SELECT CLAUSE BUT NOT IN THE GROUP BY CLAUSE
-123 THE PARAMETER IN POSITION n IN THE FUNCTION name MUST BE A CONSTANT OR KEYWORD
-125 AN INTEGER IN THE ORDER BY CLAUSE DOES NOT IDENTIFY A COLUMN OF THE RESULT
-126 THE SELECT STATEMENT CONTAINS BOTH AN UPDATE CLAUSE AND AN ORDER BY CLAUSE
-127 DISTINCT IS SPECIFIED MORE THAN ONCE IN A SUBSELECT
-128 INVALID USE OF NULL IN A PREDICATE
-129 THE STATEMENT CONTAINS TOO MANY TABLE NAMES
-130 THE ESCAPE CLAUSE CONSISTS OF MORE THAN ONE CHARACTER, OR THE STRING PATTERN CONTAINS AN INVALID OCCURRENCE OF THE ESCAPE CHARACTER
-131 STATEMENT WITH LIKE PREDICATE HAS INCOMPATIBLE DATA TYPES
-132 AN OPERAND OF value IS NOT VALID
-133 A COLUMN FUNCTION IN A SUBQUERY OF A HAVING CLAUSE IS INVALID BECAUSE ALL COLUMN REFERENCES IN ITS ARGUMENT ARE NOT CORRELATED TO THE GROUP BY RESULT THAT THE HAVING CLAUSE IS APPLIED TO
-134 IMPROPER USE OF LONG STRING COLUMN column-name OR AN EXPRESSION THAT RESOLVES TO A LONG STRING
-136 SORT CANNOT BE EXECUTED BECAUSE THE SORT KEY LENGTH IS GREATER THAN 4000 BYTES
-137 THE LENGTH RESULTING FROM operation IS GREATER THAN maximum-length
-138 THE SECOND OR THIRD ARGUMENT OF THE SUBSTR FUNCTION IS OUT OF RANGE
-142 THE SQL STATEMENT IS NOT SUPPORTED
-144 INVALID SECTION NUMBER number
-147 ALTER FUNCTION function-name FAILED BECAUSE SOURCE FUNCTIONS CANNOT BE ALTERED
-148 THE SOURCE TABLE source-name CANNOT BE RENAMED OR ALTERED
-150 THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW OR TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED
-151 THE UPDATE STATEMENT IS INVALID BECAUSE THE CATALOG DESCRIPTION OF COLUMN column-name INDICATES THAT IT CANNOT BE UPDATED
-152 THE DROP clause CLAUSE IN THE ALTER STATEMENT IS INVALID BECAUSE constraint-name IS A constraint-type
-153 THE STATEMENT IS INVALID BECAUSE THE VIEW OR TABLE DEFINITION DOES NOT INCLUDE A UNIQUE NAME FOR EACH COLUMN
-154 THE STATEMENT FAILED BECAUSE VIEW OR TABLE DEFINITION IS NOT VALID
-156 THE STATEMENT DOES NOT IDENTIFY A TABLE
-157 ONLY A TABLE NAME CAN BE SPECIFIED IN A FOREIGN KEY CLAUSE. object-name IS NOT THE NAME OF A TABLE.
-158 THE NUMBER OF COLUMNS SPECIFIED FOR THE VIEW IS NOT THE SAME AS THE NUMBER OF COLUMNS SPECIFIED BY THE SELECT CLAUSE, OR THE NUMBER OF COLUMNS SPECIFIED IN THE CORRELATION CLAUSE IN A FROM CLAUSE IS NOT THE SAME AS THE NUMBER OF COLUMNS IN THE CORRESPONDING TABLE, VIEW, TABLE EXPRESSION, OR TABLE FUNCTION
-159 DROP OR COMMENT ON object IDENTIFIES A(N) object-type1 RATHER THAN A(N) object-type2
-160 THE WITH CHECK OPTION CANNOT BE USED FOR THE SPECIFIED VIEW
-161 THE INSERT OR UPDATE IS NOT ALLOWED BECAUSE A RESULTING ROW DOES NOT SATISFY THE VIEW DEFINITION
-164 auth-id1 DOES NOT HAVE THE PRIVILEGE TO CREATE A VIEW WITH QUALIFICATION authorization-ID
-170 THE NUMBER OF ARGUMENTS SPECIFIED FOR function-name IS INVALID
-171 THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT nn OF function-name IS INVALID
-173 UR IS SPECIFIED ON THE WITH CLAUSE BUT THE CURSOR IS NOT READ-ONLY
-180 THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID
-181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE
-182 AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID
-183 AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF DATES
-184 AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE CONTAINS A PARAMETER MARKER
-185 THE LOCAL FORMAT OPTION HAS BEEN USED WITH A DATE OR TIME AND NO LOCAL EXIT HAS BEEN INSTALLED
-186 THE LOCAL DATE LENGTH OR LOCAL TIME LENGTH HAS BEEN INCREASED AND EXECUTING PROGRAM RELIES ON THE OLD LENGTH
-187 A REFERENCE TO A CURRENT DATE/TIME SPECIAL REGISTER IS INVALID BECAUSE THE MVS TOD CLOCK IS BAD OR THE MVS PARMTZ IS OUT OF RANGE
-188 THE STRING REPRESENTATION OF A NAME IS INVALID
-189 CCSID ccsid IS UNKNOWN OR INVALID FOR THE DATA TYPE OR SUBTYPE
-190 ATTRIBUTES OF COLUMN column-name IN TABLE table-name ARE NOT COMPATIBLE WITH THE EXISTING COLUMN
-191 A STRING CANNOT BE USED BECAUSE IT IS INVALID MIXED DATA
-197 QUALIFIED COLUMN NAMES IN ORDER BY CLAUSE NOT PERMITTED WHEN UNION OR UNION ALL SPECIFIED
-198 THE OPERAND OF THE PREPARE OR EXECUTE IMMEDIATE STATEMENT IS BLANK OR EMPTY
-199 Illegal use of the specified keyword.
-203 A REFERENCE TO COLUMN column-name IS AMBIGUOUS
-204 Object not defined to DB2.
-205 Column name not in table.
-206 Column does not exist in any table of the SELECT.
-208 THE ORDER BY CLAUSE IS INVALID BECAUSE COLUMN name IS NOT PART OF THE RESULT TABLE
-212 name IS SPECIFIED MORE THAN ONCE IN THE REFERENCING CLAUSE OF A TRIGGER DEFINITION
-214 AN EXPRESSION IN THE FOLLOWING POSITION, OR STARTING WITH position-or-expression-start IN THE clause-type CLAUSE IS NOT VALID. REASON CODE = reason-code
-216 Not the same number of expressions on both sides of the comparison in a SELECT . 
-219 THE REQUIRED EXPLANATION TABLE table-name DOES NOT EXIST
-220 THE COLUMN column-name IN EXPLANATION TABLE table-name IS NOT DEFINED PROPERLY
-221 “SET OF OPTIONAL COLUMNS” IN EXPLANATION TABLE table-name IS INCOMPLETE. OPTIONAL COLUMN column-name IS MISSING
-222 AN UPDATE OR DELETE OPERATION WAS ATTEMPTED AGAINST A HOLE USING cursor-name
-223 AN UPDATE OR DELETE OPERATION WAS ATTEMPTED AGAINST AN UPDATE HOLE USING cursor-name
-224 FETCH cannot make an INSENSITIVE cursor SENSITIVE.
-225 FETCH STATEMENT FOR cursor-name IS NOT VALID BECAUSE THE CURSOR IS NOT DEFINED AS SCROLL
-228 FOR UPDATE CLAUSE SPECIFIED FOR READ-ONLY CURSOR cursor-name
-229 The locale specified in a SET LOCALE statement was not found.
-240 THE PART CLAUSE OF A LOCK TABLE STATEMENT IS INVALID
-243 SENSITIVE CURSOR cursor-name CANNOT BE DEFINED FOR THE SPECIFIED SELECT STATEMENT
-244 SENSITIVITY sensitivity SPECIFIED ON THE FETCH IS NOT VALID FOR CURSOR cursor-name
-245 THE INVOCATION OF FUNCTION ROUTINE-NAME IS AMBIGUOUS
-250 THE LOCAL LOCATION NAME IS NOT DEFINED WHEN PROCESSING A THREE-PART OBJECT NAME
-251 TOKEN name IS NOT VALID

Positive Values (Warnings)

 +100  Row not found or end of cursor.
 +222  Trying to fetch a row within a DELETE statement.
 +223  Trying to fetch a row within an UPDATE statement.
 +231  FETCH after a BEFORE or AFTER but not on a valid row. 
 +304  Value cannot be assigned to this host variable because it is out of range.
 +802  The null indicator was set to -2 as an arithmetic statement didn't work.

Letter SQL Codes

  If you have a code in the type of SQL-0000##A these are still valid but hard to decipher.
  Use the chart below to map the letter to the last number of the value.   
  This was created when SQL Codes where only able to be unsigned.
  Example   SQL-000010}  => This translates to SQL CODE:  100.
  Example   SQL-000010N  => This translates to SQL CODE: -105.
  Example   SQL-000092P  => This translates to SQL CODE: -927.
  
 
  0   1   2   3   4   5   6   7   8   9  
  }   A   B   C   D   E   F   G   H   I  
------------------------------------------
     -1  -2  -3  -4  -5  -6  -7  -8  -9  
      J   K   L   M   N   O   P   Q   R

External links

• A more extensive list of the codes