Db2 table function operators

Table function operators invoke predefined Db2 table functions. A table function is a logical database object that returns a table (one or more rows) based on a statement expressed in a specific language, such as SQL, C, or Java. A table function that uses an SQL statement to return its table is similar to a view.

In the context of a data flow, a table function produces a subquery in the generated code. This subquery returns a result set that can be joined to other tables in the flow. You can use table functions to define all kinds of SQL queries or programmatic requests for data from external sources. Like custom operators, table functions are powerful and flexible data transformation tools.

For example, the following syntax defines a Db2 table function that selects aggregated sales figures from the SLS_SALES_FACT table in the GOSALESDW schema:

Create function salesfunc (PRODUCT_KEY int, RETAILER_KEY int)
returns table (PRODUCT_KEY int, RETAILER_KEY int, SALE_TOTAL dec(7,2))
language sql reads sql data no external action deterministic
return select PRODUCT_KEY, RETAILER_KEY, sum(SALE_TOTAL) from GOSALESDW.SLS_SALES_FACT group by PRODUCT_KEY, RETAILER_KEY; 

This table function has two input parameters: the columns PRODUCT_KEY and RETAILER_KEY. Table function parameters represent inputs to the evaluation of the function's query; the query is processed with reference to these columns or expressions. For example, the following query joins the results of the table function to the SLS_PRODUCT_DIM and SLS_RTL_DIM dimensions:

select t1.PRODUCT_KEY, t1.RETAILER_KEY, t1.SALE_TOTAL
from GOSALESDW.SLS_PRODUCT_DIM, GOSALESDW.SLS_RTL_DIM, table (salesfunc(GOSALESDW.SLS_PRODUCT_DIM.PRODUCT_KEY,GOSALESDW.SLS_RTL_DIM.RETAILER_KEY)) as t1
where GOSALESDW.SLS_PRODUCT_DIM.PRODUCT_KEY = t1.PRODUCT_KEY and GOSALESDW.SLS_RTL_DIM.RETAILER_KEY = t1.RETAILER_KEY;

The dimension table rows are joined to each row that the table function returns, based on matching PRODUCT_KEY and RETAILER_KEY values. This processing emulates the behavior of a correlated subquery.