471,893 Members | 2,120 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

Dynamic SQL in table SQL UDFs

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
Nov 12 '05 #1
1 2340
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Ralph | last post: by
7 posts views Thread by Yaro | last post: by
3 posts views Thread by Alexandre H. Guerra | last post: by
5 posts views Thread by jcccs05 | last post: by
12 posts views Thread by Serman D. | last post: by
reply views Thread by Helmut Tessarek | last post: by
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.