In this session you will learn how to access UDTFs, how to pass parameters to them and how to join them together. You will get a lot of tips and tricks how to create and register external UDTFs for accessing data areas and IFS data.
Data located within tables or physical files can be accessed, filtered and accumulated by executing a SQL SELECT statement. Creating and using SQL views instead of joining the tables and accumulating the data manually or within the source code helps to mask complexity and to reduce the source code. But sometimes the request cannot be fulfilled with a single statement. For grouping multiple statements together some kind of program or function is needed. Creating and using User Defined Table Function (UDTFs) is one of the solutions. A UDTF can be written in either SQL or an HLL programming language (= external UDTFs) and must be specified in the FROM clause of a SELECT statement. Even though it is not possible to access non-database objects, such as data areas or user spaces directly with SQL, it is possible to perform the access within an HLL-Program and register this program as UDTFs. In conjunction with an external UDTF it is even possible to read data directly from the IFS.
Speaker: Birgitta Hauser, Toolmaker Advanced Efficiency GmbH
Session Handout: 25d SQL UDTF.pdf
Back to the sessions