0

This is my code: I trying to create UDF function for z/os from Data Studio . I don't need to use External function or others. I need to execute this SQL Function .

 CREATE FUNCTION FUNCTION5()
 RETURNS FLOAT
 language sql
 DETERMINISTIC
 READS SQL DATA 
 NO EXTERNAL ACTION
 BEGIN 
 DECLARE RANVAL FLOAT ;
 SELECT RAND() INTO RANVAL FROM SYSIBM.SYSDUMMY1;   
 RETURN RANVAL ;
 END

But, while executing the above code, i'm getting this error. Can yuou please help me to figure it out please.

I want to develop nearly 20 Scalar UDF on DB2 Z/OS . kindly help me, please.

 Deploy [MeDB]MeDB.FUNCTION5

Running
MeDB.FUNCTION5 - Deploy started.
Create user-defined function returns SQLCODE: -199, SQLSTATE: 42601.
MeDB.FUNCTION5: 0: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601,      SQLERRMC=DECLARE;ON AFTER <INTEGER>, DRIVER=4.18.60
DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=DECLARE;ON AFTER <INTEGER>, DRIVER=4.18.60
MeDB.FUNCTION5 - Deploy failed.
MeDB.FUNCTION5 - Roll back completed successfully.
Vivek Harry
  • 419
  • 3
  • 11
  • 25

1 Answers1

0

I believe the function you're trying to create is not supported in DB2 7. The usage of the BEGIN, END, and DECLARE keywords indicate you're trying to use SQLPL, which isn't supported until DB2 10 as compiled scalar functions. I don't have any documentation for 7, but I think you can only create inline scalar functions, which is limited to one expression in a RETURN statement as described in the DB2 10 reference for CREATE FUNCTION (inline scalar):

The CREATE FUNCTION (inlined SQL scalar) statement defines an SQL scalar function at the current server and specifies an SQL procedural language RETURN statement for the body of the function. The function returns a single value each time it is invoked.

SQL-routine-body

Specifies a single RETURN statement.

David
  • 170
  • 2
  • 9