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

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).

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

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).

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.

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.

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.)

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.

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?

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.