Can dynamic sql be used inside the UDF or not? other wise is any way there to implement that?
Can sql procedure be called from inside the UDF, if yes from what version.

Please clarify these doubts.

IIRC, you could do cursors from FP4 (but do not quote me on this) and from FP7(aka 8.2) you can call SPs ..

I checked with IBM manual 'whats new with DB2 version 8.2' but they have only mentioned Sps can be called from triggers, no info about UDF.

hmmm ... I have a working example of a UDF calling a SP at my current client (though not in prod, yet)

Cheers
Sathyaram

I downloaded trial version of 8.2:

This proc is working fine, but I am not able to compile function, I am getting this error:

ADMINISTRATOR.temp: 16: [IBM][CLI Driver][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=16. SQLSTATE=42613

ADMINISTRATOR.temp - Build failed.
ADMINISTRATOR.temp - Roll back completed successfully.

What may be the problem?


CREATE PROCEDURE ADMINISTRATOR.PROC (in tabname varchar(50),colname varchar(50),out output1 varchar(500) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
RESULT SETS 1
LANGUAGE SQL

P1: BEGIN

declare stmt varchar(100);
declare var varchar(20);
declare c1 cursor with return for s1;

set stmt = 'select ' || colname || ' from ' || tabname ;

prepare s1 from stmt;
open c1;

fetch c1 into var;

set output1 = var;

END P1
---------------

CREATE FUNCTION ADMINISTRATOR.temp(tabname varchar(50),colname varchar(50) )

LANGUAGE SQL
MODIFIES SQL DATA
RETURNS VARCHAR(500)

------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
declare output1 varchar(500);
call proc(tabname,colname,output1);
RETURN output1;

END

In CREATE UDF, the clause MODIFIES SQL DATA is permitted only for table Functions .. If you are using scalar functions, then only contains sql and reads sql data are allowed ...
If this is a real example, you may use READS SQL DATA in UDF ..


But, if your proc is defined as modifies sql data , then you cannot define a scalar UDF ... The work around is to define a TABLE function and handle this table to a scalar value conversion in your base SQL ...

But remember, this UDF will be called from a SELECT Statement, and therefore it is not advisable(though technically feasible as in the example below) to modify data ... SELECT Statements are fundamentally read only and I would want to adhere to the protocol ...

Eg. Can you create a table using a SELECT statement ??? Yes, you can ...

CREATE PROCEDURE ADMINISTRATOR.PROC (in tabname varchar(50),colname varchar(50),out output1 varchar(500) )
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
RESULT SETS 1
LANGUAGE SQL
modifies SQL DATA

P1: BEGIN

declare stmt varchar(100);
declare var varchar(20);
declare c1 cursor with return for s1;

set stmt = 'create table i(i int)' ;
execute immediate stmt ;

set output1 = var;

END P1

CREATE FUNCTION ADMINISTRATOR.temp(tabname varchar(50),colname varchar(50) )
RETURNS TABLE(output1 VARCHAR(500))


modifies sql data
BEGIN ATOMIC
declare output1 varchar(500) ;
call ADMINISTRATOR.PROC(tabname,colname,OUTPUT1);
RETURN values(output1);

END

select * from table(ADMINISTRATOR.temp('SYSCAT.TABLES','TABNAME' )) AS x(COL1)

select * from i


Cheers
Sathyaram

My situation is SP procedure will return a string from dynamic sql and from my UDF I need to get that string. Is this possible or not?

My verison is: DB2 8.2 ESE

Please tell how I can achieve this?

With what you have given before, change your UDF defn to

CREATE FUNCTION ADMINISTRATOR.temp(tabname varchar(50),colname varchar(50) )
RETURNS VARCHAR(500)
LANGUAGE SQL
READS SQL DATA


------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
declare output1 varchar(500);
call proc(tabname,colname,output1);
RETURN output1;
END

this will work ...

Cheers
Sathyaram

This ofcourse assumes you are refering to UDB, not DB2 for zOS. Only simple SCALAR SQL UDFs and traditional COBOL, PL/1, C, or ASM UDFs can be created - complex SPL UDFs are not supported in DB2, only in UDB.

This function is giving error:

SQL0374N The "MODIFIES SQL DATA" clause has not been specified in the CREATE FUNCTION statement for LANGUAGE SQL function "ADMINISTRATOR.TEMP" but an examination of the function body reveals that it should be specified. LINE NUMBER=13. SQLSTATE=428C2


CREATE FUNCTION ADMINISTRATOR.temp(tabname varchar(50),colname varchar(50) )
RETURNS VARCHAR(500)
LANGUAGE SQL
READS SQL DATA


------------------------------------------------------------------------
-- SQL UDF (Scalar)
------------------------------------------------------------------------
F1: BEGIN ATOMIC
declare output1 varchar(500);
call proc(tabname,colname,output1);
RETURN output1;
END

I cannot help but notice that there is a concurrent thread "Problem calling a procedure in a function" with remarkably similar problems. Wouldn't be someone from the same site would it?

The called "proc" wouldn't have "modifies sql data" by any chance. DB2 seems to be saying that if the called proc modifies data, then the calling udf must be in modify-mode.

If appears you can only use modifies sql data in a UDF if that udf
- is an sql language udf
- and it returns a table.

James Campbell