Creating a UDSF written in Python

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

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.py file.

import nzae

class multiply(nzae.Ae):

    def _getFunctionResult(self, rows):
        x, y = rows
        if x is None:
            self.userError("first input column may not be null")
        return x * y

multiply.run()

Deployment

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

db2 "CREATE FUNCTION multiply_scalar(integer, integer) returns integer  LANGUAGE PYTHON  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.py'"

Running

Use this function in a query, for example:

db2 "values multiply_scalar(3,5)"

The following result is returned:

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