1

I'm having an issue with a stored procedure in DB2 LUW 10.5 using Data Studio 4.1.3 when trying to call more then one statement at a time. I've narrowed the problem down to the way semi-colon's (;) are being treated. For easier trouble-shooting I created a really simple example of what I am trying to do.

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";
    insert into "DB2ADMIN"."TestTable"
    values('a','a','a');
    insert into "DB2ADMIN"."TestTable"
    values('b','b','b');
    select * from "DB2ADMIN"."TestTable";

If I run the code above it completes fine but when I look at the sp I only see:

--<ScriptOptions statementTerminator=";"/>

CREATE OR REPLACE PROCEDURE DB2ADMIN.sp_Test ()
    delete from "DB2ADMIN"."TestTable";

It only reads to the first semi-colon and then stops. If I remove all semi-colons but the last one the procedure will not get created. My code in real life is much longer but is behaving the same. Any help or ideas would be greatly appreciated.

  • Did you have a chance to look at the procedure samples in the manual? Procedure body is a compound statement: BEGIN ... END, not just a pile of DML statements. This is in addition to your delimiter problem mentioned by others. – mustaccio Aug 24 '18 at 18:16
0

Inside a stored procedure body you have to use a semi-colon to delimit statements. But semi-colons are also the default for delimiting statements in most clients so you end up needing to change your client's delimiter to something else (I use @). You end up with something like:

create procedure cleanup()
begin
    delete from mytable;
    delete from myothertable;
end @
  • 2
    That did the trick. I added "--SET TERMINATOR @" after the create or replace line and then set it back to the semi-colon after the end@ line. Thanks! – Chris Aug 24 '18 at 18:23
  • In DataStudio you can also right click on you sql editor window and choose "Set Statement Terminator". You can do the same on the "Validation" tab at the bottom of each SQL editor view. You can also change the default in Preferences - Data Management - SQL Development - SQL and XQuery Editor – Paul Vernon Aug 24 '18 at 18:39
  • I noticed the "Set Statement Terminator" option but thought that it would be best to put it in the code. That way it doesn't matter what the Terminator is set to the code should still run OK.....I hope. – Chris Aug 24 '18 at 18:57
  • It's not the delimiter per say, but the lack of BEGIN END in the OP's post. Without BEGINing a compound statement, the DB only sees the first statement. – Charles Aug 24 '18 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.