Creating a UDTF written in Python

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

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.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_table(integer, integer) returns table (m integer) language PYTHON  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.py'"

Running

Use this function in a query, for example:

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

The following result is returned:

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