"Yaro" <ya************ *@op.pl> wrote in message
news:cf******** **@nemesis.news .tpi.pl...
Hello
Is it possible using dynamic SQL in UDF?
In Sybase below example work but in DB2 UDB 8.1.3 I get error:
"SQL0104N Unexpected element "EXECUTE IMMEDIATE" found....."
CREATE FUNCTION DropTable(p_Tab leName VARCHAR(30) )
RETURNS VARCHAR(50)
LANGUAGE SQL
BEGIN ATOMIC
DECLARE var_SqlStr VARCHAR(50);
SET var_SqlStr = 'DROP TABLE ' || p_TableName;
EXECUTE IMMEDIATE var_SqlStr;
RETURN var_SqlStr;
END@
Any other manners?
It appears that what you are trying to do is not possible in a UDF in V8.1
of DB2. (I am assuming that you are on Windows, Linux, or Unix).
According to the SQL Reference Volume 2
(
ftp://ftp.software.ibm.com/ps/produc...r/db2s2e80.pdf
), the CREATE FUNCTION (SQL Scalar, Table or Row) statement may only have
one of the following in the SQL function body: the keyword RETURN (which
doesn't help you) or a "dynamic-compound-statement". If you follow the link
for "Compound SQL (Dynamic)" at the bottom of the page, it says that the
only SQL statements you can use are: fullselect; searched UPDATE, searched
DELETE, INSERT, or SET. Apparently, DROP and EXECUTE IMMEDIATE are not
allowed in this context. You will have to drop tables in another way.
Luckily, there are many ways to drop tables in DB2:
- You can use the Control Center.
- You can issue DROP commands from the DB2 command window.
- You can code DROP statements within applications. For example, I just used
the Stored Procedure Builder to create a Java UDF that will drop any table
within the database to which I am currently connected. (I haven't installed
DB2 V8.1 yet so I used the Stored Procedure Builder within DB2 V7.2. You
should be able to create a similar Java UDF in DB2 V8.1 with the Development
Center.)
Rhino