Unanswered: CREATE PROCEDURE failing in DB2 UDB v7.2
I am trying to create a very simple stored procedure in DB2 UDB v7.2, as follows:
CREATE PROCEDURE XANSAPROC(IN IDTOUSE CHAR(20))
SET SOMEDATA = 'NEWDATA' WHERE MYPARENTID = IDTOUSE;
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
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.
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