Re: Stored Procedure error trapping advice

Ed Schaefer wrote: > > Hi: > > I'm fairly new at stored procedures and was wondering if someone could > answer some questions. > > I've written a stored procedures which copies and deletes lots of data. > Comprised of a number of "insert into table_name select * from > another_table" type statements. > > 1) Can you use the DBINFO function to trap for an insert of delete > error? > > 2) What's the best way of using the 'on exception' clause to ignore > certain errors as non-fatal? I'm going to let Stephen Wedeneder's direct response stand and just say that due to the difficulties with handling errors in SPL you are probably better off using a programming language like ESQL/C or 4GL for this task. It looks to me like you can probably use my dbcopy.ec in a shell script for most of this and even dbdelete.ec for the rest (I am working on a potentially faster dbdelete.ec now and may submit it soon the original version was too slow to submit). Dbcopy.ec is in the file utils2_ak that I submitted to the IIUG Software Repository. It contains extensive error handling and recovery coding which can certainly be enhanced for special needs. It logs all records in error in LOAD/DBLOAD format for later cleanup and manual reloading and can optionally ignore duplicate target row errors. Art S. Kagel