--- title: "DB2 SQL Error Codes You Always Need" description: "DB2 SQL error codes given for interviews as well as to use in your development or production support project." url: https://srinimf.com/2010/10/23/sql_error_codes/ date: 2010-10-23 modified: 2026-10-10 author: "Srini" image: https://i0.wp.com/srinimf.com/wp-content/uploads/2010/10/pexels-photo-5028854.jpeg?fit=1880%2C1253&ssl=1 categories: ["SQL"] tags: ["DB2 SQL Error Codes", "Top SQL errors in DB2"] type: post lang: en --- # DB2 SQL Error Codes You Always Need You always need these SQL errors for your DB2 project. The list given with [SQL error](https://srinimf.com/2016/10/30/the-best-way-to-resolve-sql-error-305-simplified/) and explanation and reasons for error. You an read these before your interview also. **Four basic points on SQL errors in DB2:** - 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 posi­tioned after the last row of the result table. (DB2 V8) New with Version 8, 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. ### DB2 SQL error codes for successful query execution • **+100 **ROW NOT FOUND FOR FETCH, [UPDATE OR DELETE](https://srinimf.com/2014/05/08/db2-zos-common-error-codes/), OR THE RESULT OF A QUERY IS AN EMPTY TABLE. Suggestion: If expecting data, verify WHERE clause for accuracy and com­pleteness. • **+117 **THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS. Suggestion: Correct [SQL statement](https://srinimf.com/2022/01/23/two-types-of-plsql-cursors-what-are-those/) to provide only one value for each column in the table. • **+231 **(DB2 V8) 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. • **+304 **A VALUE WITH DATA TYPE data-type1 CANNOT BE AS­SIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION posi­tion-number WITH DATA TYPE data-type2. Suggestion: Verify DCLGEN host variable definitions are current with DB2 catalog table/view attributes. • **+347 **(DB2 V8) 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. • **+802 **EXCEPTION ERROR exception-type HAS OCCURRED DUR­ING 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. ### DB2 SQL error codes for unsuccessful query execution **-104 **  [ILLEGAL SYMBOL](https://srinimf.com/2011/12/18/db2-illegal/) “token“. SOME [SYMBOLS THAT MIGHT](https://srinimf.com/2015/01/28/db2-sql-error-codes-104-and-199/) BE LEGAL ARE: token-list - **-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 REQUEST­ED 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](https://srinimf.com/2015/09/09/sql-code-180-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](https://srinimf.com/2015/01/11/top-db2-performance-tuning-interview-questions/) variable to [SELECT statement](https://srinimf.com/2014/07/09/4-traits-in-db2-cursor-with-hold/) in the format of “column:hostvarind” Related[SQL Queries: Insert, Update, Delete, and Merge](https://srinimf.com/2021/08/14/here-are-4-sql-queries-useful-to-alter-table-data/) Related[DB2 – Fetch 1 Row Vs Optimize 1 Row Top Differences](https://srinimf.com/2014/05/02/db2-fetch-queryno-optimize/) [https://srinimf.com/2015/08/28/db2-error-codes-](https://srinimf.com/2015/08/28/db2-error-codes-)[204](https://srinimf.com/2023/07/03/what-is-the-204-error-in-db2-how-to-resolve-it/)-205-206/ Related[DB2-Timestamp Mismatch -818 SQL Error](https://srinimf.com/2015/01/22/db2-timestamp-mismatch-818-sql-error/) Related[Db2- SQL Error -805 Resolution](https://srinimf.com/2013/07/29/db2-sql-error-805-resolution/) • **-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 AL­READY 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 IDEN­TIFIED 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 DELE­TION 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 DU­PLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS Xrid. Suggestion: Verify DB2 INDEX and, if needed, change the statement to an UPDATE. **-804** AN ERROR WAS FOUND IN THE APPLICATION PROGRAM INPUT PARAMETERS FOR THE SQL STATEMENT. REASON reason **-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. • **-811 **THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATE­MENT 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](https://srinimf.com/2014/12/11/db2-load-error-904-and-resolution/) BY AN UNAVAIL­ABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name. Sugges­tion: -904 is usually caused because a database utility job has started the desired DB2 object in utility mode. Check DB2 Mas­ter Log for more details on the resource name – contact DBA. • **-911 **THE CURRENT [UNIT OF WORK](https://srinimf.com/2011/09/18/db2-rollback-cursors/) 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 COM­MITs 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. Sug­gestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program hold­ing 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. ***References*** ***[Ref 1 | ](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.codes%2Fsrc%2Ftpc%2Fdb2z_n.htm)******[Ref-2 ](http://www.caliberdt.com/tips/sqlcode.htm)*****[| ](http://www.caliberdt.com/tips/sqlcode.htm)**[***Ref3***](http://ssds.usf.edu/docs/dsnmch11.pdf) ### Share this: - - [Tweet](https://twitter.com/share) - [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2010%2F10%2F23%2Fsql_error_codes%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2010%2F10%2Fpexels-photo-5028854.jpeg%3Ffit%3D1200%252C800%26ssl%3D1&description=DB2%20SQL%20Error%20Codes%20You%20Always%20Need) - - [Share on Tumblr](https://www.tumblr.com/share) - [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2010/10/23/sql_error_codes/?share=jetpack-whatsapp) - [More](#) - - [Print (Opens in new window) Print](https://srinimf.com/2010/10/23/sql_error_codes/?share=print) - [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2010/10/23/sql_error_codes/?share=reddit) - [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2010/10/23/sql_error_codes/?share=telegram) - [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20DB2%20SQL%20Error%20Codes%20You%20Always%20Need&body=https%3A%2F%2Fsrinimf.com%2F2010%2F10%2F23%2Fsql_error_codes%2F&share=email) - ### Like this: Like Loading…