Dynamic SQL in table SQL UDFs

[Eugene]

In a DB2 V8.1 FP4 database I am trying to create a table SQL UDF that
is to return a contents of a temporary table with in this UDF:
create function getitemdata(pint int)
returns table
(
hostid smallint,
owid bigint
)
specific getitemdata
MODIFIES SQL DATA
NO EXTERNAL ACTION
begin atomic
declare v_temp_sql varchar(256);

set v_temp_sql =
'declare global temporary table '||
'item_data_tmp '||
'( '||
' hostid smallint, '||
' owid bigint '||
') '||
'on commit preserve rows not logged with replace' ;

execute immediate v_temp_sql;

<...inserting into session.item_data_tmp here...>

return select * from session.item_data_tmp;
end
@

It fails returning:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "execute immediate v_temp_sql;
<...inse" was
found following "mpuser_8k_01' ; ". Expected tokens may include:
"<space>". LINE NUMBER=25. SQLSTATE=42601

Are dinamic SQL statements (EXECUTE/PREPARE) not allowed in table SQL
UDFs (FP4)? Any workaround?

Thanks,
-Eugene

[Serge Rielau]

No can do. Only the statements listed in "Compound Statement (Dynamic)"
are allowed in inline SQL PL. The idea is to keep it small and slick.
I would propose to use a persistent table and some unique "token" to
privatize access to the table by session.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab