SQL0803N is an error message that means the SQL statement just issued failed due to violating a unique constraint within the database. The ‘N’ at the end of this error means that this statement failed and was not executed, but that it is probably not a database system issue. See Db2 Basics: Error Messages for more general information on interpreting Db2 error messages.
What This Error Looks Like
As with any error message in Db2, it is critical to get the full error message text to troubleshoot the issue. Here is an example of what that full output should look like:
insert into dbi_repos.MONITORED_DB_PARTS (db_id ,db_part_num ,coll_id ,server_name ,db_login_id ,db_login_crypt_pwd ,is_monitored ,is_catlg_node) VALUES (1 ,0 ,1 ,'example.com' ,'db2inst1' ,'test' ,'N' ,'Y') DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DBI_REPOS.MONITORED_DB_PARTS_1" from having duplicate values for the index key. SQLSTATE=23505
If you’re not issuing the command at a Db2 command line, then this message may be buried in application logs. It may also be reported as a -803 error. Every application that accesses a database should log errors like this in detail – error checking is a critical component of any application. Output from an application may look a bit more like this:
DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=1;DBI_REPOS.MONITORED_DB_PARTS_1
Identifying the Real Problem
In the error messages above, I have highlighted the critical pieces of information in red. Even if you don’t have the full text of the error message in an application log, these two pieces of information should be returned in the reduced output available. These pieces of information tell us in this case that we’re violating index number 1 on the table DBI_REPOS.MONITORED_DB_PARTS_1. Now that I have that information, I can run a simple query to show me which columns have duplicate data when they shouldn’t:
select substr(indname,1,32) as indname , substr(colnames,1,50) as colnames from syscat.indexes where tabschema='DBI_REPOS' and tabname='MONITORED_DB_PARTS_1' and iid=1 with ur INDNAME COLNAMES -------------------------------- -------------------------------------------------- PX_MONDBPARTS +DB_ID+DB_PART_NUM 1 record(s) selected.
The values that must be changed in this query to match the specific error message are in red. Please note that the table schema and name must be in all upper-case or in the specific mixed case used if you are evil enough to force table names into mixed case.
In this case, we can see that the statement was trying to use duplicate values for the combination of DB_ID and DB_PART_NUM. This combination of values already existed in the table. I can verify that using an sql statement that is much more customized to the table. In this case, I would use:
select *
from dbi_repos.MONITORED_DB_PARTS
where DB_ID=1
and DB_PART_NUM=0
with ur
DB_ID DB_PART_NUM COLL_ID SERVER_NAME DB_LOGIN_ID DB_LOGIN_CRYPT_PWD IS_CATLG_NODE IS_MONITORED
----------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------
1 0 21 somesillydbserver.example.com db2inst1 XXXXXXXXXXXXXXXXXXXX Y Y
1 record(s) selected.
The values that I fill in for DB_ID and DB_PART_NUM here are the values from my original insert statement.
Resolving the Error
The options for resolving this issue are:
- Alter the data being inserted to not conflict with data that is already in the table
- Correct the data already in the table to make sure the values do not conflict with what is being inserted
The solution to this error message is nearly always changing the data you’re trying to add to the table in some way.
This is not a database system problem, and generally all a DBA can do is help you with this analysis to figure out what is wrong with the data.
