If the database server times out a connection, the connection pool on Tomcat side would not be aware of its disconnection, and could throw the following exception in Tomcat:
Caused by: com.ibm.db2.jcc.c.DisconnectException: A communication error has been detected. Communication protocol being used: Reply.fill(). Communication API being used: InputStream.read(). Location where the error was detected: Read timed out. Communication function detecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS DB2ConnectionCorrelator: CCD3560A.K404.080507130918 at com.ibm.db2.jcc.b.a.a(a.java:373) at com.ibm.db2.jcc.b.gb.b(gb.java:191) at com.ibm.db2.jcc.b.gb.c(gb.java:238) at com.ibm.db2.jcc.b.gb.c(gb.java:353) at com.ibm.db2.jcc.b.gb.v(gb.java:1362)
In order to prevent the exception, the connection pool can validate the connection.
In doing so, two attributes can be added to the JDBC resource, testOnBorrow and validationQuery respectively:
name="jdbc/dsSos" auth="Container" type="javax.sql.DataSource" username="******" password="*******" driverClassName="com.ibm.db2.jcc.DB2Driver" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" maxIdle="2" maxWait="10000" url="jdbc:db2://mydbURL:50000/MYDB" maxActive="15" removeAbandoned="true" removeAbandonedTimeout="300" testOnBorrow="true" validationQuery="some sql..." logAbandoned="true" "some sql..."
In the above example should be a valid SQL query that the connection pool will execute to ensure a live connection is returned to the application. The embedded query should be simple and efficient. For example:
For Oracle:
validationQuery="select 1 from dual"
For MySQL:
validationQuery="select 1"
