DB2 Error codes

SQLCODE Overview

If SQLCODE = 0, execution was successful.

If SQLCODE > 0, execution was successful with a warning.

If SQLCODE < 0, execution was not successful.

If SQLCODE = 100, "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

New with DB2 V8, when DB2 processes a multiple row FETCH statement, the contents of SQLCODE is set to +100 if the last row in the table has been returned with the set of rows.

SQLCODE - Successful SQL Execution

SQL Return Code +100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE.

Suggestion: If expecting data, verify WHERE clause for accuracy and completeness.

SQL Return Code +117 THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS.

Suggestion: Correct SQL statement to provide only one value for each column in the table.

SQL Return Code +231 CURRENT POSITION OF CURSOR cursor-name IS NOT VALID FOR FETCH OF THE CURRENT ROW.

Suggestion: Be certain to FETCH to position on a row after opening a cursor. If cursor is declared SENSITIVE STATIC SCROLL, the row may be a hole, from which no values can be fetched.

SQL Return Code +304 A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2.

Suggestion: Verify DCLGEN host variable definitions are current with DB2 catalog table/view attributes.

SQL Return Code +347 THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP.

Suggestion: Verify predicate in the SQL WHERE clause of the form "counter_col < constant" or "counter_col < :hostvar".

SQL Return Code +802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number.

Suggestion: Check arithmetic operation for divide by zero or result to exceed size of host variable.

SQLCODE - Unsuccessful SQL Execution

SQL Error Code -117 THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER 

OF SPECIFIED OR IMPLIED COLUMNS.

Suggestion: Provide one value for each column in the table.

SQL Error Code -150 THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED.

Suggestion: Be certain to specify base DB2 table/view names for INSERT statements.

SQL Error Code -180 THE DATE, TIME OR TIMESTAMP VALUE value IS INVALID.

Suggestion: Verify the data value is in the correct range and value type.

SQL Error Code -181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE.

Suggestion: Verify data format with the SQL Reference Guide.

SQL Error Code -204 name IS AN UNDEFINED NAME.

Suggestion: Correct DB2 CREATOR or OBJECT NAMEs located in SQL statements.

SQL Error Code -227 FETCH fetch-orientation IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION (sqlcode,sqlstate).

Suggestion: CLOSE and re-OPEN the cursor; For scrollable use (FIRST, LAST, BEFORE, AFTER, or ABSOLUTE) to establish valid position.

SQL Error Code -305 THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED.

Suggestion: Add null indicator variable to SELECT statement in the format of "column:hostvarind".

SQL Error Code -501 THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN.

Suggestion: Correct logic in application program to OPEN the cursor before the FETCH or CLOSE statement.

SQL Error Code -502 THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN.

Suggestion: Correct logic in application program to CLOSE the CURSOR before the OPEN statement.

SQL Error Code -503 A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR.

Suggestion: Use FOR UPDATE statement in your cursor.

SQL Error Code -530 THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint-name IS INVALID.
Suggestion: Ensure that INSERT row for DB2 PARENT table is completed before INSERT row in CHILD table.

SQL Error Code -532 THE RELATIONSHIP constraint-name RESTRICTS THE DELETION OF ROW WITH RID X'rid-number'.

Suggestion: Change the program to DELETE CHILD table row before DELETE of row on PARENT table.

SQL Error Code -551 auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name.

Suggestion: Contact the support DBA to GRANT the needed privilege.

SQL Error Code -803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS Xrid.

Suggestion: Verify DB2 INDEX and, if needed, change the statement to an UPDATE.

SQL Error Code -805 DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason.

Suggestion: Ensure COLLECTION name is in DB2 PLAN. Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -811 THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE.

Suggestion: -811 is often detected after program check for DB2 data existence. Consider using new DB2 V8 FETCH FIRST ROW ONLY feature instead.

SQL Error Code -818 THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z.
Suggestion: Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: -904 is usually caused because a database utility job has started the desired DB2 object in utility mode. Check DB2 Master Log for more details on the resource name – contact DBA.

SQL Error Code -911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -913 UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -922 AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code.

Suggestion: Connection to DB2 has failed due authority for USER or PLAN. Contact DBA to check DB2 authorizations.

SQL Error Code -927 THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.