I am using DB2 Express Edition V9, trying to run a very simple stored procedure as shown below. I am executing it from the Control Center. The Statement Termination Character was ; , even tried without it but still get the same error message. What am I missing here? Thanks in advance.

create procedure my_proc1 ()
LANGUAGE SQL
BEGIN
INSERT into t1 values (1, 'FIRST');
END

I get the following error:

------------------------------ Commands Entered ------------------------------
create procedure my_proc1 ()
LANGUAGE SQL
BEGIN
INSERT into t1 values (1, 'FIRST');
END;
----------------------------------------------------------------
create procedure my_proc1 ()
LANGUAGE SQL
BEGIN
INSERT into t1 values (1, 'FIRST')
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 "values
(1, 'FIRST')". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

END
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 "END".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601

Change your Statement Termination character to something other than the semicolon. Most people use the @.

Andy

Thanks Andy. I changed the Statement Termination Character to be '@' and that seems to have solved the problem other than the fact that I don't see the insert happening in the table

------------------------------ Commands Entered ------------------------------
create procedure my_proc1 ()
LANGUAGE SQL
BEGIN
INSERT into T1 values (10, 'ABCDE');
END@
------------------------------------------------------------------------------
create procedure my_proc1 ()
LANGUAGE SQL
BEGIN
INSERT into T1 values (10, 'ABCDE');
END
DB20000I The SQL command completed successfully.

Now when I do a select * from T1 - it returns nothing

Is this something to do with the '@' as statement termination character? I tried putting '@' at the end of the insert statement - no luck. Or it is something else like Auto Commit?

All you did was create the stored procedure. All this does is define the process in the database. If you want the stored procedure to actually be run, you have to CALL it.

Andy

ofcourse! can't believe I asked that dumb question. Thanks Andy.