UDAF in a SQL query

After you register a UDAF in a Db2® instance, you can call the function in the same manner as any other SQL aggregate function.

For the sample PenMax aggregate function, first create a sample table that contains the data to be processed by the UDAF. For example:

CREATE TABLE myints (a int, b int);
INSERT INTO myints VALUES (1,2);
INSERT INTO myints VALUES (1,4);
INSERT INTO myints VALUES (1,6);
INSERT INTO myints VALUES (2,8);
INSERT INTO myints VALUES (2,10);
INSERT INTO myints VALUES (2,12);

Then, you can run the sample PenMax aggregate function, as follows:

SELECT penmax(b) FROM myints;

Sample output follows:

PENMAX
--------
10

Another example follows:

SELECT a,penmax(b) FROM myints GROUP BY a;

Sample output follows:

 A | PENMAX
---+--------
 1 | 4
 2 | 10