Some common DB2 / SQL errors with their resolution.

Hi

I am here presenting some common errors, hope they will help you in interviews.

INSTANT EXPERT: SQL ERROR CODES

DB2 UDB Version 8 for z/OS

Introduction

SQL return codes provided by DB2 UDB for OS/390 and z/OS can be confusing and often reference manuals are not available or close at hand when you really need 

them. This Instant Expert Reference Card will review SQL 

return code processing and common SQL error conditions you may encounter in your daily work with DB2.

Retrieving SQL Return Code Information & 

Messages Into Your Programs

• COBOL programs executing SQL statements communicate 

with DB2 via a Working Storage area called the SQL Communications Area (SQLCA).

• When DB2 executes SQL statements, it returns the results of 

the operation into the SQLCODE and SQLSTATE fields in the 

SQLCA. SQLCODE provides key information about the success or failure of SQL statement execution.

SQLCODE Overview

• If SQLCODE = 0, execution was successful.

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

• If SQLCODE < 0, execution was not successful.

• -805 DBRM OR PACKAGE NAME location-name.collectionid.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.

• -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.

• -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.

• -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. 

• -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. 

• -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. 

• -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.

• -927 THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE 

CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE 

PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.

·        -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.

• -150 (DB2 V8) 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.

• -180 THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID. 

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

value type.

• -181 THE STRING REPRESENTATION OF A DATETIME VALUE IS 

NOT A VALID DATETIME VALUE.

Suggestion: Verify data format 

with the SQL Reference Guide.

• -204 name IS AN UNDEFINED NAME.

Suggestion: Correct DB2

CREATOR or OBJECT NAMEs located in SQL statements.

• -227 (DB2 V8) 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.

• -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”

• -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.

• -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.

• -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.

• -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.

• -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.

• -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.

• -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.