Can anyone please give me any error handling examples for DB2 stored procedures.
I have the following problem:
I have a table "target" with 5 millions records. I have a weekly load to the "target" table from a table called "source". The business rule is not to load records with a shipment Id that already exists in the "target" table. To accomplish this I originally was planning on using a "select NOT IN" sql statement, but due to the volume of records in the source and target table I decided to look at other options.
As a possible solution, I considered to store the shipment Id as the primary key so it wouldn't allow to load duplicate shipments. In doing my problem is that the stored proc exits out when ever the cursor runs into a record that is a duplicate.
Can anyone show me an example of error handling that would write the duplicate record to a log, but would continue to process the rest of the records from the source.
You did not tell if you use SQL Stored Procedures or not, but here is a way to do it using SQL.
In your procedure add a exception handler with CONTINUE option:
DECLARE nError INT;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET nError = SQLCODE;
After inserting a record check if nError is a 0 (no error), a primary key violation error (look up code from message documentation) or something else. If the error code is something else, stop your processing and inform calling process that something happened.
I have not found many examples but, I have been uisng the following error handling and it works well.
Note when you first right the stored proc you need to determine what type of error handling you want, i.e. primary key violation, not found, etc... then look up the sqlstate for that error. Then declare the sqlstate and a variable that will be set when the program encounters this error. You can also declare the sqlstate to continue with the program, rollback etc...
attached is an example, I hope this helps. I will post more when I get better examples.