FUNCTION: MODIFIES SQL DATA

[db2sysc@yahoo.com]

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?

[amurchis]

Re: FUNCTION: MODIFIES SQL DATA

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---------.[color=blue]
>--*--+--------------------+--*--+------------------------+----->[/color]
'-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.



db2sysc@yahoo.c om wrote:[color=blue]
> 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?
>[/color]

[Serge Rielau]

Re: FUNCTION: MODIFIES SQL DATA

db2sysc@yahoo.c om wrote:[color=blue]
> 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?
>[/color]
Only SQL Table functions are allowed to be MODIFIES SQL DATA.
(see syntax diagram)
The reason is that modifying SQL data inside of a WHERE clause or SELECT
list get srather bizarre semantically.
Note that the usage of SQL Table functions which modify SQL dat ais also
limited. Only in to level queries, and if there is a join they must
correlate to all other joined tables to enforce a clear ordering.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab