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