Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2014
    Posts
    4

    Unanswered: Capture Sql error -530 in SP

    Using DB2 V10.
    I have SP with multiple SQL statements.
    Prior and after execution of each SQL statment, within SP, I have a marker, which is passed back to a calling program. This marker tells me what was a last executed SQL statment in a SP.

    Here is my problem.
    I am performing INSERT which results - 530.
    However, control is not passed to the statement right after INSERT.
    Hence I can not check SQLCODE and perform an appropriate/controled action, within SP.

    Control is passed back to a calling program. At that time I can determine that it is -530 and derive name of the index.

    I have a workaround for this problem, which I am not very happy about.
    Just before Insert, I perform a Select on columns, which comprise Index.
    If I have a find (SQLCODE = 0), I know that subsequent Insert will result -530, which will be then bypassed.
    How can capture -530 within SP.
    Last edited by edwardsaver; 08-06-14 at 11:59.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Does your SP contain specific (or generic) exception handlers , and if so, what processing do they perform ?
    If not, then you need to add appropriate exception handler(s)

  3. #3
    Join Date
    Aug 2014
    Posts
    4
    I am using:
    DECLARE NOTFND CONDITION FOR SQLSTATE '02000'; DECLARE DUPTBL CONDITION FOR SQLSTATE '42710';


    I cloud not find any reference to <exception handler> which addresses my problem of -530 (foreign key violation)

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Widen your search...start with the db2 knowledge-centre online for your version of db2.
    The statements to study (and there are examples both online and in your SAMPLES tree on your db2 server) are :
    DECLARE EXIT HANDLER FOR ...
    DECLARE CONTINUE HANDLER FOR ...

    If your sprocs do not contain exception handlers then they are usually unfit for production use, whether you realise this or not...

    Start here : (and study all the related linked pages until you've got the idea...)
    IBM Knowledge Center

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by edwardsaver View Post
    I cloud not find any reference to <exception handler> which addresses my problem of -530 (foreign key violation)
    You should handle SQLSTATE='23503' which corresponds to SQL0530N.
    Regards,
    Mark.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    A CONDITION HANDLER should be set up that traps all fatal errors. The handler should trap the error message and send it back to calling program as one of the SP parms that is used for the SP when errors occur.

    Separate CONDITION HANDLERS should be set for non-fatal errors where you want the SP to continue, such as NO ROWS FOUND on a SELECT, UPDATE, or DELETE (SQLSTATE '02000').
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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