I'm trying to create a procedure and encountering all sorts of errors. I initially tried an example from Osborne's Complete DB2 Reference, an then resorted to something even simpler but still no luck.

CREATE PROCEDURE test (
IN x smallint,
OUT y smallint)
DYNAMIC RESULT SETS 0
READS SQL DATA
LANGUAGE SQL
BEGIN
SET y=x+1
END

returns the error
SQL0104N An unexpected token "END" was found following "QL BEGIN SET y=x+1 ". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=8. SQLSTATE=42601

Sounds like it wants a semi-colon, so I add one after x+1 and get two errors:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "SQL BEGIN SET y=x+1". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=8. SQLSTATE=42601

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

Would somebody please shed some light on this for me?


Thanks,
Chris

How you are compiling that procedure.if you put it in an script file,
use @ as a signal to the end of procedure and use ; as the end of statements
with in the procedure, then use

db2 -td@ -f <procedure_file_name>

any time you misses the t option db2 takes ; as the end of statement.

regards,

mujeeb

I'm just using the same plain text query tool I use for my other SQL work. Should I instead be using one of the DB2 tools (Command Center, Control Center)?

You will have to change the Statement Delimiter in your tool to @...

By default, most tools use ;

Cheers
Sathyaram