Cleanup Service - Improvements for connection handling

Current Situation

• The Cleanup Service consumes 10 connections from an individual connection pool.. The size of the connection pool is programmatically determined.
  • JS7 consumes all in all 25 connections.
  • The Cleanup Service establishes connections on startup of JOC Cockpit. The connections remain in place for the lifetime of JOC Cockpit.
• Some DBMS products by default limit the number of connections per user account and process. JOC Cockpit is running from a single process.
  • Oracle by default limits the connections according to the formula: (1.5 * number of processes) + 22
  • This allows up to 24 connections, whereas JOC Cockpit can consume up to 25 connections. As a result connections can become unavailable, for example with the JOC Cockpit Cluster Service (even in a situation when a single JOC Cockpit instance is used).

Desired Behavior

• The Cleanup Service consumes a minimum of 5 connections. The maximum number can be configured from the Settings page (section cleanup -> max_pool_size, default: 5).
  • For environments with a larger number of daily task executions it is recommended to increase this value to 10 or more.
  • Users who increase this value should adjust related server settings in their DBMS to allow the overall number of connections as specified by the JS7 - Database article.
• The Cleanup Service will establish the required database connection when starting to run for the period specified with its respective settings. The Cleanup Service will disconnect from the DBMS on completion of its run.
  • This is intended to use DBMS connections only for the period when the Cleanup Service is actively purging the database.

Test Instructions

• for MySQL DBMS
  • Use MySQL Workbench und go to Administration -> Client Connections:
    • Refresh and count connections of JS7 database user.
  • JOC-Cockpit
    • Settings -> cleanup
      • increase the max_pool_size setting e.g. to 10 and set perid_begin in next 2-3 minutes
  • Switch to MySQL Workbench and wait for the period_begin start time.
    • Refresh this view every n seconds
    • Expected result: the number of connections should be increased for the execution time of the Cleanup Service and then decreased again.
  • Check the service-cleanup.log file to find any errors.