Convert an SQL VARA to an EXEC VARA + SQL job.

Discussion created by Michael_Lowry on Aug 24, 2017
Latest reply on Sep 24, 2018 by Michael_Lowry

We have recently observed a few cases in which an SQLI query caused a work process to hang. SQL VARA objects run their queries inside a work process. Such queries occupy the WP 100% while they are running. If something goes wrong with the query — if for example it simply takes a very long time to run — this can lead to other problems. What we have seen is that after a while, a timeout is reached and the PWP gives up hearing back from the hung WP. If this happens to a few WPs, the overall stability and performance of the system can be put in jeopardy.

Because of this, we are reevaluating our use of SQL VARA objects. As a part of this, I have devised a way to replace an SQL VARA object with a combination of an EXEC VARA object and an SQL job. This way, the SQL query runs as an ordinary SQL job on an SQL agent. If something goes wrong with the query, it will affect only that job, and not the whole work process.

In broad strokes, it works like this:

  1. Instead of accessing an SQL VARA, the accessing object accesses an EXEC VARA.
  2. The EXEC VARA calls an SQL job, passing any parameters that are required.
  3. The SQL job creates a data sequence with the SQL results, and passes them back to the calling EXEC VARA.

This approach has several advantages over using SQL VARA objects

  • No risk of hanging a WP
  • Easier to debug SQL queries
  • Possible to set database details dynamically
  • Possible to handle errors gracefully (especially helpful when the VARA is used in a prompt set)

I have generalized this approach to make it applicable to many different situations. I will publish example objects soon.