We use JTopen version 7.8 in production to run SQL queries on an iBMI server from a Tomcat server hosted on a LINUX virtual machine.
We encountered on 3 occasions a failure that we cannot explain.
Our JDBC connection pool is overloaded (120 connections) and threads seem to be waiting on com.ibm.as400.access.AS400ThreadedServer.receive(AS400ThreadedServer.java:339).
A SQL query was targeted in particular but after optimizing it (<50ms), the problem is still present.
We suspected a lock on the iBMI server on one of the files used by the targeted stored procedure.
A test with an exclusive lock shows that JTOpen returns an error to the Java code and that the pool behaves well.
Restarting the Tomcat instance results in the deletion of QZDASOINIT jobs which saturate the iBMI server and the whole system restarts fine !
We have read in the JTOpen documentation that it is possible to use parameters to manage a timeOut on the SQL query:
"jdbc:as400://xxx.xxx.xxx.xxx;thread used=false;socket timeout=6;login timeout=6"
Have you ever tried these settings? What about performance and robustness?
Is it possible to use timeouts on SQL queries? Does the cancellation result in the cancellation of the QZDASOINIT jobs on the iBMI server as a result?
It seems to be possible since version 7.8, do we have any feedback on this option?
Thanks for your help
We expect to find out why our iBmi server is hanging;