Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2012
    Location
    Zagreb, Croatia
    Posts
    11

    Unanswered: User defined function (UDF) - DB2 z/OS v9.1

    Hello,
    is there possibility in DB2 for z/OS v9.1 to have UDF with some logic in it - to use IF, WHILE,... statements.

    I have this UDF :
    CREATE FUNCTION NADJI_PARENT(CHILD INT)
    RETURNS INTEGER
    NO EXTERNAL ACTION
    F1: BEGIN ATOMIC
    DECLARE MYPARENT INTEGER;
    DECLARE PREVPARENT INTEGER;
    DECLARE REDOVI INTEGER;
    SET PREVPARENT = CHILD;

    SET REDOVI = (SELECT COUNT(*) FROM RIODS.REQUEST_HIERARCHY WHERE CHILD_REQUEST_ID = PREVPARENT);

    WHILE REDOVI > 0 DO
    SET MYPARENT = (SELECT DISTINCT ROW PARENT_REQUEST_ID FROM RIODS.REQUEST_HIERARCHY WHERE CHILD_REQUEST_ID = PREVPARENT FETCH FIRST 1 ROWS ONLY);
    SET REDOVI = (SELECT COUNT(*) FROM RIODS.REQUEST_HIERARCHY WHERE CHILD_REQUEST_ID = PREVPARENT);
    IF(REDOVI <> 0) THEN
    SET PREVPARENT = MYPARENT;
    END IF;
    END WHILE;
    RETURN PREVPARENT;
    END

    and I'm getting this error
    "alt_sql_proc_rb" was expected instead of "CREATE FUNCTION nadji_parent(child int)
    RETURNS INTEGER
    NO EXTERNAL ACTION".
    As I can tell it's complaining about 'BEGIN ATOMIC'.

    Thanks

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    if db2 zos support recursive query , you can try this:

    with t(n,parent) as (
    SELECT DISTINCT ROW 1,PARENT_REQUEST_ID FROM RIODS.REQUEST_HIERARCHY WHERE CHILD_REQUEST_ID = CHILD
    union all
    select DISTINCT ROW a.n+1, PARENT_REQUEST_ID from T a, RIODS.REQUEST_HIERARCHY b where b.CHILD_REQUEST_ID = a.parent
    fetch first 1 rows only
    ) select parent from t order by n desc fetch first 1 rows only

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Nested sets model instead

    Have you thought about using the nested sets model instead? Right now you are stuck in highly procedural code and are not using SQL as a declarative language. Google and try it.

  4. #4
    Join Date
    Nov 2012
    Location
    Zagreb, Croatia
    Posts
    11
    Hello all, thank you for your help. We found out - the problem here is that this version of DB2 for z/OS that we have does not support use of logic statements in UDFs - we have to wait for this until we pass to next version. We are now looking for other solutions, maybe writing functions in some other language, or stored procedure...

Tags for this Thread

Posting Permissions

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