You might wonder what there is to write about SQLCODE -803. It just means that you are trying to insert a row already present in the table and being rejected because of a unique index on the table. End of discussion. I have felt that way for many, many years. Recently I was asked if I could find out what the content of the row already there looked like.

In order to do so you have to know the RID (Record IDentifier) of the row and fortunately the RID is returned in the SQLCA area as the last value of the SQLERRMT field. Without the RID the task cannot be solved so the program getting a -803 must save the SQLCA somehow (print it out preferably formatted). Tools like CA Detector, IBM Query Monitor and BMC SQL Analyzer are also able to collect the SQLCA of failing SQL statements so you might find the RID there as well. A well formatted error message may look like this:

SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
BECAUSE INDEX IN INDEX SPACE MYINDEX CONSTRAINS COLUMNS OF THE
TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS.
RID OF EXISTING ROW IS X'0000004208'.

Here you can see that MYINDEX is the unique index preventing the insert. By acessing the DB2 catalogue I can find the table related to MYINDEX and in this example I assume MYINDEX is an index on MYTABLE. Previously I would now (with a lot of difficulty) have been able to locate the row using DB2 offline utility DSN1PRNT, but it is lightyears easier to do it using the SQL function RID:

SELECT * FROM MYTABLE
WHERE RID(MYTABLE) = 16904

Hey where did that value 16904 come from? This is the decimal value of X'0000004208'. Translation can be done easily by using TSO SAY X2D(4208). Unfortunately there is no function or expression in SQL to do the translation. And the RID function returns an integer value. As input parameter to the RID function you must specify the table to which the RID value belongs.

For those of you interested in performance you should know that using the RID function as above will get its own special access path making DB2 able to retrieve the row using a single getpage. And to those of you unaware of the format of a RID the first four bytes is the page number and the last byte is the row number on the page. This limits the number of rows on a single DB2 page to 255. And thus the maximum number of rows in a DB2 table (lifted in DB2 12) is a little more than 1.000 billion rows.

Every single row in a DB2 table has a unique RID value, but the RID value can change for the same row after a REORG. So please do not use the RID function to identify rows. But if you have a table with two or more rows being exactly identical you can use the RID function to get rid(!) of the duplicates. First you use the RID function in the SELECT list to retrieve the RID of the duplicates and then you use it like in the example above to delete the duplicates.