Prev: DB2 UDB Security Best Practices documentation
Next: Shutdown all instances
From: Rafael Faria on 20 Apr 2010 23:45
Hi All,

Is it possible in DB2 to create a UDF which performs an insert into a
table and retunrs an integer? From reading the DB2 doco this doesn't
seem to be possible and trying with a simple sample proved my
assumption.
I understand that this can be achieved by using a procedure rather
than a function but the requirement is that this encapsulated logic
should be able to be executed from a SQL statement (a SELECT or INSERT
for instance).

Any suggestions would be greatly appreciated.

Bellow is the ideal function definition which does not compile.

CREATE TABLE edw02.raf_tab
(code INTEGER NOT NULL
,value INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1
,INCREMENT BY 1
,NO MAXVALUE
,NO CYCLE
,ORDER)
)@

CREATE FUNCTION edw02.test_raf (tst_code INTEGER)
RETURNS INTEGER
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN ATOMIC

DECLARE n_value INTEGER;

SET n_value = ( SELECT value
FROM edw02.raf_tab
WHERE code = tst_code
);

IF (n_value IS NULL) THEN

INSERT INTO edw02.raf_tab (code)
VALUES (tst_code);

SET n_value = ( SELECT value
FROM edw02.raf_tab
WHERE code = tst_code
);
END IF;

RETURN n_value;

END@


Regards,
Rafael
From: Rafael Faria on 21 Apr 2010 23:19
On Apr 22, 11:25 am, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> On 4/22/2010 5:25 AM, Rafael Faria wrote:> On Apr 21, 7:02 pm, Tonkuma<tonk...(a)fiberbit.net>  wrote:
> >> You can specify "MODIFIES SQL DATA" option in an SQL Table Function.
>
> > Correct, I realise that, but is this the only option?
>
> MODIFIES SQL DATA is supported fro compiled (BEGIN without ATOMIC) SQL
> UDF starting DB2 9.7.1 Note that the context where such functions can be
> invokes is very limitted.
> I.e. right-hand side of a SET statement.
> (or in case of PL SQL :=)
>
> Cheers
> Serge
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab

Thanks Serge.
From: Tonkuma on 21 Apr 2010 05:02
You can specify "MODIFIES SQL DATA" option in an SQL Table Function.
From: Rafael Faria on 21 Apr 2010 19:55
On Apr 21, 7:02 pm, Tonkuma <tonk...(a)fiberbit.net> wrote:
> You can specify "MODIFIES SQL DATA" option in an SQL Table Function.

Correct, I realise that, but is this the only option?
From: Serge Rielau on 21 Apr 2010 21:25
On 4/22/2010 5:25 AM, Rafael Faria wrote:
> On Apr 21, 7:02 pm, Tonkuma<tonk...(a)fiberbit.net> wrote:
>> You can specify "MODIFIES SQL DATA" option in an SQL Table Function.
>
> Correct, I realise that, but is this the only option?
MODIFIES SQL DATA is supported fro compiled (BEGIN without ATOMIC) SQL
UDF starting DB2 9.7.1 Note that the context where such functions can be
invokes is very limitted.
I.e. right-hand side of a SET statement.
(or in case of PL SQL :=)

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

 | 
Pages: 1
Prev: DB2 UDB Security Best Practices documentation
Next: Shutdown all instances