Trying to avoid -803 in DB2 insert

Question

I'm trying to insert a single row which may or may not already exist. I'd like to avoid selecting it first and/or getting a -803 if it does exist. I've done some research and tried both ignore and the merge statement, but keep getting syntax errors on both. In any case, I'm not trying copy the data from another table - so, merge is not really appropriate.
Isn't there some way in DB2 SQL to just issue a failure-proof insert and not have to code around this? In other words, is there some insert syntax which guarantees the data will be added if it doesn't exist or will return zero status even it it does?

Answer 1

MERGE is appropriate, since you may dynamically supply values in the USING clause (see example 5 in http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm).

But MERGE is a DB2 V8.2 feature! You're probably on V8.1, aren't you?

If you implement the existence check like this:

SELECT
if (found) UPDATE else INSERT

beware of concurrency issues: Two concurrent threads may both find no column and then both try to insert, even if there's a transaction around the above code. To ensure this won't happen, you need to acquire an update lock with Repeatable Read in the above SELECT statement using WITH RR USE AND KEEP UPDATE LOCKS.

Answer 2

In short, the answer is no. In long, it all depends on the constraints you have set up on your table. If you attempt to execute an insert against a table that has a unique constraint on a column, and the data already exists that you are trying to insert, you will get an error in DB2 (and any other RDBMS).

In this case, your best option is probably to write a stored procedure that checks to see if the record already exists before creating it.