Compressing Integration Server's Apache Derby Embedded Database

This article pertains to customer installations with extremely large ‘db’ directories for the Integration Server’s Apache Derby embedded database. The shell script is supported for Linux installations of Integration Server 10.1 forward. At this time, this includes 10.1, 10.3, 10.5, 10.7, 10.11 and 10.15. There is no Windows bat file for this article.

The attached readme contains all the information required to run this script. Lastly, the attached shell script is appended with a ‘.txt’ file suffix, so it may be included as an attachment. Once downloaded, the script should be renamed by removing that suffix.

derby-compression-readme.pdf (240.3 KB)
derby-compression.sh.txt (8.1 KB)

Note: The IS data statistics collector used by the Admin UI dashboard introduced in 10.7 retains data captured for the past 30 days by default. Consider reducing this number to 7 or lower before compressing the data in the embedded database. The following steps should be performed if the number of days is changed:

First, reduce the number of days of retained data. This may be configured via the Admin UI’s dashboard configuration in version 10.11. For version 10.7, the configuration file must be directly edited in (installPath) /IntegrationServer/instances/ (instanceName) /config/monitor/monitor.cnf file. Edit ‘retentionDays’ property value with a value greater than or equal to 1.

Second, restart the data statistics collector via this service call.

http(s):// (host) : (port) /invoke/wm.server.datacollector:restartEngine

Third, initiate the data purge.

http(s):// (host) : (port) /invoke/wm.server.datacollector:deleteData

Depending on the amount of data, the ‘deleteData’ service may run for a while. Its activity can be monitored by setting the Integration Server’s log level for facility 0045 (Data Statistics) to ‘Debug’. The purge will be completed once these server.log messages cease to appear in the log.

‘… [ISS.0045.0064D] … Deleted the resource {0} data older than {1} from JDBC data source’

Once the purge completes, perform the Apache Derby compression, so it reclaims the disk files freed up from the deleted rows in the statistics data tables.

3 Likes

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.

Hello all, Starting with Derby 10.15.2, you must also download the ‘derbyshared.jar’ for that version too. The attached derby-compression.sh.txt’ script will include all jars that begin with ‘derby*’, so no more changes are required. Thanks to @weikhang.chew for reporting this.