SEO

Hello to everyone,

I'm currently doing some UDF and stored procedure development in DB2 v.9.

I have a problem with the following construct:

SET (var_a, var_b) = (select a, b from table1 where ...)

This statement is acceptable in a UDF, but DB2 rejects it in a stored
procedure...

A one-variable SET statement (SET var_a = ... ) is acceptable both in
UDF's and stored procedures.

Is it a normal behaviour of DB2? Is there any other way to set two
variables with one statement (a select into perhaps) that would work
both in a stored procedure written in SQL ?

Thanks in advance,

--
Szymon Dembek

Szymon Dembek wrote:
> Is it a normal behaviour of DB2? Is there any other way to set two
> variables with one statement (a select into perhaps) that would work
> both in a stored procedure written in SQL ?
In DB2 9 unfortunately that is the case. The reason lies in the very
different implementation between inline SQL PL (functions, triggers) and
regular SQL PL (procedures).
Right now your're stuck with SET in inlien SQL PL and SELECT/VALUES INTO
in procedures.

However! This has been fixed (drum roll, first non-official
announcement, I may be shot) in the release presently in closed beta and
soon to be open beta.
In this upcoming release the SET statement is a first class statement
and you can do what you intend in an SQL procedure or even from JDBC,
CLP, ...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
> Szymon Dembek wrote:
> > Is it a normal behaviour of DB2? Is there any other way to set two
>> variables with one statement (a select into perhaps) that would work
>> both in a stored procedure written in SQL ?
> In DB2 9 unfortunately that is the case. The reason lies in the very
> different implementation between inline SQL PL (functions, triggers) and
> regular SQL PL (procedures).
> Right now your're stuck with SET in inlien SQL PL and SELECT/VALUES INTO
> in procedures.
>
> However! This has been fixed (drum roll, first non-official
> announcement, I may be shot) in the release presently in closed beta and
> soon to be open beta.
> In this upcoming release the SET statement is a first class statement
> and you can do what you intend in an SQL procedure or even from JDBC,
> CLP, ...
>
> Cheers
> Serge

Hi,

Big thanks for this very fresh and very secret news

On another topic: do you have any info regarding what is the rationale
behind the following:

To make it possible to do updates, inserts and deletes in an UDF, you
have to declare it with "MODIFIES SQL DATA". But unfortunatelly it works
only in case of a table function and not in case of a scalar function
(according to db2 doc).

May I hope this limitation will get worked around sooner or later ?

Best regards

--
Szymon Dembek

Szymon Dembek wrote:
> To make it possible to do updates, inserts and deletes in an UDF, you
> have to declare it with "MODIFIES SQL DATA". But unfortunatelly it works
> only in case of a table function and not in case of a scalar function
> (according to db2 doc).
>
> May I hope this limitation will get worked around sooner or later ?
I think one of major obstacles to lift this restrictions is my humble
self ;-)
MODIFYING SQL DATA within a query is an inherently dangerous thing. In
general side-effects are frowned upon for good reason.
We started allowing nested modification in the FROM clause because the
rewards are quite high. That gace us SELECT FROM INSERT/UPDATE/DELETE
and TABLE UDF. But we decided to place very strong restrictions on them.
E.g. a table udf must correlate to all other tables in the FROM clause
to force it to be the innermost join partner.
Outside of table functions we don't allow joins directly at all (you
have to use WITH clauses to "work around" it.
Now, if we were to allow scalar UDF which modify the database there are
similar fears around order of execution and decisions whether or not to
execute. E.g. WHERE clauses, arguments to CASE expressions or COALESCE.
What about order of execution with the select list?

To sum it up, I have yet to see an argument strong enough to convince me
that this feature is any better than a GOTO statement....

So.. if you make a good point....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
> Szymon Dembek wrote:
>> To make it possible to do updates, inserts and deletes in an UDF, you
>> have to declare it with "MODIFIES SQL DATA". But unfortunatelly it
>> works only in case of a table function and not in case of a scalar
>> function (according to db2 doc).
>>
>> May I hope this limitation will get worked around sooner or later ?
> I think one of major obstacles to lift this restrictions is my humble
> self ;-)
> MODIFYING SQL DATA within a query is an inherently dangerous thing.

You're totally right, I keep forgetting that UDF is inline SQL actually...

I'm porting some Sybase ASA udf's to db2 and they work quite differently
in Sybase - you can do updates, you have in and out parameters... Quite
frankly - I can't tell the difference between function and procedure in
sybase (except from return value) - but I'm a total newbie in regard to ASA.

Best regards

--
Szymon Dembek

Szymon Dembek wrote:
> I'm porting some Sybase ASA udf's to db2 and they work quite differently
> in Sybase - you can do updates, you have in and out parameters... Quite
> frankly - I can't tell the difference between function and procedure in
> sybase (except from return value) - but I'm a total newbie in regard to
> ASA.
INOUT is another line I'd prefer not to cross.
But if have a Sybase system at hand you can perhaps run some experiments
for your amusement (and if you wish my education):
create a table T with an identity column and a regular column.
create some random other table S
create a function foo(arg) that inserts its argument into T
Now try combinations like:
SELECT foo(1), foo(2) FROM S WHERE foo(3 + S.c1) = foo(4 + S.c1)
I wonder how S looks like afterwards.

You can run similar tests with INOUT arguments ;-)

Of course my example is mean and what what a real user would do.
But I'd love to know what real user WOULD do with that functionality.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
> SELECT foo(1), foo(2) FROM S WHERE foo(3 + S.c1) = foo(4 + S.c1)
SELECT .. FROM T that is.
Read/write conflicts are another kettle of fish I'd rather not think
about...



--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab