Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Question of SQL based Table UDF in DB2

    1) I wanted to write a SQL based table UDF, in DB2 in LUW 9.7. This SQL table UDF, takes in INTEGER as parameter, returns a table of 2 columns of type INTEGERS.

    Since I cannot do a dynamic SQL in a SQL based table UDF, I wrote a SQL-PL based stored procedure, STORE_PROC1 which will do the dynamic query and insert the result from the query in a temporary table, temp_table that contains 2 integer columns and commits the data . The table UDF then queries the temporary table and return the 2 column data as shown below ..

    Table UDF structure :
    CREATE FUNCTION myschema.GETASSIGNMENTS4GROUP( IN_INT INTEGER )
    RETURNS TABLE (
    OUT_INT1 INTEGER,
    OUT_INT2 INTEGER)
    LANGUAGE SQL MODIFIES SQL DATA
    NO EXTERNAL ACTION

    F1: BEGIN ATOMIC
    CALL store_proc1 (IN_INT);--
    RETURN SELECT out_INT1, OUT_INT2
    FROM myschema.temp_table;--

    END;


    Somehow this construct is not working, even though stored procedure worked with out any error. .. My table UDF is not returning any data .. Do not know why .. Any suggestions ??

    If I run the stored procedure alone, I an can see that the data gets inserted into the temporary table and I can verify through a select statement in a CLP Since this SQL based table UDF is not working, I have to go through External table UDF using Java..

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you used Stored procedure?

    Put the body of the Stored procedure into the UDF(and modify it to conform UDF syntax) might be easier.

    If you want a Stored procedure version too, you can reference the table UDF inside the Stored procedure.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Does store_proc1 populate the temp_table that you are selecting from in the function? Unless the sp is rather long and complex I agree with Tonkuma, whats the purpose of the SP?


    /Lennart
    --
    Lennart

  4. #4
    Join Date
    Apr 2012
    Posts
    3
    In the table UDF, which is SQL based, I cannot make a dynamic SQL. Is that a not a correct statement ?. The select query I need run to return the result in the table UDF, is in a column of a table . So I have to execute a dynamic SQL, to get the list of rows to be returned by the table UDF. I thought you cannot make a dynamic SQL call in a SQL based table UDF and also you cannot have multiple return statements in a SQL based table UDF. To get around this problem, I thought I would write a stored procedure which can be called from the table UDF. So, in the stored procedure, I would get the select statement from the column of a table row for the passed integer parameter which is unique primary key for the table . I then make dynamic SQL call using this column data to get the list of rows and insert them in another temporary table, temp_table and commit the data . Then in the table UDF , I do a select query to return the rows from temp_table.. This method was suggested in one of the threads.. May be I missed something.. Let me revisit those threads, my steps and the go through the Reference Manual once again ..

    I switched over to Java based external table Table UDF,

    Vasu

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Vasu_r_60 View Post
    In the table UDF, which is SQL based, I cannot make a dynamic SQL. Is that a not a correct statement ?. The select query I need run to return the result in the table UDF, is in a column of a table . So I have to execute a dynamic SQL, to get the list of rows to be returned by the table UDF. I thought you cannot make a dynamic SQL call in a SQL based table UDF and also you cannot have multiple return statements in a SQL based table UDF. To get around this problem, I thought I would write a stored procedure which can be called from the table UDF. So, in the stored procedure, I would get the select statement from the column of a table row for the passed integer parameter which is unique primary key for the table . I then make dynamic SQL call using this column data to get the list of rows and insert them in another temporary table, temp_table and commit the data . Then in the table UDF , I do a select query to return the rows from temp_table.. This method was suggested in one of the threads.. May be I missed something.. Let me revisit those threads, my steps and the go through the Reference Manual once again ..

    I switched over to Java based external table Table UDF,

    Vasu
    Ok, I see. This should work (even though a bit unorthodox). Can you post a minimal example with the sql statement table, some statements, the temp table and the procedure?
    --
    Lennart

  6. #6
    Join Date
    Apr 2012
    Posts
    3
    Anyway, my customer did not want a new table to be created.. So I moved away from SQL based UDF to Java based external table UDF .

    With external UDF, the UDF is not returning data , but keeps displaying nulls
    probably in a loop..I have to stop the UDF, when I do

    select * form table(myschema.GETASSIGNMENTS4CPG(100))..

    I took the sample code supplied by IBM ( db2instancepath/samples/java/jdbc directory.. ) . I did not see anything wrong withe the Java code . Can I know what is wrong with my code, as to why I am not getting data ?. Java is not my cup of tea, but I did not see much different from the sample code provided by IBM..

    Following is the create function I used.. Built the jar file and used normal procedure to create the function in data server.. ( which is 64 bit Linux )

    CREATE FUNCTION myschema.GETASSIGNMENTS4CPG(CPG_IDIN INTEGER)
    RETURNS TABLE (CPG_ID INTEGER, SPI_ID INTEGER)
    SPECIFIC getAssignments4CPG
    READS SQL DATA
    NO EXTERNAL ACTION
    SCRATCHPAD 10
    RETURNS NULL ON NULL INPUT
    DISALLOW PARALLEL
    LANGUAGE JAVA
    EXTERNAL NAME 'UDFSQLSRV:UDFsqlsrv!getAssignments4CPG'
    FENCED THREADSAFE
    NOT DETERMINISTIC
    DBINFO
    PARAMETER STYLE DB2general@

    My Java code for the UDF is attached below..


    Vasu
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2012
    Posts
    1

    thanks for sharing

    Althought i haven't figure out how to get this done, but thanks

Posting Permissions

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