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?
views:
313answers:
2
+1
A:
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.
AJ
2010-01-13 17:40:11
Better yet: Don't waste time doing existence checking, have the stored procedure do the insert, and just handle the -803 (SQL0803N) error.Even better: Just have the application check the return code from the insert and handle the -803 error.
Ian Bjorhovde
2010-01-14 17:27:48
Thanks for the quick response. I was hoping DB2 had some equivalent to the "ignore" parameter, but no such luck. Mike
Mike GH
2010-01-15 13:44:33
Tried handling the -803 and doing an update, but this leaves a stack trace in the log even when the overall process is successful, which causes others to ask questions. So, I'm changing the code to do an existence check first. Thanks for all the suggestions.
Mike GH
2010-03-18 14:13:11
A:
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.
Ralf Pantförder
2010-05-12 12:25:32