TNS Names SQL Query operation repeating rows
So I opened up a ticket with HP support, but I was curious if anyone else has come across this.
I've used the TNS Names SQL Query operation quite a bit with no issues, but now I am running into problems where it will repeat rows, from the select statement, that it has already processed. I think it is because the operations that it is doing for each row takes quite a while (about an hour).
As an example, it will get to this operation and do a select statement that returns 5 rows. It will do the first, then the second, third, fourth, then back to first, second, third, etc. Then I get an error and the workflow crashes. The error is:
Error during run persistence: org.hibernate.exception.GenericJDBCException: could not update: [com.iconclude.dharma.model.execution.RunStepHistory#238562].
Just responding to my post with the solution, in case anyone else runs into this issue and finds this post.
There is a file jras.properties, in that file you can add the following:
com.iconclude.ras.session_timeout = <value in ms>
The default timeout for all RAS sessions is 30 minutes. the SQL Query operation uses the session to keep track of what row its on in the query. When this timout hits, the session closes, and it restarts the query from the begining the next time it hits that step.
Increasing this timeout from the default fixed the issue I was having.