Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Red face Unanswered: Is there a way to get SQL statement in sql exception handler?

    Hi
    I know how to get SQLSTATE,
    SQLCODE

    by

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SELECT SQLSTATE,
    SQLCODE
    INTO p_sqlstate_out,
    p_sqlcode_out
    FROM sysibm.sysdummy1;
    SET v_ret_value = -1;
    END;

    but could anyone tell me how to get the whole sql statement which the exception occurred?

    Many thanks !

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by loppp View Post
    but could anyone tell me how to get the whole sql statement which the exception occurred?
    Hi.

    Unfortunately (what a shame ...), not in DB2 for LUW.
    There is no item like DB2_ROW_NUMBER in the GET DIAGNOSTICS statement in DB2/i or DB2/z where you can get the row number of your SP where you got an error.

    Only since DB2 10.1.2 you have such ability, but it's implemented differently:
    FP2: Error locating in SQLCA expanded
    You are still not able to get this value in some SQL/PL variable of calling SP, for examle, since we don't have an ability to access these SQLCA fields in SQL/PL.
    Regards,
    Mark.

Posting Permissions

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