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:
  1. Run the agent StartReporting on one day at 8 p.m. EST.
  2. 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.
  3. 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 April 11, 2017 - Updated May 1, 2017

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.