Creating a UDTF written in R

The example in this section shows how to create, on a Db2® system, a user-defined table function (UDTF) written in R.

The following example creates a scalar function called multiply_table that multiplies two numbers and returns the result as a table.

Code

Add the following code to the $DB2_HOME/function/routine/multiply.R file.

nz.fun <- function() {
  while (getNext()) {
    x<-getInputColumn(0)
    y<-getInputColumn(1)
    setOutput(0,x*y)
    outputResult()
  }
}

Deployment

Deploy the UDX to a Db2 instance by issuing the following command, where path_to_multiply.R is to be replaced by the fully qualified path to the multiply.R file.

     
db2 "CREATE FUNCTION multiply_table(integer, integer) returns table (m integer) language R parameter style \
NPSGENERIC  FENCED  NOT THREADSAFE  NO FINAL CALL  DISALLOW PARALLEL  NO DBINFO  DETERMINISTIC \
NO EXTERNAL ACTION RETURNS NULL ON NULL INPUT  NO SQL external name 'path_to_multiply.R'"

Running

Use this function in a query, for example:

db2 "select * from table(multiply_table(3,5))"

The following result is returned:

M          
-----------
         15