Long running stored procedure

Ignition
1

I have a stored procedure that (unfortunately) takes longer than 60 seconds to run.
On the upside, I only need to run it once a day.

I would like to set it up as a gateway timer or scheduled task to run once a day.

But because it takes longer than 60s, it errors out and it appears that the Gateway resets the DB connection.

Any suggestions? (Short of modifying the SP to run faster.)

Thanks.
-Shane

2

Any thoughts on how to change the connection timeout for system.db calls?
Or maybe another mechanism I could use?

Even calling system.db.runPrepUpdate with a tx created with beginTransaction and a large timeout doesn't seem to work. 60s seems to be the limit.

Thanks.
-Shane

3

I think you are up against the hard-coded 60-second Vision to Gateway timeout... but I'm not sure.

Your first step is to figure out what is going on with that stored procedure. Maybe the tables are missing indexes that will help speed things up.

4

Thanks for the response.

Unfortunately, this is from a legacy system that we are slowly modernizing, so modifying the SP isn't possible (hence my caveat in the original post).

You make a good point though, I should try running this completely from a Gateway context to make sure none of the built in timeouts or restrictions from the designer/console/etc. are coming into play.

1 Like
5

Is this something that queries data from tables or inserts data into the database?
If it queries you will probably need to do a sendMessage both ways, one to the gateway where the payload has the target client node that does the stored procedure, and another from the gateway to the target client node with the data after it is done.

6

If it only runs once a day, I would think placing the results/status/etc into gateway memory tags would suffice for all consumers.

7

Missed that part... yep. 100%. Now more coffee.

8

From what I read, the OP doesn't mention they need any results from the stored procedure - if that is the case and it just needs to run once a day, why not move it out of the Ignition space and just use a SQL agent job or something similar? That would be very easy to manage and not interfere with Ignition...

1 Like
9

Ok, so I set it up to call system.db.runPrepUpdate() on the stored procedure from a scheduled gateway event, and at the 60s mark it timed out and reset the DB connection. This was the exception returned:

java.sql.SQLRecoverableException: Io exception: Socket read timed out

I had hoped running it solely in the gateway context would allow it to go longer than 60s.

I don't need any results from the stored procedure.
I can't modify the stored procedure as it's part of a legacy system that's being converted.

Any thoughts?

10

That sure looks like a JDBC driver error. Look for a newer version (if you can) or an extra connection property that could modify its behavior.

1 Like
11

Opened a ticket. Ignition support says the 60 second timeout is built in to the Oracle JDBC driver and can not be modified / extended.

12

You're going to have to run it using Oracle scheduling in the DB, then. :man_shrugging:

1 Like