Interpret Ibm Db2 Sql Error Messages
Contents |
the error code from the db2 command prompt with "? sqlxxxx"
Db2 Sqlstate
where xxxx is the SQLCODE from the error message: [db2inst1@matilda db2 sql tutorial db2inst1]$ db2 ? sql-803 SQL0803N One or more values in the INSERT statement, UPDATE statement, jcl abend codes or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "
feature of DB2 programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field https://en.wikipedia.org/wiki/DB2_SQL_return_codes SQLCODE within the DB2 SQLCA block. SQLCODE is no longer part https://docs.oracle.com/database/121/DRDAG/errors.htm of the SQL-standard. The SQL-standard replaced SQLCODE by the more detailed SQLSTATE. Contents 1 SQLCA 2 SQLCODE 2.1 Zero (Successful) 2.2 Negative values (Errors) 2.3 Positive Values (Warnings) 3 References SQLCA[edit] The SQL communications area (SQLCA) structure is used within the DB2 program to return error information to db2 sql the application program. This information in the SQLCA and the SQLCODE field is updated after every API call for the SQL statement... SQLCA contains ERROR HANDLING data SQLCODE[edit] The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive. 0 means successful execution. Negative means unsuccessful with an error. An example is -911 which db2 sql error means a timeout has occurred with a rollback. Positive means successful execution with a warning. An example is +100 which means no rows found or end of table Here is a more comprehensive list of the SQLCODEs for DB2: Zero (Successful)[edit] 0 Successful Negative values (Errors)[edit] -007 The specified 'character' is not a valid character in SQL statements. -010 THE string constant beginning with string is not terminated properly. -029 INTO Clause required. -060 INVALID type SPECIFICATION: spec -084 Unacceptable SQL statement. -101 The statement is too long or too complex. -102 String constant is too long. -104 Illegal symbol encountered in the SQL statement. -117 The number of values in the INSERT does not match the number of columns. -122 Column or Expression in the Select List is not valid -180 Bad data in Date/Time/Timestamp. -181 Bad data in Date/Time/Timestamp. -188 The host variable in a DESCRIBE statement is not a valid string representation of a name. -199 Illegal use of the specified keyword. -204 Object not defined to DB2. -205 Column name not in table.
Error Messages Mapped Errors SQL Tracing and the Gateway Interpreting Gateway Error Messages The gateway architecture consists of different components. Any component may detect and report an error condition while processing SQL statements that refer to one or more DRDA database tables. This means that errors can be complex, involving error codes and supporting data from multiple components. In all cases, however, the application ultimately receives a single error code or a return code. As most gateway messages exceed the 70character message area in the Oracle SQL Communications Area (SQLCA), the programmatic interfaces and Oracle Call Interfaces, that you use to access data through the gateway should use SQLGLM or OERHMS to view the entire text of messages. Refer to the programmer's guide to the Oracle precompilers for additional information about SQLGLM, and refer to the Oracle C++ Call Interface Programmer's Guide for additional information about OERHMS. Errors encountered when using the gateway can originate from many sources, as follows: Errors detected by the Oracle database Errors detected by the gateway Errors detected in the DRDA software, either on the client or server side Communication errors Errors detected by the server database Errors Detected by the Gateway Errors detected by the Oracle database are reported back to the application or tool with the standard ORA type message. Refer to Oracle Database Error Messages for descriptions of these errors. For example, the following error occurs when an undefined database link name is specified: ORA-02019: connection description for remote database not found Errors in the ORA-9100 to ORA-9199 range are reserved for the generic gateway layer (components of the gateway that are not specific to DRDA). Messages in this range are documented in Oracle Database Error Messages. Errors Detected in the DRDA Software Errors detected in the DRDA gateway, on the client or server side, are usually reported with error ORA-28500, followed by a gateway-specific expanded error message. There are two return codes reported in the expanded message: drc specifies DRDA-specific errors. grc specifies generic gate