Before I embark on a quest which ultimately proves hopeless, I'd like to ask those with greater experience...
Is it possible to create a user defined aggregate function for DB2 without privileges to upload compiled files..? This is for DB2 for AS400 on iSeries v7r3m0.
My first thought was to create the UDAF as a normal SQL function via my SQL client DBeaver 5.2.5. I've created regular UDFs this way before, however I'm not sure if an aggregate function can be written this way. I have yet to find any examples online, and this one IBM document isn't for my version.
If this cannot be done in SQL, then the next option is JAVA. My skills there are basic yet sufficient to explore the possibility, but it'd be my first time doing so on DB2. What may be the major roadblock there is in my understanding that JAVA functions for DB2 require uploading compiled files to the server, but I do not have privileges to do this. Unless I'm incorrect on this point..?
My next thought was whether JAVA functions can be written and compiled on the server..? For instance, can I write JAVA code in a script window of my SQL client, so when I run the script, the JAVA is compiled by the server, and then stored on the server..? Or perhaps is there's a JAVA development client like IBM Data Studio which connects to the server just the same..?
Or...something..? Any suggestions would be greatly appreciated.
I'll explain the UDAF I wish to create. It would be similar to any built-in UDAF like SUM(), but it would be character-based, and its name would be GetCommonWords. The input would be a table column of type CHAR(n). The output would also be CHAR(n), with a string of words that are common to the beginning of all records.
Consider the following phrases for the column "FOOD":
The usage would be GetCommonWords(Food), and the result would be the words common to all: peanut butter.
Db2 for i does not have a CREATE FUNCTION (aggregate) statement even in the current 7.4 release.
https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/db2/rbafzcreatef.htm
You should know that Db2 for i, Db2 for z and Db2 (for LUW) are essentially separate products, and although there is certain amount of common syntax and features, you do need to look at manual pages (and web articles etc) for your particular platform (and ideally version level)