Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    10

    Unanswered: How to write external tale functions in DB2 for Z/OS

    Can we write the table functions in SQL?If no,then How to write external tale functions in DB2 for Z/OS.

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by sheela562
    Can we write the table functions in SQL?If no,then How to write external tale functions in DB2 for Z/OS.
    DB2/zos don't support table functions in SQL. It does support external table function.
    The sql syntax is(please reference the SQL reference book for detail)
    CREATE FUNCTION TUDFS(INPUT1 VARCHAR(1))
    PARAMETER STYLE DB2SQL
    FENCED
    RETURNS TABLE (C1 DEC(5,0) ,
    C2 VARCHAR(5),
    C3 INT)
    LANGUAGE PLI
    DETERMINISTIC
    NO SQL
    EXTERNAL NAME "TUDFS"
    SPECIFIC "TUDFS"
    NO EXTERNAL ACTION
    SCRATCHPAD 100
    DBINFO
    FINAL CALL
    DISALLOW PARALLEL
    WLM ENVIRONMENT WLMENV3
    PROGRAM TYPE MAIN
    CARDINALITY 30;


    SELECT C1 FROM TABLE(TUDFS('A')) X ;

    You still have to write your own application to support the table function. the above example will need an PLI application with name of 'TUDFS' to execute.

    Just FYI, it is not easy to write an application for table function. Good luck.

    nidm

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nidm
    Just FYI, it is not easy to write an application for table function.
    I don't understand this part. A table function returns a table. When you have a table, you simply query it - like any other table. So writing an application that retrieves data from a table function is straight-forward.

    Writing a table function itself is not complicated either. The only thing you have to remember is that a table function acts like an iterator. Thus, you have to know that DB2 calls the function multiple times and fetches a single record upon each call - until the function returns SQLSTATE '02000' (end of table).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Apr 2007
    Posts
    10
    As you said it is easy to write a table function to fetch data directly from a table.Iterative common table expression was used to implement the str2tbl function in DB2 for Windows.
    We want to implement the Str2tbl function as an external function in C.How to implement str2tbl as an external function in C for Z/OS

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by sheela562
    As you said it is easy to write a table function to fetch data directly from a table.
    I didn't say that. My point was that a table function returns, well, a table. Accessing and working that table is no different from any other table (except for the "TABLE ( func() )" syntax).

    How to implement str2tbl as an external function in C for Z/OS.
    What exactly is the problem? You have to write an iterator that receives a string(?) as input. Then, upon each call to the table function, you traverse along the string and return the next sequence. The information how far you progressed can be stored on the scratchpad between function calls.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by stolze
    I don't understand this part. A table function returns a table. When you have a table, you simply query it - like any other table. So writing an application that retrieves data from a table function is straight-forward.
    ..
    Please refer to the original context. I mean "it is not easy to write an application for the table udf", the one to support the UDF. But you mean "the the one to query/use table udf". I already provided a query to use the table udf.

    To write an application to support the table UDF. The programer need to understand something like result set, which is harder.

    Whether a thing is complicated is always a relative term. The table UDF is much more complex, comparing to sql scalar udf, source udf or external scalar udf.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nidm
    Please refer to the original context. I mean "it is not easy to write an application for the table udf", the one to support the UDF. But you mean "the the one to query/use table udf". I already provided a query to use the table udf.

    To write an application to support the table UDF. The programer need to understand something like result set, which is harder.
    I wasn't sure what you were referring to. That's why I tried to explain both sides, i.e. querying the results of a table function and implementing a table function.

    (I consider it as a fundamental requirement for every programmer to know what an iterator does. Cursors over result sets are just that: iterators. That's why I was asking for specific problems to implement a table function.)

    Whether a thing is complicated is always a relative term. The table UDF is much more complex, comparing to sql scalar udf, source udf or external scalar udf.
    I wouldn't say that. External scalar UDFs also support the FINAL CALL clause, which leads to different calltypes being passed to the function by DB2 and you can also have a scratchpad. From there, the step to table functions in rather marginal, i.e. you have multiple output parameters instead of only one. Granted, many people don't use scalar functions to their full power and stick with a simpler implementation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2007
    Posts
    10
    Thanks for the reply Stolze.
    I found the below article written by you on "Parsing strings in SQL".
    http://www.ibm.com/developerworks/db...03stolze1.html
    It is working as expected in db2 for Windows.
    I want to develop an equivalent for elemIdx() and elements() functions as an external function in C for DB2-Z/OS.
    Can you please provide an example for implementing external table functions
    in C?

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What have you tried so far and where do you get stuck?

    p.s: If you are already using C code, you could put both functions "elemIdx" and "elements" into the same table 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
  •