Hikari connections and active AS400 jobs

Question

I am using Hikari connection pool manager to query some tables in an AS400 machine.

I've set a minimum of 1 connection pool connections and a maximum of 10. I am querying 5 tables at the same time.

The problem is that despite using HikariDataSource getConnection() method before querying and using Connection close() method after each query, when I go to WRKACTJOB I see 10 active jobs, presumably one per connection until max connection pool connections are reached.

How do I get clear the unused connections\jobs?

Thank you

Answer 1

The point of a connection pool is to remove the overhead of establishing connections to the database. It does this by maintaining a "pool" of constantly alive connections, ready for use.

If your workloads are "spikey" with long periods of no activity, but occasional periods of lots of activity, I would suggest setting an idleTimeout of, for example, 1 minute (60000ms). If you set minimumIdle to 0 as well, then after your periodic workload completes, connections will all be closed within approximately one minute.

Answer 2

I want the connections to be removed from the connection pool after the connection is closed... I want those connections removed after the queries.

I understand. Don't use a Connection pool. By design, a connection pool will keep a pool of open (and available) Connection(s). For the behavior you want, use a regular unpooled DataSource.