sql procedures will not compile

[pfist]

I am new to DB2 and as400, but not to databases. Background is java and Oracle PL/SQL development.

I created schema test2 and I am trying to compile SQL procedure below. Is there something wrong with this procedure? I am trying to create procedures in general but with no success. Machine is V5R1. Would someone give me a simple yet valid procedure that has no dependencies on their own machine. ( you can 'fix' my procedure and repost if you like ) I will try to compile on mine. I suspect that I am lacking privileges needed to create procedures. Please help.

Thanks, pfist
----------------------------------------------------
create procedure test2.doubleIt( INOUT p_in integer )
LANGUAGE SQL
begin
SET p_in = p_in * 2;
end;

 

[stressman]

are you sure that set p_in = p_in * 2 is SQL language

 

[pfist]

(Note: this is on an AS/400 V5R1) I believe what I have here is valid syntax for a DB2 database. That is what I am trying to confirm. I look through the reference documentation and appears to say that basic programming constructs are supported, e.g. 'if then' 'for' etc. But no matter what I do I cannot compile any procedure or function. I don't know why. Here is another example that I can't compile. The table definition is included. Does anybody out there use SQL procedures and functions out there or has everybody gone to 'external' procedures. Give me something that you can compile and I will try it.

create table TEST2.T1
( COL_PK integer primary key
, COL_DATA VARCHAR(20) NOT NULL );

CREATE PROCEDURE TEST2.DOIT ( IN p_pk INTEGER, IN p_val VARCHAR(20) )
LANGUAGE SQL
INSERT INTO TEST2.T1
( COL_PK, COL_DATA ) VALUES ( p_pk, p_val );

This example doesn't give any meaningful error codes: it just says:
"SQL7032 - SQL procedure, function, or trigger DOIT in TEST2 not created"

In fact--with what little experience I have--all error messages in DB2 are quite cryptic.

pfist