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

The connections are open. That's what a connection pool does. — Elliott Frisch, Jun 18 '15 at 02:40
No, I want the connections to be removed from the connection pool after the connection is closed. I don't mind having 10 connections when the tables are being queryed. What I want is those connections removed after the queries — RedEagle, Jun 18 '15 at 02:45
Without seeing a copy of your WRKACTJOB results, it's hard to be sure of anything. (Screen text is better than a screen image.) From the minimal detail in your question, it sounds like all is as it should be. A connection does require a "job"; but seeing a job remain does not mean any connection exists. Normally, sites wouldn't want the jobs to end just because a connection closes. Other connections might re-use jobs so that full job-start/job-end doesn't happen just because a connection is requested. — user2338816, Jun 18 '15 at 02:56

score 3 · Accepted Answer · answered Jun 18 '15 at 10:07

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.

score 2 · Answer 2 · answered Jun 18 '15 at 02:54

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.

Hikari connections and active AS400 jobs

2 Answers2