Blog do projektu Open Source JavaHotel

wtorek, 29 maja 2018

DB2 and user-defined aggregate functions

Introduction
It is hard to believe but in DB2 there was not a feature to create user-defined aggregate function. It caused problems while migrating from Oracle to DB2 and to overcome them required sometimes a lot of effort.
Finally, this long-awaited option has been shipped in DB2 11.1. It is good news. But there also a bad news, it is very scanty documented. The only information I was able to find was an article in official DB2 Knowledge Centre.
So I decided to fill the gap and to create my own port of exemplary Oracle aggregate function.
The source code of Oracle UDF and DB2 equivalent is available here (db2aggr branch). Unfortunately, the custom aggregates are less flexible than standard and there is no way to apply additional argument, for instance, the delimiter character. This applies to DB2 and Oracle. In the example, hardcoded coma is used.
Aggregate function
The aggregate function is similar to standard LISTAGG function. But it also removes duplicates and sorts the result.
Assuming the table:
CREATE TABLE "TESTID" 
   "ID" VARCHAR2(20 BYTE)
  ); 
  INSERT INTO TESTID VALUES('G');  
  INSERT INTO TESTID VALUES('H');  
  INSERT INTO TESTID VALUES('A');
  INSERT INTO TESTID VALUES('B');
  INSERT INTO TESTID VALUES('C');
  INSERT INTO TESTID VALUES('C');
  
  INSERT INTO TESTID SELECT * FROM TESTID;
The expected result of running:
  SELECT LISTAGGDUPL(ID) FROM TESTID;
is:
A,B,C,G,H
Oracle
Oracle PL/SQL code of the aggregate function is available here. It simply collects values in the nested table and the final removing duplicates and sorting are done by running:

FOR Result IN ( SELECT DISTINCT O.COLUMN_VALUE FROM TABLE(self.gCollect) O ORDER BY 1 )

DB2
User-defined aggregate function in DB2 are Java or C++, there is no way to develop it in pure DB2 SQL/PL. So the implementation consists of two parts: Java code and SQL/PL code for defining the signature and deployment.
  • Java project is accessible here. That is very simple and straightforward, no comments are required.
  • SQL/PL code is available here.
  • Test code, the same for Oracle and DB2.
Additional remarks
  • In order to run the function in DB2 it is recommended to increase the Java heap space. Example: db2 update dbm cfg using JAVA_HEAP_SZ 76800
  • I was unable to trigger "MERGE PROCEDURE" phase. So I'm not sure of the Java code implementing it is valid.
  • During the test, I discovered that INITIALIZE and FINALIZE code is run twice, I do not understand why. 

Brak komentarzy:

Prześlij komentarz