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. 

Civilization The Board Game, next version

Introduction
I deployed a new version of my computer implementation of  Civilization The Board Game. The implementation consists of three parts:

• Civilization Engine 
• Civilization Web Interface
• Demo. Demo version is deployed to Heroku, free quota. Please wait several minutes unless the dyno is brought back to life.

New features implemented 

• Culture track
• Spend culture tokens to advance a culture level
• Devote city to arts
• MetalWorking
• Code Of Laws
• Currency
• Irrigation
• Navigation

Devote city to arts

The player can choose a city to devote it to arts to collect culture tokens. Culture tokens can be also sent from scout or scouts occupying an appropriate square.

Of course, the noble sacrifice is not in vain, it is piled in the resource panel.

Advance culture level, culture track

If the player stocks enough culture tokens can spend them to advance culture.

The cost depends on the culture level, in the beginning, it is 3 culture tokens. After spending culture, the player is climbing up one step on the culture ladder.

The player can advance only once using the dialog window but it can be repeated several times in the same City Management phase.

Important: the current implementation supports only marking the progress on the culture track. The culture cards, great persons, and culture victory are still pending.

MetalWorking technology

During the battle, the iron token can be used to increase the attack strength. Also, iron harvested from hut and villages can be consumed. The current implementation spends firstly iron token. If the iron token is not gathered, the hut or village is used. In the future implementation, the player will have a choice which iron to spend. 

Code Of Law technology

After winning the battle, the economy is increased and a coin is added to the technology card.

Currency technology

The player can spend incense to gain three culture tokens. Explicit incense token can be used or incense collected from hut or village. The player has an option to point it.

Irrigation technology

Irrigation allows building the third city. Research this technology as quickly as you can, it is the must in this game.

Navigation technology

After researching this technology, the player can cross the water but cannot stop in it. It is also the must in water covered area.

Next steps

• Great persons
• More technologies