Execute Immediate in UDF

[jabernet]

In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
in a UDF (at least as I understand it; maybe I'm just doing something
wrong). Is this planed to be included in Future Versions? Somebody has
an Idea for a workaround to this?

What I need to do: We have for different customers an individual
customer table, and we'd like a table UDF to return the records of a
customer table based on the ID of a customer. This would allow not to
have to generate dynamic SQL for joining the tables in the
application, but just to call a function which does this for us.

Any ideas or hints would be appreciated.

Regards,
Janick

[Serge Rielau]

ja******@swissasp.ch wrote:

In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
in a UDF (at least as I understand it; maybe I'm just doing something
wrong). Is this planed to be included in Future Versions?

Future? Yes I can confirm something that fuzzily :-)

Somebody has an Idea for a workaround to this?

Create a procedure with the dynamic SQL in it.
Then CALL the procedure from the UDF.
Now a quick test reveals that PREPARE/EXECUTE is treated as MODIFIES SQL
DATA, so you may have to go with a TABLE UDF, since scalar UDF do not
support MODIFIES SQL DATA.

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

[jabernet]

On Mar 2, 10:52 pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

In DB2 LUW 'till Version 9 it is not possible to execute dynamic SQL
in a UDF (at least as I understand it; maybe I'm just doing something
wrong). Is this planed to be included in Future Versions?

Future? Yes I can confirm something that fuzzily :-)

Somebody has an Idea for a workaround to this?

Create a procedure with the dynamic SQL in it.
Then CALL the procedure from the UDF.
Now a quick test reveals that PREPARE/EXECUTE is treated as MODIFIES SQL
DATA, so you may have to go with a TABLE UDF, since scalar UDF do not
support MODIFIES SQL DATA.

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

Thanks for the fast response Serge. That's pretty much what I figured
I'll have to do.
Good to know though it will once be simplified in the far future :)

Regards,
Janick