Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Location
    Tuticorin, India
    Posts
    103

    Unanswered: UDF with dynamic sql as argument

    Environment : DB2 UDB V8.2/AIX 5.3

    Background : Application migration from Oracle to DB2

    How to code an equivalent DB2 UDF having a dynamic sql as an argument to get the count of rows present in the table?

    Equivalent oracle UDF is as below
    create or replace FUNCTION row_count_check (sql1 IN VARCHAR2)
    RETURN NUMBER IS
    Count_NO NUMBER;
    BEGIN
    EXECUTE IMMEDIATE sql1 INTO count_no;
    RETURN (count_no);
    END;

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Why dont you pass the tablename instead of the query and then execute the same.


    you can follow the following steps

    1. Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR statement1

    2. Create the statement
    SQLQuery = 'SELECT Count(*) FROM ' || TableName ( where TableName is the variable which you have passed )

    3. Prepare the statement
    PREPARE statement1 from SQLQuery

    4. Open cursor
    OPEN cursor1

    5. Put the value into a variable
    FETCH cursor1 into TableCount

    6. Finally Return the count
    RETURN TableCount
    Last edited by nick.ncs; 01-28-09 at 14:30.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Dynamic SQL is not possible in UDFs. You have to use a stored procedure for such a task.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2010
    Posts
    7
    Hi Knut.....I looked at one of your other recent replies where you have suggested to use Dynamic Statement in UDF, so does that means the new version allows this functionality:

    What I am struggling with is to use CASE with Multiple Select Statements in UDF ....Not sure if that's possible or if that can be achecived using some other efficient way:

    The Function I am trying is, accepts two parameter and based on one of the parameter TYPE select from a specific table.......
    CREATE FUNCTION TEST.GET_CHILD_ID(
    IN_ID BIGINT,
    IN_TYPE BIGINT
    )
    RETURNS BIGINT
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    RETURNS NULL ON NULL INPUT
    BEGIN ATOMIC
    DECLARE CHILD_ID BIGINT;
    CASE
    WHEN IN_TYPE=1 or IN_TYPE=11
    THEN SELECT TABLE_A.ID INTO CHILD_ID FROM TEST.TABLE_A AS TABLE_A WHERE TABLE_A.TABLE_A_ID = IN_ID;
    WHEN IN_TYPE=4 or IN_TYPE=41
    THEN SELECT TABLE_B.ID INTO CHILD_ID FROM TEST.TABLE_B AS TABLE_B WHERE TABLE_B.TABLE_B_ID = IN_ID;
    WHEN IN_TYPE=2 or IN_TYPE=21
    THEN SELECT TABLE_C.ID INTO CHILD_ID FROM TEST.TABLE_C AS TABLE_C WHERE TABLE_C.TABLE_C = IN_ID;
    WHEN IN_TYPE=0 or IN_TYPE=111
    THEN SELECT TABLE_D.ID FROM INTO CHILD_ID TEST.TABLE_D AS TABLE_D WHERE TABLE_D.TABLE_D_ID = IN_ID;
    WHEN IN_TYPE=9 or IN_TYPE=91
    THEN SELECT TABLE_E.ID INTO CHILD_ID FROM TEST.TABLE_E AS TABLE_E WHERE TABLE_E.TABLE_E_ID = IN_ID;
    END CASE;
    RETURN CHILD_ID;
    END@


    http://www.dbforums.com/db2/1662124-...ml#post6485753

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Scalar SQL UDFs do not support dynamic SQL. You can use a so-called "Compound SQL (Dynamic) statement" and there is no OPEN/FETCH/CLOSE allowed in the function body. You can use a cursor in a table function, however.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Nov 2010
    Posts
    7
    I guess I am using the Compound SQL in my function.....Please correct me if I am wrong. I have tried writing this Function in all the possible ways but somehow I am stuck with this.......just can't make it work.
    Last edited by GKUMA; 11-23-10 at 17:35.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are using a CASE statement inside the compound SQL (BEGIN ATOMIC ... END), which is not supported because the syntax diagram does not list it as allowed statement. dav1mo mentioned that in the other thread you are referencing above. I suggest you rewrite your UDF implementation as Dave proposed.
    Code:
    CREATE FUNCTION ...
       RETURNS ...
       RETURN SELECT ...
                 UNION
              SELECT ...
                 UNION
              ...@
    If you think that your code should actually work, please explain why you believe the DB2 documentation supports that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2011
    Posts
    1

    Error when creating UDF

    I have a code example here.


    IBM V91 for Z/OS:

    CREATE FUNCTION TEST.FN_T2( )
    RETURNS DATE FOR SBCS DATA CCSID EBCDIC
    PARAMETER CCSID EBCDIC
    LANGUAGE SQL
    READ SQL DATA
    BEGIN ATOMIC
    RETURN SELECT DATE(CURRENT TIMESTAMP)
    FROM SYSIBM.SYSDUMMY1;

    I am getting this error message..
    Error:22/02/2011 14:15:09 0:00:00.015: DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "FOR" was found following "". Expected tokens may include: ", ) <END-OF-CLAUSE>". SQLSTATE=42601

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The syntax doesn't fit. You have a BEGIN ATOMIC but no matching END. But you don't need the BEGIN ATOMIC anyway. Just use the RETURN statement as the whole body of the function.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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