DB2 SQL Error SQLCODE 803 SQLSTATE 23505

DB2 SQL Error SQLCODE 803 SQLSTATE 23505 means that an Insert or Update operation is violating a uniqueness constraint that is already in place. It occurs either after performing Load with the identity_override modifier or when the table gets altered to add an Identity clause with existing data.

What Does SQLCODE = – 803 Mean?

SQLCODE = -803 refers to the DB2 error where DB2 fails to generate unique values as default values on a given column. Generally, you get this error code if you’re trying to insert DB2 generated values after enforcing uniqueness through a defined index.

The reason behind this error is pretty straightforward. Since you have tried to enforce uniqueness through the unique indexing, if the new value matches a prior one, error 803 will occur. And you cannot expect DB2 to generate an alternate value that is always unique.

What Is Error Code 23505 in SQL State?

The error code 23505 in SQL state is typically associated with the SQLCODE 803. And there can be different instances when you get the DB2 error SQLCODE=-803, SQLSTATE=23505. Often, the error will occur either during di-preprocess or on table with an identity column.

How to Resolve DB2 SQL Error SQLCODE 803?

You can try to resolve the issue depending on the scenario you’re in.

DB2 Error SQLCODE=-803, SQLSTATE=23505 During Di-Preprocess

You would normally get this error on WebSphere Commerce V7.0 Feature Pack 2 or a later feature pack while trying and failing to run di-preprocess against the master catalog. It is related to the new search tool introduced from Feature Pack 2 and newer Feature Packs.

Here’s how the error message may look like.

  DB2 SQL Error: SQLCODE=-803 SQLSTATE=23505 SQLERRMC=1;DB2INST1.TI_APGROUP_0 DRIVER=4.12.55

If we break this down, it means the insertion of a new record failed because of violating a primary key constraint. This happens when there’s an existing record with the same CATENTRY_ID value of the new one. And the cause might be either of the following two.

1. Multiple parent categories of the same catalog entry

Use the query below to check if a catalog entry has more than one parent category. The result will show a list of all the category entries and their respective parent categories in the master catalog.

SELECT catentry_id catgroup_id FROM catgpenrel WHERE catentry_id IN (SELECT catentry_id FROM catgpenrel WHERE catalog_id = <catalog_id> GROUP BY catentry_id HAVING COUNT(catentry_id) > 1)

2. Multiple parent categories of one category

The following SQL query should let you know if there are more than one parent for each category.

SELECT catgroup_id_parent catgroup_id_child FROM catgrprel WHERE catalog_id = <catalogId> AND catgroup_id_child IN (SELECT catgroup_id_child FROM catgrprel WHERE catalog_id = <catalogId> GROUP BY catgroup_id_child HAVING COUNT(catgroup_id_child) > 1)

Solution

Once you identify the reason, make appropriate changes to make sure each child category or entry has only one parent. Here’s how.

1. Unload the data from the table.
2. Drop the table and recreate it where you set the Identity column to have a starting value higher than the current max. Recreate the unique index.
3. Reload the data into the table.

If you’re using Version 8 or above, you can achieve the same using the ALTER TABLE command and set a RESTART WITH value for the IDENTITY column.

DB2 Error SQLCODE=-803, SQLSTATE=23505 on Table With Identity Column

As stated before, the SQLCODE = -803 error in DB2 LUW occurs when you try to insert or update an entry but violate the unique constraint. The Identity column generates a value that is already there in the table. This may occur when DB2 performs new inserts after load.

Solution

Restart the internal sequence of the IDENTITY column with a value higher than the summation of maximums and CACHE value of IDENTITY column.

Here’s an example with EMP table.

1. Create Table

  CREATE TABLE "DB2INST2"."EMP"(“Your initial commands and constraints go here);

2. Check max value for IDENTITY column

  db2 "SELECT MAX(ID) FROM EMP"
  Let’s assume it returns 125357

3. Calculate restart value for internal sequence. Here, max_value = 125357 and cache_value = 20. Hence, use restart value as 125380.

  ALTER TABLE EMP ALTER COLUMN ID RESTART WITH 125380

Restart the internal sequence and the insertion will be complete.

Frequently Asked Questions

What is error code SQL0803N in DB2?

The error code SQL0803N in DB2 is the same as SQLCODE=-803 SQLSTATE=23505. You might see it in the application error log when running an insert from CLI.

End Statement

Getting the Error SQLCODE 803 SQLSTATE 23505 might seem daunting at first. But you can find your way around once you understand the reasoning. And if you’ve got the latest version of DB2, it becomes easier. Hope you found this article useful.