db2 function error! help

[shibing.yu]

my function is


CREATE FUNCTION BOD_SEQ_NEXT
()
RETURNS NUMERIC(19)
LANGUAGE SQL
MODIFIES SQL DATA
FENCED
NOT DETERMINISTIC
EXTERNAL ACTION
BEGIN ATOMIC
declare v_seq_next NUMERIC(19) default -1;
-- If the sequence table was initialized at table creation with one row,
-- the delete & insert could be replaced with single update.
-- Also, the SEQ column need not be declared as PRIMAY KEY since
-- a sequence table should only contain one row.
-- delete from BOD_SEQ;
-- insert into BOD_SEQ (SEQ) values (default);
update TMGIIM.BOD_SEQ set SEQ = default;
select max(SEQ) into v_seq_next from TMGIIM.BOD_SEQ;
RETURN v_seq_next;
END@


but i get the 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 "v_seq_next " was found following "select
max(SEQ) into". Expected tokens may include: "<space>". LINE NUMBER=18.
SQLSTATE=42601

(btw
db2 version is 7.2.1 and patch is FP12_WR21337
)
thanks

[Serge Rielau]

Re: db2 function error! help

On a microsocopic level there are two things wrong here that I see:
1. select max(SEQ) into v_seq_next from TMGIIM.BOD_SEQ;
needs to be:
SET v_seq_next = (select max(SEQ) from TMGIIM.BOD_SEQ) ;
2. MODOFIES SQL DATA is only allowed in TABLE function and only in
DB2 V8.1 FP4 and up for LUW.
DB2 UDB for z/OS supports MODIFIES SQL DATA is very specific contexts
(such as VALUES on INSERT or right-hand side of UPDATE SET.

On a macrosocopic level you seem to be using IDENTITY already. Why not
use a sequence instead.

Assuming you had to IDENTITY (for some odd reason) you can use
IDENTITY_VAL_LO CAL in V7 or SELECT FROM UPDATE in V8.1.4 to retrieve the
value instead of doing select max().

Cheers
Serge

[shibing.yu]

Re: db2 function error! help

i modify
set v_seq_next =(select max(SEQ) from TMGIIM.BOD_SEQ) ;

but the error message is

[IBM][CLI Driver][DB2/NT] SQL0628N Multiple or conflicting keywords
involving the "LANGUAGE SQL" clause are present SQLSTATE=42613

by the way
db2 version IS DB2/NT 8.1.6

[Serge Rielau]

Re: db2 function error! help

That's because of the MODIFIES SQL DATA. It's not supported with
language SQL

Cheers
Serge