Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Bath, England
    Posts
    7

    Question Unanswered: CREATE PROCEDURE failing in DB2 UDB v7.2

    Hi folks

    I am trying to create a very simple stored procedure in DB2 UDB v7.2, as follows:

    CREATE PROCEDURE XANSAPROC(IN IDTOUSE CHAR(20))
    LANGUAGE SQL
    BEGIN
    UPDATE XANSATAB
    SET SOMEDATA = 'NEWDATA' WHERE MYPARENTID = IDTOUSE;
    END

    The SQL in lines 4 and 5 works perfectly when run alone in Command Centre with the ID value hard-coded. I have amended elements of the stored procedure command many times, but whatever I try I get 2 error messages, as follow:

    Error message 1
    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 "MYPARENTID = IDTOUSE". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=5. SQLSTATE=42601

    Error message 2
    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

    Has anyone been here before?

    Many thanks if you can help.

    Best regards

    Marc

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Yes, we've been here before (more than once!)

    http://dbforums.com/showthread.php?t...ighlight=42601

  3. #3
    Join Date
    Jan 2003
    Location
    Bath, England
    Posts
    7

    Is a C compiler required?

    Firstly, thanks to Damian for the link to the related thread. I have tried re-setting the termination character to several values, and have tried all of these with and without the @ after the final END. So far no joy, but will keep trying.

    In the mean time, while I was searching for info I found another post. This said that in order to write SQL language stored procedures on UDB7.2 (which is what I am working on) 'you need to have a C compiler installed on the server... DB2 converts the SP into C under the covers'. I have made enquiries with my client and discovered (1) that nobody has written any SPs on this system before; and (2) it is very unlikely that a C compiler would have been put onto the server.

    Can someone confirm that this is indeed a requirement, please? I do not want to advise the client to install a compiler only to find that it is not in fact required.

    Many thanks.

  4. #4
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    It's indeed required. All stored procedures are compiled into the native language by a C compiler and stored as object code.
    For further info, you can view any of the sample .db2 files under /samples/sqlproc folder in your DB2 installation

Posting Permissions

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