Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: Need to store SQL error code in debug table

    Hi,
    I have one table having column dbg_lvl and dbg_msg.
    Let say i executed some update sql statement in stored perocedure which has some eror. e.g. it threw the error like

    SQL0150N The target fullselect, view, typed table, materialized query table,
    or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target for which the requested operation is not permitted. SQLSTATE=42807

    Now I want to store 42807 in dbg_lvl column and the message "SQL0150N The target fullselect, view, typed table, materialized query table,
    or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target for which the requested operation is not permitted" in dbg_msg column.
    Is it possible, if yes then how?

    Thanks
    Surjya

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check out the SQLSTATE variable and SQLERRM() function in the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Set up an exit handler for this:

    CREATE PROCEDURE SP_TEST
    (INOUT PARM1 CHAR(1),
    INOUT PARM1 CHAR(1),
    .
    .
    OUT DB2_ERROR varchar(1024))
    .
    .
    .
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    GET DIAGNOSTICS EXCEPTION 1 DB2_ERROR = MESSAGE_TEXT;

    --this one only needed if you want to report warning errors > 0
    DECLARE EXIT HANDLER FOR SQLWARNING
    GET DIAGNOSTICS EXCEPTION 1 DB2_ERROR = MESSAGE_TEXT;
    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
  •