Generic table functions

A generic table function is a table UDF where the output table is not specified when the UDF is defined. Instead, the output table is specified when the UDF is referenced. Different output table size and shape are possible for the same generic table function, depending on different input arguments.

You can create generic table functions with the Java™ programming language.

To define a generic table function, use the CREATE FUNCTION statement, and specify the RETURNS GENERIC TABLE option. To use this option, you must specify the LANGUAGE JAVA and PARAMETER STYLE DB2GENERAL options.

In the following example, the names and types of output columns are not specified:

CREATE FUNCTION csvRead (VARCHAR(255))
RETURNS GENERIC TABLE
EXTERNAL NAME 'UDFcsvReader!csvReadString'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL

Once the function is defined, you can access the function output using an SQL select statement, which includes a typed correlation clause. In the following example, the SELECT statement is used to indicate that the table contains two columns: USER, which is an INTEGER data type, and LINK, which is a VARCHAR(100) data type.

Select TX.*
From TABLE (csvRead('/TMP/data/userWebClicks.log'))
     AS TX (USER INTEGER, LINK VARCHAR(100))
WHERE TX.LINK LIKE 'www.ibm.com%'

You can use another SELECT statement to access the output from the same generic table function. In the following example, the SELECT statement is used to indicate that this time the table contains three different columns: CUSTOMERID, which is an INTEGER data type; NAME, which is a VARCHAR(100) data type; and ADDRESS, which is a VARCHAR(100) data type.

Select TX.*
From TABLE (csvRead('/TMP/data/customerWebClicks.log')) 
     AS TX (CUSTOMERID INTEGER, NAME VARCHAR(100), ADDRESS VARCHAR(100))'