HikariCP not able to reconnect to AS400 after connections are killed

[abhishek2bommakanti]

Environment

HikariCP version: 3.4.1
JDK version     : 1.8.0_144
Database        : AS400 / DB2
Driver version  : net.sf.jt400:jt400-jdk8:10.1

---

We have a Spring Boot (2.1.6) application that uses HikariCP and the JT400 driver to connect to an AS400 database. In production, we have a job that kills all connections to the AS400 every Sunday at 2am. After this, the app loses all connections to the database and refuses to reconnect until we restart the service.

as400-cp - Failed to validate connection S1041273 (Communication link failure. (Connection was dropped unexpectedly.))

as400-cp - Connection S1041273 marked as broken because of SQLSTATE(08003), ErrorCode(-99999)
java.sql.SQLException: The connection does not exist.
	at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:836)
	at com.ibm.as400.access.JDError.throwSQLException(JDError.java:421)
	at com.ibm.as400.access.AS400JDBCConnection.checkOpen(AS400JDBCConnection.java:498)
	at com.ibm.as400.access.AS400JDBCConnection.send(AS400JDBCConnection.java:2736)
	at com.ibm.as400.access.AS400JDBCStatement.close(AS400JDBCStatement.java:541)
	at com.ibm.as400.access.AS400JDBCPreparedStatement.close(AS400JDBCPreparedStatement.java:683)
	at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:364)
	at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2209)
	at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:2008)
	at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:311)
	at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1984)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1914)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
	at org.hibernate.loader.Loader.doQuery(Loader.java:937)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
	at org.hibernate.loader.Loader.doList(Loader.java:2689)
	at org.hibernate.loader.Loader.doList(Loader.java:2672)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
	at org.hibernate.loader.Loader.list(Loader.java:2501)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
	at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2223)
	at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069)
	at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170)
	at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)

Can you please let us know how to configure the application so that it auto-reconnects to the DB instead of us having to manually restart it?

[dandesybsb]

I'm having the exact same error under different circumstances. In my case, I have a long running process that repeats a query (with different parameters) 45000 times. After about 25 or 30000 iteration, I start to get jdbc.spi.SqlExceptionHelper : Connection is closed immedaitely followed by jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: null.

After several of them, then I get the exact exception listed in the OP. I'm pretty sure that my pool is wrongly configured. What's the secret?

[CodeDead]

Same problem and has been a problem for years now, unfortunately. We have to schedule entire service restarts to mitigate this.