Troubleshooting SQL0803N

Posted by

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:

  1. Alter the data being inserted to not conflict with data that is already in the table
  2. 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.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.