DB2 Version 10.1 for Linux, UNIX, and Windows

SET SERVEROUTPUT command

Specifies whether output from the DBMS_OUTPUT message buffer is redirected to standard output.

Authorization

EXECUTE privilege on the DBMS_OUTPUT module.

Required connection

Database

Command syntax


                     .-OFF-.   
>>-SET SERVEROUTPUT--+-ON--+-----------------------------------><

Command parameters

ON: Specifies that messages in the message buffer are redirected to standard output.
OFF: Specifies that messages in the message buffer are not redirected to standard output.

Examples

To redirect messages in the DBMS_OUTPUT message buffer to standard output, specify SET SERVEROUTPUT ON. In this example, the PUT procedure adds partial lines to the DBMS_OUTPUT message buffer. When proc1 runs, because SET SERVEROUTPUT ON is specified, the text stored in the DBMS_OUTPUT message buffer is displayed.

SET SERVEROUTPUT ON@  

DROP PROCEDURE proc1@  

CREATE PROCEDURE proc1() 
BEGIN
  CALL DBMS_OUTPUT.PUT( 'p1 = ' || p1 );
  CALL DBMS_OUTPUT.PUT( 'p2 = ' || p2 );    
  CALL DBMS_OUTPUT.NEW_LINE;  
END@  

CALL proc1( 10, 'Peter' )@  

SET SERVEROUTPUT OFF@

This example results in the following output:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

DROP PROCEDURE PROC1
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE proc1() 
BEGIN
  CALL DBMS_OUTPUT.PUT( 'p1 = ' || p1 );
  CALL DBMS_OUTPUT.PUT( 'p2 = ' || p2 );    
  CALL DBMS_OUTPUT.NEW_LINE;  
END@  
DB20000I  The SQL command completed successfully.

CALL proc1( 10, 'Peter' )@  

  Return Status = 0

p1 = 10
p2 = Peter

SET SERVEROUTPUT OFF
DB20000I  The SET SERVEROUTPUT command completed successfully.

Usage notes

Messages are added to the DBMS_OUTPUT message buffer by the PUT, PUT_LINE, and NEW_LINE procedures.

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 command line processor (CLP) or command line processor plus (CLPPlus). When this flag is enabled, the application calls the GET_LINES procedure after executing each SELECT or CALL statement, and redirects the messages from the message buffer to standard output. 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( 50000 );

When the command SET SERVEROUTPUT OFF executes: it calls the DBMS_OUTPUT.DISABLE procedure, messages that are in the message buffer are discarded, and calls to PUT, PUT_LINE, and NEW_LINE procedures are ignored. The DBMS_OUTPUT.GET_LINES procedure will not be called after each SELECT or CALL statement.