this the scenario... at the end i have posted the questions
-------------------------------------------------------------------------
i have been trying to create stored procedures in the CONTROL CENTER, after refering to many books i tried this one :


create procedure employee_vote55(in employee_id char(6), out current_votes integer)
specific emp_vote_by_id55
dynamic result sets 0
modifies sql data
not deterministic
language sql
ivote: begin
insert into emp_votes values (employee_id, current date);
select count(*) into current_votes from emp_votes where empno ='1';
end ivote

----------------------------------------------------------------------
but it gives a error:
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 "ee_id,
current date)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=8. SQLSTATE=42601

select count(*) into current_votes from emp_votes where empno ='1'
SQL0206N "CURRENT_VOTES" is not valid in the context where it is used.
SQLSTATE=42703

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

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end ivote". 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
-----------------------------------------------------------------------

but if i write the SQL commands in one line, then it doesnt show any error and procedure gets created.
i.e
-----------------------------------------------------------------------

create procedure employee_vote121(in employee_id char(6), out current_votes integer)
specific emp_vote_by_id121
dynamic result sets 0
modifies sql data
not deterministic
language sql
ivote: begin
insert into emp_votes values (employee_id, current date);select count(*) into current_votes from emp_votes where empno ='1'; end ivote

-----------------------------------------------------------------------
for this no error is shown.

So my question is :
1. Is this the normal thing in DB2?
2. is there any problem in the Control center that i have.
3. Does any syntax needs to be corrected
4. If this is the way, then how will i be able to create big procedures that have cursors?

please have a look. and if any other control center has to be used then please inform.

Change the statement terminator to something other than ';'. Right now, you have either no terminator, which means that DB2 believes the statement ends and the end of line, so each line is a separate statement with a wrong syntax, or you have ';' as statement terminator, which tells DB2 that each ';' terminates the statement. Since you have used ';' for the nested statements inside the stored procedure, you effectively terminated the CREATE PROCEDURE statement in the middle, which is again a syntax error.

If you switch to a statement terminator like '@', you wont have any problems.

thank you sir, it worked

Hello, shailendra2908!
I recommend to change the statment delimeter, that would not receive the error.

For example:

For more information, try this: DB2 cook book by Graeme Birchall. There many examples of SQL queries and stored procedure's.

If you are asking this question, there's a decent chance you are coming to DB2 from SQL Server (yeah. me too. ). I use go for the terminator and that at least gives me some semblance of comfort.

Have to run. Accidentally used the tab key to indent multiple lines, and it erased my text instead, then I discovered there is no ctrl-z or undo in this thing