Hi guys,

I am trying to create a function on DB2 version 9.5 (on 64 bit AIX). I have written the following function definition into a file.


CREATE FUNCTION SMODEL.sampleF
(
param1 DECIMAL(4) ,
param2 CHARACTER(6) ,
param3 VARCHAR(16) ,
param4 DECIMAL(9) ,
param5 CHARACTER(2) ,
param6 CHARACTER(1) ,
param7 DECIMAL(11,5) ,
param8 DATE
)
RETURNS DECIMAL(18,4)
LANGUAGE SQL
READS SQL DATA
f1: BEGIN
DECLARE var1 INTEGER DEFAULT 0;
RETURN 0;
END f1
@


When I try to create the function from the command line using

db2 -td@ -f st.sql

I get the following error

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 "DECLARE" was found following "L DATA f1: BEGIN
". Expected tokens may include: "<delim_atomic>". LINE NUMBER=15.
SQLSTATE=42601


I also get the same error when I connect to database using TOAD DB2 and run the create command as a script.

It works fine on windows with DB2 ExpressC 8.1, I made sure that it is not an end of statement issue (i am using @), it is not an dos2unix end of line character issue. I would appreciate your help on this.

Thanks a lot.

Look at the documentation for CREATE FUNCTION:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

and

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

You need BEGIN ATOMIC, not BEGIN.

Andy

Thanks for the reply.

I tried it before but when I put ATOMIC, I can not declare a cursor in the function definition. I might be confusing some basic things about DB2 functions but DB2 is not consistent itself. It works without ATOMIC in DB2 ExpressC.

So my question is now:

May I create a function with ATOMIC and declare a cursor in it?

I have read some documentation but it did not help.

You cannot declare a cursor in a function. The syntax diagrams on the links I provided so that.

Andy