Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: db2 stored procedure

    db2 9.7 fp 3a / Win 2008 R2

    I am executing a stored procedure called sp1 (it does delete and update rows ) using DB2 command prompt as :
    db2 call sp1

    then i can see the message SQL20511N There is not enough available space in the DBMS_OUTPUT message buffer.
    But the query was continued and completed successfull and all the outputs (which item deleted and updated) are listed on the screen.

    question 1 - how can I resolve the DBMS_OUTPUT buffer issue
    question 2 - how can I output the messages into the file rather than on screen.
    Please advise me.

    Thanks.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    DBMS_Output.Enable lets you increase the buffer size (from default of 20000 bytes).
    Study the Infocenter for the details.

    To redirect the stored procedure output to a file (if you are calling the procedure from a command line) then you can use redirection.

    Example in db2cmd.exe shell

    db2 "call yourprocedure" > stored_procedure_output.txt

    You can also use CLPPLUS and in there you can use the SPOOL command to redirect output.

    Study the Infocenter for details.

  3. #3
    Join Date
    Nov 2011
    Posts
    87

    Thank You.

    Thanks for your help. I am able to see the details in inforcentre.
    Regards,

Posting Permissions

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