There is a way to make the IS to test a Oracle connection to check if it is alive or dead, and in case of a closed connection, to reconnect it again?

We’re getting some “java.sql.SQLException: Io exception: Connection reset” when we try to run a service the first time in a day. We have found that it is caused by the Oracle’s connection timeout. This is ok, but trying to repeat the operation again, the error persists.

Someone has suggested us to make a job that just “SELECT SYSDATE FROM DUAL” periodically, but this seens a very poor solution.

The solution with running dummy sql statement against database is employed by weblogic connection pool implementation to test against stalled connections. So I would not call it poor in general.
It’s poor in case of IS since we have no access to internals of the pool. Even if you schedule it as a job you are not guaranteed to iterate over all available connections from the pool. So your periodic call may actually check just few of many connections from the pool.
Moreover, in case connection got broken you can’t recover anyway - you only can prevent timeout on Oracle side - but it’s just one of many reasons connection may go bad.
I don’t know of JDBC Adapter in 6.0.1 dealing with this problem. It could be in 6.1…

We have found a workaround for this case: just set the ‘Minimum pool size’ to 0 (zero) and ‘Pool increment size’ to the minimum size we needed. After configuring this is this way, the error has gone away.

Researching some documents at advantage.webmethods.com, I had found a Troubleshooting guide (TS_Advanced_Troubleshooting_Guide.pdf) that says (page 28, 2nd paragraph) that ‘when using WmDB’ (which is not out case) the program (or flow) has to call pub.db:close to close the connection and return it to the pool. Failing to do that, the connection would be lost.

In our case, we are not using such package (wmDB), but we’re using the JDBC Adapter with a connection pool. In our case we should do the same (call pub.db:close or something similar)?

Feng - With JDBC Adapter, look in the JDBC_Adapter_Users_Guide_6.0.x in Chapter 3 regarding connection config. In your case, the Expire Timeout parameter will be of interest as this is what determines how long a connection to the db will remain active before it is removed from the pool.

As far as your flow service is concerned, you’re right that there is no pub.db:close with the JDBC Adapter. What you have to be careful about is writing your service with respect to explicit vs. implicit transactions (see Appendix B of users guide).