DBMS_OUTPUT

Toad for IBM DB2
1

I am writing debug statements but don’t know where the output is going. This is the statement: CALL DBMS_OUTPUT.PUT(‘v_part=’ || v_part);

I enable using: CALL DBMS_OUTPUT.ENABLE (100000);

Where are the debug statements?

2

Thanks for the feedback. Those debug statements are not being displayed.
I have opened CR 90710 to track this issue.

3

This seems to be an IBM limitation related to .Net:

To see an output you need to call SET SERVEROUTPUT ON
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0053562.html

But command fails using .Net provider
ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token “SERVEROUTPUT” was found following "SET ". Expected tokens may include: “SSA”.

P.S.
“DBMS_OUTPUT.ENABLE” is part of “SET SERVEROUTPUT ON”

4

I’ve discussed it with IBM:
They don’t have an interface to return such output natively for .Net provider.

I can suggest following workaround:
=== Create function that returns output

CREATE FUNCTION “DB2ADMIN”.“WRAPPER_DBMS_OUTPUT” ( )
RETURNS VARCHAR(32000)
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE l_lines DBMS_OUTPUT.CHARARR;
DECLARE l_numlines INTEGER DEFAULT 10000;
DECLARE l_result VARCHAR(32000) DEFAULT ‘’;
DECLARE i INTEGER;

 --now extract the data
 CALL DBMS_OUTPUT.GET_LINES( l_lines, l_numlines );
 SET i = 1;
 WHILE i <= l_numlines DO
    SET l_result = l_result || l_lines[i] || chr(13) || chr(10);
    SET i = i + 1;
 END WHILE;
 return l_result;

END;

== Enable possibility to return output
call dbms_output.enable();
=== Call your procedures
call dbms_output.put_line(‘hello’);
call dbms_output.put_line(‘world’);
=== Select info from output
select DB2ADMIN.WRAPPER_DBMS_OUTPUT() a from sysibm.sysdummy1;