We are using DB2 on cloud instance with DR node. Application use hikari pool for connection pooling implementation. While this works fine with the primary active, we fails to maintain the pool once the primary goes down and DR is enabled. DB2 ACR feature allow us to define alternate server configuration as part of the JDBC URL and, DB2 driver redirect connection to alternate server once the primary goes down. I am looking to understand how Hikari client behave to this change. While testing this, we found that with current configuration of Hikari, following error is received on DR enablement.
Dec 4 21:09:12 cloudfoundry xsmf-hclgit-prod WARN HikariPool-1 - Failed to validate connection com.ibm.db2.jcc.t4.b@6817a942 ([jcc][t4][10335][10366][4.25.13] Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003). Possibly consider using a shorter maxLifetime value. Dec 4 21:09:12 cloudfoundry xsmf-hclgit-prod WARN HikariPool-1 - Failed to validate connection com.ibm.db2.jcc.t4.b@24477f6 ([jcc][t4][10335][10366][4.25.13] Invalid operation: Connection is closed. ERRORCODE=-4470, SQLSTATE=08003). Possibly consider using a shorter maxLifetime value.
The -4470 code from server actually means that connection is closed from the server side (this is the correct behaviour from DB2 side as primary server goes down, the existing connection will throw this error). I was of an understanding, with this error, hikari pool should refresh the connections and on new request, DB2 driver will take care that it get the connection with alternate server now (instead of primary). However his does not seems to be working that way as Hikari seems to be continuing using the connection which are closed from the server side. This result into Hikari not getting any active connection and start timig out.
Any pointer of help to make this working together (Hikari pool refresh + DB2 ACR to alternate server) will be really helpful.
For your understanding, i am also sharing the hikari pool property which define the alternate server as part of the URL
datasource: url: jdbc:db2://<primary server name>:50001/BLUDB:sslConnection=true;enableClientAffinitiesList=1;affinityFailbackInterval=120;clientRerouteAlternateServerName=<Alternate Server Name>;clientRerouteAlternatePortNumber=50001,50001;enableSeamlessFailover=1;maxRetriesForClientReroute=3;retryIntervalForClientReroute=2; username: ${DB2_USERNAME} password: ${DB2_PASSWORD} driver-class-name: com.ibm.db2.jcc.DB2Driver type: com.zaxxer.hikari.HikariDataSource hikari: connection-timeout: 600000 maximum-pool-size: 100 max-lifetime: 2000000 validation-timeout: 5000 data-source-properties: cachePrepStmt: true
Post the above error (connection closed), i also see the below logs on new connection requests
ERR org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 600002ms. Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:612) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:768) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at com.hcl.service.dao.UserDaoImpl.getAuthorizationClaims(UserDaoImpl.java:3272) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at com.hcl.service.dao.UserDaoImpl$$FastClassBySpringCGLIB$$eefb69c8.invoke(<generated>) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771) Dec 4 21:19:12 cloudfoundry xsmf-hclgit-prod ERR at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)