CODEWITHSUNDEEP
consoledb2
Koushik Chandra
Koushik Chandra

Reputation: 1491

Print error message in console from DB2 procedure

Is there any other simple way to print message in console from DB2 procedure without setting DB2_COMPATIBILITY_VECTOR=ORA in registry?

Upvotes: 1

Views: 15331

Answers (3)

Koushik Chandra
Koushik Chandra

Reputation: 1491

I am able to achieve what I intended using the below steps:

[user@boxname]/home/user>db2 "set serveroutput on"
DB20000I  The SET SERVEROUTPUT command completed successfully.
[user@boxname]/home/user>cat > copy_table
CREATE OR REPLACE PROCEDURE COPY_TABLE(
  IN SRC_SCMA VARCHAR(30),
  IN TGT_SCMA VARCHAR(30),
  IN SRC_TBL VARCHAR(50),
  IN TGT_TBL VARCHAR(50)
)
LANGUAGE SQL
CALLED ON NULL INPUT

BEGIN

DECLARE a INT;
DECLARE b INT;
DECLARE c INT;
DECLARE d INT;

SELECT COUNT(1) INTO a FROM SYSCAT.SCHEMATA WHERE SCHEMANAME = UCASE(''||SRC_SCMA||'');

IF a <= 0 THEN
    CALL DBMS_OUTPUT.PUT_LINE('Invalid Source Schema Specified');
        GOTO LAST_BLOCK;
END IF;

SELECT COUNT(1) INTO b FROM SYSCAT.SCHEMATA WHERE SCHEMANAME = UCASE(''||TGT_SCMA||'');

IF b <= 0 THEN
    CALL DBMS_OUTPUT.PUT_LINE('Invalid Target Schema Specified');
        GOTO LAST_BLOCK;
END IF;

SELECT COUNT(1) INTO c FROM SYSCAT.TABLES WHERE TABSCHEMA = UCASE(''||SRC_SCMA||'') AND TABNAME=UCASE(''||SRC_TBL||'');

IF c <= 0 THEN
    CALL DBMS_OUTPUT.PUT_LINE('Invalid Target Schema Specified');
        GOTO LAST_BLOCK;
END IF;

SELECT COUNT(1) INTO d FROM SYSCAT.TABLES WHERE TABSCHEMA = UCASE(''||TGT_SCMA||'') AND TABNAME=UCASE(''||TGT_TBL||'');

IF d > 0
THEN
        EXECUTE IMMEDIATE 'DROP TABLE '||TGT_SCMA||'.'||TGT_TBL;
        EXECUTE IMMEDIATE 'CREATE HADOOP TABLE '||TGT_SCMA||'.'||TGT_TBL||' AS SELECT * FROM '||SRC_SCMA||'.'||SRC_TBL;
        CALL DBMS_OUTPUT.PUT_LINE('Invalid Source Table OR Source Schema & Table Combition Is Wrong');
ELSE
        EXECUTE IMMEDIATE 'CREATE HADOOP TABLE '||TGT_SCMA||'.'||TGT_TBL||' AS SELECT * FROM '||SRC_SCMA||'.'||SRC_TBL;
        CALL DBMS_OUTPUT.PUT_LINE('Target Table Created & Loaded');
END IF;

 LAST_BLOCK:
        RETURN;

END@
[user@boxname]/home/user>db2 -td@ -f copy_table
DB20000I  The SQL command completed successfully.

[user@boxname]/home/user>db2 "CALL COPY_TABLE('invalid_user','valid_user','TEST_TABLE','TEST_TABLE')"

  Return Status = 0

Invalid Source Schema Specified

Upvotes: 1

AngocA
AngocA

Reputation: 7693

When you use the console the send messages, you have to wait to the end of the execution. If you generate a message in a batch process, it could take a while to show the message.

If your execution is just one instruction and one error, you can 'raise a signal'. In this way, the error message could be retrieved from any application that access DB2.

Instead of printing the output in the console, which IMO is not a very good practice (only console is able to show the server output), you can write a message in a log. You can write the output in another table, or use a framework like log4db2.

By using a log mechanism like log4db2, if you execute this statement as part of a batch process, you can get the message just by querying the log table at any time, and you do not have to wait to the end of the execution.

Upvotes: 0

data_henrik
data_henrik

Reputation: 17118

Because the built-in module dbms_output allows printing to the console AND it can be used without setting the compatibility vector, you shoul be able to print the messages. I just tested this without a procedure on a V10.5 database:

[hloeser@mymachine]$ db2 "set serveroutput on"
DB20000I  The SET SERVEROUTPUT command completed successfully.
[hloeser@mymachine]$ db2 "call dbms_output.put('Hello')"

  Return Status = 0
[hloeser@mymachine]$ db2 "call dbms_output.new_line"

  Return Status = 0

Hello

It is important to enable serveroutput.

Upvotes: 0

Related Questions