Creating a UDSF written in R

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

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

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_scalar(integer, integer) returns integer  LANGUAGE R  parameter style \ 
NPSGENERIC  FENCED  NOT THREADSAFE  NO FINAL CALL  ALLOW 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 "values multiply_scalar(3,5)"

The following result is returned:

1          
-----------
         15