CODEWITHSUNDEEP
shelldb2procedure
Goldray
Goldray

Reputation: 161

Create a procedure in DB2

I created a simple procedure using DB2 in .sql file and executed the script with Shell (Unix). I got an error : SQLSTATE 42601.

DB20000I  The SQL command completed successfully.
CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
update EMP set ENAME='ALLAIN' where EMPNO=7789
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "IN'
where EMPNO=7789".  Expected tokens may include:  "<psm_semicolon>".  LINE
NUMBER=3.  SQLSTATE=42601

There is the procedure :

CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
update EMP set ENAME='ALLAIN' where EMPNO=7789;
END
/

Upvotes: 0

Views: 652

Answers (2)

Goldray
Goldray

Reputation: 161

I added '--#SET TERMINATOR /' before the procedure creation and the problem is solved.

--#SET TERMINATOR /
CREATE OR REPLACE PROCEDURE PROC_SAMPLE1()
BEGIN
update EMP set ENAME='ALLAIN' where EMPNO=7789;
END
/

Upvotes: 0

Hogan
Hogan

Reputation: 70538

If this is contained in a file you need to change the batch terminator. Since the SP lines end with ; the batch ends with /, you need to specify this on the command line.

eg 

db2 -td/ -f <your file>.sql

I like to use # since it does not mess with unix shells like / will. Here replace your last character with # in the file and use -td#

Upvotes: 2

Related Questions