Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    34

    Unanswered: db2 error handling - stored procs examples

    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.

    Thanks for any help,

    BK

  2. #2
    Join Date
    Jan 2002
    Posts
    8
    Iam looking for a similar solution. Were you able to get an answer for this. If yes, could you please post the example.
    Thanks
    Nilima

  3. #3
    Join Date
    Apr 2002
    Posts
    4
    Still haven't found a solution, will post one if I ever find it

  4. #4
    Join Date
    Nov 2001
    Location
    Finland, Kirkkonummi
    Posts
    17
    Hello,

    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.

    Hopefully this helps,

    Vesku

  5. #5
    Join Date
    Mar 2002
    Posts
    34

    sql stored proc error handling examples

    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.
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2002
    Posts
    2

    External Stored Procedure - Error Handling problem

    Hi
    I am trying to find out how to do error handling for External Stored Procedures (specifically Stored Procedures (SP) programmed in C/C++).

    I would like an example(s) if possible. (Especially how someone will
    call the SP from the client side and access the error message)

    Background info:

    I currently have a SP that takes one input parameter and the
    Language is C and the Parameter type is DB2SQL (no DBINFO).

    CREATE SP_NAME (IN param INTEGER)
    Language C
    PARAMETER TYPE DB2SQL
    NO DBINFO
    PROGRAM TYPE SUB
    (etc)

    Stored Procedure implementation

    extern "C" <return_type> SP_NAME (sqlint32 * param)
    {
    // code
    }

    Help would be appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •