Creating a UDAF written in Python

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

The following example creates an aggregate function called average that calculates the average of several integers.

Code

Add the following code to the $DB2_HOME/function/routine/average.py file.

import nzae

class AvgUda(nzae.Ae):

    def _initializeState(self):
        self.setState([0,0])

    def _accumulate(self, currentState, inputRow):
        sum = currentState[0] + inputRow[0]
        num = currentState[1] + 1
        self.setState([sum, num])
    def _merge(self, state, inputState):
        sum = state[0] + inputState[0]
        num = state[1] + inputState[1]
        self.setState([sum, num])
    def _finalResult(self, state):
        sum = state[0]
        num = state[1]
        return sum / num

AvgUda.run()

Deployment

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

db2 "CREATE FUNCTION average(integer) returns float aggregate with (integer, integer) language PYTHON parameter style \
NPSGENERIC  FENCED  NOT THREADSAFE  ALLOW PARALLEL  NO DBINFO  DETERMINISTIC \
NO EXTERNAL ACTION  CALLED ON NULL INPUT  NO SQL external name 'path_to_average.py' "

Running

It is assumed that there is a table 'test_int' with a single integer column 'i':

I          
-----------
          1
          2
          4
          8

The following result is returned:

1                       
------------------------
  +3.75000000000000E+000

  1 record(s) selected.