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) ,
READS SQL DATA
DECLARE var1 INTEGER DEFAULT 0;
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.
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.
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.