On Jun 6, 4:51 pm, Serge Rielau <srie...@ca.ibm.comwrote:
amitabh.me...@gmail.com wrote:
I have around 10 merge stmts stored in a table. In my stored procedure,
i just fetch these stmts and execute them. Is there some mechanism by
which if there is a RI exception, then that particular merge stmt is
left and control continues with other stmts in the list?
Of course. All you need is to catch the RI error (SQLSTATE 23503).
Are all these MERGE statements on different tables? Ideally you would
simply sort them properly.
Or you test the RI condition with the MERGE.
DB2 allows the addition of a predicate to the WHEN [NOT] MATCHED clause.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge
My mistake, i wrote the wrong scenerio... instead of many merge stmt,
there is just one merge stmt.. but many records in a table, out of
which few have RI errors. I want to put all good ones into another
table using this merge stmt. The problem is that when exception occurs
(RI) while merging, none of the record (even good ones) are
transferred. Is there some way that the good records get transferred
inspite of the RI exception gettting thrown?
The for loop I was using is like:
FOR insertSQL AS
(SELECT SQLSTRING, TABLE_NAME FROM my_table)
DO
SET sqlRun = insertSQL.SQLSTRING;
EXECUTE IMMEDIATE sqlRun;
-- COMMIT;
END FOR;
COMMIT;