Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Question Unanswered: Stored procedure trace during execution

    I an executing a 1000 line stored procedure and this procedure is called 2000 times at a time, as it inputs the records from an excel sheet. I could see one or two exceptions raising from the procedure while execution. ( Ex: SQLCODE -304 )

    I need to exactly trace the code during execution and locate where the exception is raising. Is it possible only with the use of coding i.e., in the stored procedure itself?



  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First, sqlcode -304 =
    --------------------------------------
    SQL0304N A value cannot be assigned to a host variable because
    the value is not within the range of the host
    variable's data type.

    Explanation:

    A FETCH, VALUES, or SELECT into a host variable list failed
    because the host variable was not large enough to hold the
    retrieved value.

    The statement cannot be processed. No data was retrieved.

    User Response:

    Verify that table definitions are current and that the host
    variable has the correct data type. For the ranges of SQL data
    types, refer to the SQL Reference .

    Federated system users: for the ranges of data types that are
    returned from a data source, refer to the documentation for that
    data source.

    sqlcode : -304

    sqlstate : 22001, 22003
    ---------------------------------------------------
    This is likely from something like trying to read an INTEGER into a SMALLINT.


    Now for how to find the offending statement.

    You did not say if the SP is SQL or external.

    What DB2 version and OS?

    If it is SQL, you can try something like creating a varchar variable at the top:

    DECLARE SP_Location varchar(50);

    Then before each statement that you suspect place something like:

    SET SP_Location = 'Before First query".

    Then append the SP_Location string to the error message text in you condition handler.

    Andy

  3. #3
    Join Date
    Apr 2007
    Posts
    63

    Question

    Its really useful if debugging is carried out by a programmer.
    If the stored procedure (SQL) is used by an end user, I need to inform them , during which operation, the procedure fails.

    1.Is it the best method to return message to them?

    2.my requirement is to record each and every log of the queries executed by the stored procedure. can i use the same method for this purpose?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    1) getting more information is always better than getting not enough.

    2) depends on your situation.

    Andy

  5. #5
    Join Date
    Apr 2007
    Posts
    63

    Thumbs up

    Ok I solved it. Thank u so much for your time!!

Posting Permissions

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