Tuesday, 3 April 2012
DB2 DBMS_OUTPUT.PUT_LINE issue, output to console is truncated
When the command SET SERVEROUTPUT ON executes, it calls the DBMS_OUTPUT.ENABLE procedure with the default buffer size of 20000 bytes and sets an internal flag in the client application. When this flag is enabled, the client application calls the GET_LINES procedure after executing each SELECT or CALL statement, and redirects the messages from the message buffer to standard output.
So, when you try to DBMS_OUTPUT.PUT_LINE many times in your script, the output gets truncated if it exceeds that buffer size.
To increase the DBMS_OUTPUT buffer size, call DBMS_OUTPUT.ENABLE procedure with a larger buffer size after executing SET SERVER OUTPUT ON, for example: CALL DBMS_OUTPUT.ENABLE( 500000 );
Subscribe to:
Post Comments (Atom)
I have about 6,000,000 records in table and now it fails to print all the records into console. how do I resolve this issue. help me out in this.
ReplyDeleteWhy would you like to print 6M record to the console. Why don't you use a File instead?
DeleteIn case you need it on the console, then you need increase the buffer size as in the last statement of the blog. I am not sure if the max buffer size will work for your dataset or not, you need to try it.