Results 1 to 11 of 11

Thread: UDF urgent help

  1. #1
    Join Date
    Dec 2004
    Posts
    54

    Unanswered: UDF urgent help

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IIRC, you could do cursors from FP4 (but do not quote me on this) and from FP7(aka 8.2) you can call SPs ..
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2004
    Posts
    54
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    hmmm ... I have a working example of a UDF calling a SP at my current client (though not in prod, yet)

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Dec 2004
    Posts
    54
    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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Last edited by sathyaram_s; 01-28-05 at 18:32.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Dec 2004
    Posts
    54
    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?

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Apr 2002
    Location
    Chicago
    Posts
    10
    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.




    Quote Originally Posted by sathyaram_s
    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

  10. #10
    Join Date
    Dec 2004
    Posts
    54
    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

  11. #11
    Join Date
    Jan 2005
    Posts
    191
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •