Using generic table functions

Use generic table functions when you have to produce different customized result sets based on different unstructured data inputs. For example, you might have data stored in CSV (comma-separated values) files, with different schemas.

1. Create a Java class that takes the CSV file handle as input, reads the file and returns rows of data to the database.
   A sample Java file with a simple CSV reader is provided in the following location:

   • For UNIX or Linux®: sqllib/samples/java/jdbc/UDFcsvReader.java
   • For Windows: sqllib\samples\java\jdbc\UDFcsvReader.java
2. Compile the external Java routine.
3. Copy the class file to the following location: $INSTALLDIR/sqllib/function.
4. Register the Java function in the database.

   CREATE FUNCTION CSVREAD
                   (
                     filename VARCHAR(255)
                   )
                   RETURNS GENERIC TABLE
                   EXTERNAL NAME 'UDFcsvReader!csvReadString'
                   LANGUAGE JAVA
                   PARAMETER STYLE DB2GENERAL
                   VARIANT
                   FENCED THREADSAFE
                   DISALLOW PARALLEL
                   NO DBINFO;

SELECT  csv.name, csv.department, csv.id 
	from T1, TABLE (CSVREAD( '~/csvfiles/file_1')) 
	AS csv (name varchar(128), department varchar(128), id int)
	where T1.ID = csv.id;