Skip to main content

SQL Error Code Introduction​

SQLCODE is a system-generated error code returned by DB2 (or any relational database management system) when a query encounters an issue. These error codes help users diagnose and understand what went wrong with their SQL statement. A positive SQLCODE usually indicates a successful operation, while negative values signal errors or problems. For example, SQLCODE -904 is a common error indicating that a resource, like a database or table, is unavailable. Understanding the SQLCODE is essential for troubleshooting and resolving issues effectively.

Example of error

---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM CUST;                                                             
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -204, ERROR:  SHRDV06.CUST IS AN UNDEFINED NAME              
DSNT418I SQLSTATE   = 42704 SQLSTATE RETURN CODE                                
DSNT415I SQLERRP    = DSNXOTL SQL PROCEDURE DETECTING ERROR                     
DSNT416I SQLERRD    = -500 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            
DSNT416I SQLERRD    = X'FFFFFE0C'  X'00000000'  X'00000000'  X'FFFFFFFF'        
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

Refer to the details here and get the setup ready before testing the samples.

Below are the list of common SQL Error code

SQLCODE -104​

The SQL query has an unexpected token or incorrect SQL syntax.

Example

SELECT * FORM EMP;
SELECT EMP_ID, NAME

Query contains the illegal symbol “FORM”.

SQLCODE -204​

The specified object (such as a table, view, or index) could not be found in the database.

Example

SELECT * FROM EMP1;

The table EMP1 does not exist in the database.

SQLCODE -206​

The specified column does not exist in the table, or the column name was misspelled. Example

SELECT AGE FROM EMP;

The column AGE does not exist in EMP.

SQLCODE -408​

A mismatch occurred between the expected and provided data types (e.g., trying to insert a character string into a numeric field)

Example

INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY)
VALUES (1, 'John Doe', 'HR', 'abcd123456', 'Fifty Thousand');

The PHONE column must contain only digits, but 'abcd123456' has alphabets. SALARY is an INTEGER, but 'Fifty Thousand' is a string.

SQLCODE -551​

The user does not have permission to perform the specified operation.

SELECT * FROM SHRDV42.CUST;

SQLCODE -530​

A foreign key violation occurred. Entering foreign key that does not exist in parent table

INSERT INTO MENTOR (EMP_ID, SKILLS)
VALUES (2001, 'AWS');

SQLCODE -531​

A foreign key violation occurred. The operation violates referential integrity, typically when attempting to delete or update a parent row that is referenced by a child.

UPDATE EMP SET EMP_ID = 2001 WHERE EMP_ID = 1005;

SQLCODE -803​

An attempt was made to insert a duplicate value in a column that has a unique constraint, like a primary key or unique index.

Example:

INSERT INTO EMP (EMP_ID, NAME, DEPARTMENT, PHONE, SALARY)
VALUES (1001, 'Alice', 'IT', '9876543210', 60000);

If EMP_ID = 1001 already exists, this violates the primary key constraint.

SQLCODE -805​

The SQLCODE -805 error in DB2 occurs when there is an issue with the DBRM (Database Request Module) or Package during program execution. TBA

SQLCODE -811​

The SQLCODE -811 error in DB2 occurs when a SELECT statement returns more than one row, but the query is expecting only a single row. TBA