Issue description

If a job runs for a long time (more than 30 minutes), the connection to the database can timeout.

  • You might get the error "Failed to Obtain Connection from Datasource":
  • Or you might see this error message with SQL exception "Connection has already been closed":

Solution

1. Access the semarchy.xml file.

2. The first step is to turn on logging for datasource. Add logValidationErrors="true" to your JNDI connection configuration (<Resource> element for the concerned database). It will help with troubleshooting.

3. Connection timeout generally happens because there is no validation query on datasources. To add such a query, add validationQuery="select 1 from dual" to all <Resource> elements where it is not yet configured. This can solve the issue.

4. Also, remove the following parameters from resources declaration:removeAbandoned="true" logAbandoned="true" removeAbandonedTimeout="1800"
These settings are OK for development environments but can be dangerous for production as they will close long-running connections.

5. As you have a lot of datasources to the same database you can also remove the parameter to avoid creating too many unused connections.