Support Article
Agent for extraction to the external tables fails
SA-36362
Summary
User has a daily agent that runs an activity that connects to a external database and extracts all the details in that database. Database connections are established through the Pega Database rule with Connection type JDBC URL.
Errors occur whenever the external database is rebooted. After the database server reboot has finished, the agent fails with ResourceUnavailableException.
If the agent activity is run manually, no issue occur.
Error Messages
java.sql.SQLRecoverableException: Io exception: Connection reset
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:458)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1373)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.execute(DatabasePreparedStatementImpl.java:576)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:6003)
Steps to Reproduce
Consider the following steps as an example:
- Run the agent StartReporting on one day at 8 p.m. EST.
- Reboot the server on the next day between 12 a.m. to 6 p.m. EST. The server reboot finishes at 5 p.m. EST.
- Run the agent at 8 p.m. EST on the same day to see the server reboot failure.
Root Cause
A defect or configuration issue in the operating environment
When the agent is enabled and run for the first time, the connection to the database is established. Because this is not a connection pool when the agent is run again, it gets the same database connection that is no longer valid because the database has been rebooted.
Resolution
Make the following change to the operating environment:
Use a data source instead of a URL connection to leverage the connection validation and pooling functionality of the application server.
Published May 1, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.