Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: Error creating simple procedure

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    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

  3. #3
    Join Date
    Jul 2004
    Posts
    3
    Quote Originally Posted by bmujeeb
    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)?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You will have to change the Statement Delimiter in your tool to @...

    By default, most tools use ;

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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