DB2 Version 10.1 for Linux, UNIX, and Windows

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.

About this task

If you want to join data that is stored in relational tables in a DB2® database with data stored in a CSV file, then you must use a generic Java™ table function to read and parse the data.

Procedure

Create a Java class which takes the CSV file handle as input, reads the file and returns rows of data to DB2. 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
Compile the external Java routine.
Copy the class file to the following location: $INSTALLDIR/sqllib/function.

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;

What to do next

You can now use the new Java function to analyze your data. For example, issue the following SELECT command:

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;