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.