Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: Is this NORMAL in DB2 ??

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    thank you sir, it worked

  4. #4
    Join Date
    Feb 2009
    Posts
    14
    Hello, shailendra2908!
    I recommend to change the statment delimeter, that would not receive the error.

    For example:

    --#SET DELIMITER !
    SELECT NAME FROM STAFF WHERE id = 10!
    --#SET DELIMITER ;
    For more information, try this: DB2 cook book by Graeme Birchall. There many examples of SQL queries and stored procedure's.

  5. #5
    Join Date
    Mar 2009
    Posts
    1

    Lightbulb Use go for terminator

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •