4

Is there( db2 database) any equivalent of DBMS_OUTPUT in Oracle or PRINT in SQL Server for DB2 or do I need to jump over my head again for this sort of thing?

please provide some code also.

raj
  • 51
  • 2
  • 5

1 Answers1

3

IBM's DB2 version 9.7 or later actually comes with a DBMS_OUTPUT module which appears to be able to do the same thing as Oracle's version. From the DB2 documentation, DBMS_OUTPUT can be called from within a stored procedure, e.g.

SET SERVEROUTPUT ON@

CREATE PROCEDURE proc1()
BEGIN
    CALL DBMS_OUTPUT.PUT( 'H' );
    CALL DBMS_OUTPUT.PUT( 'e' );
    CALL DBMS_OUTPUT.PUT( 'l' );
    CALL DBMS_OUTPUT.PUT( 'l' );
    CALL DBMS_OUTPUT.PUT( 'o' );
    CALL DBMS_OUTPUT.PUT( '.' );
    CALL DBMS_OUTPUT.NEW_LINE;
  END@
CALL proc1@

This would output Hello. to the console.

Note that you also need to make sure that the output from DBMS_OUTPUT is being redirected to the standard output:

The procedures in this module allow you to work with the message buffer. Use the command line processor (CLP) command SET SERVEROUTPUT ON to redirect the output to standard output.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i want to use print statement in store procedure just like oracle sapport dbms_output.put_line('Hello Reader!'); when procedure run than Hello Reader! is print. – raj Apr 05 '17 at 05:57
  • @raj Doesn't the code sample I've given you above show how that is possible? – Tim Biegeleisen Apr 05 '17 at 05:58
  • i am using CALL DBMS_OUTPUT.PUT( 'H' ); this statement in own procedure but in result H is not print. – raj Apr 05 '17 at 06:00
  • What version of DB2 are you using? – Tim Biegeleisen Apr 05 '17 at 06:00
  • Database Connection Information Database server = DB2/LINUXX8664 10.5.6 SQL authorization ID = DB2INST1 Local database alias = TNBDR – raj Apr 05 '17 at 06:08
  • 1
    @raj Try using `SET SERVEROUTPUT ON` to make sure your print statements are going to the place you expect them to be. – Tim Biegeleisen Apr 05 '17 at 06:10
  • db2inst1@ITLDEVTNSRDHDB:~> db2 SET SERVEROUTPUT ON DB20000I The SET SERVEROUTPUT command completed successfully. this is done but in output window mohit is not print – raj Apr 05 '17 at 06:14
  • @raj I'm out of ideas then. I'll leave my answer here in case it helps someone else get your problem solved. Here, have an upvote +1 – Tim Biegeleisen Apr 05 '17 at 06:15
  • Your procedure works just fine. If you're not seeing the output, please update your question with the exact command you're executing and all output generated by that command. – Ian Bjorhovde Apr 05 '17 at 22:17