Why are you writing this as a function instead of a stored procedure? A
UDF typically takes inputs, performs some calculation and returns a
result based on those inputs.
A procedure is designed to take series of inputs, do some actions based
on those inputs (select & return results, do inserts, etc) and possibly
return some values through output parameters based on the actions it
performed. Even then, it's designed for BATCHES of SQL statements -- a
simple insert statement like what you have below will actually perform
SLOWER if you do it inside a procedure instead of directly in your
application -- it takes just and long to send a single call statement
from your client to DB2 as a simple insert statement, and then you add
the processing of the insert itself.
Anyway to the problem at hand: the documentation notes that I found
state that "MODIFIES SQL DATA" can only be specified for SQL table
functions, not SQL scalar functions.
=======================================
.-EXTERNAL ACTION----. .-READS SQL DATA---------.
--*--+--------------------+--*--+------------------------+----->
'-NO EXTERNAL ACTION-' +-CONTAINS SQL-----------+
| (1) |
'- MODIFIES SQL DATA ---'
(1) Valid only if RETURNS specifies a table (TABLE column-list)
=======================================
You cannot write a scalar function that modifies SQL.
db*****@yahoo.com wrote: I am getting SQL0628N when I run function with INSERT and MODIFIES SQL
DATA
Version:
DB2 v8.1.7.445 Fixpack 7.
Create function test1( a int, b int)
returns integer
language sql
modifies sql data
begin atomic
insert into test1 values(1,2);
return 1;
end@
Am I missing something?
