Automic Workload Automation

Expand all | Collapse all

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

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

    Posted 08-24-2017 10:37 AM

    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.



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

    Posted 08-25-2017 04:53 AM
      |   view attached
    Here are the example objects. You’ll have to customize them a bit to make them work in your environment.
    1. Update UC0.MAL.EXEC_VARA_EXAMPLE.LIST_SERVER_PROCESSES.UC4_SYSTEMS.VARA_STATIC so that it contains a list of your AE systems.
    2. Update the pre-process of UC0.MAL.EXEC_VARA_EXAMPLE.LIST_SERVER_PROCESSES.JOBS_SQL so that it sets the client number and connection object name correctly for your environment.
    3. Make sure that the referenced connection object(s) exist and work correctly. These connection objects should be able to read (SELECT) from your respective AE system(s)’ DB(s). There should be one connection object for each AE system you defined in the static VARA.
    Once you’ve done these preliminary set-up steps, you should be able to run the workflow. Select a system name from the prompt set, and the report of the script should contain a list of the chosen system’s work processes.

    This is just an example of course. The idea is that you can use the EXEC VARA anywhere you would normally use an SQL VARA:
    • Prompt set data sources
    • Data source for FOREACH workflows
    • Script/object variables, via GET_VAR
    • Curly-brace-style VARA object references — {VARA,KEY,COLUMN} — can be used lots of places like object attributes, task conditions, and prompt set defaults
    • etc.
    Because the VARA calls a normal executable object, you have a lot more flexibility than you would with an ordinary SQL VARA. Enjoy!

    Attachment(s)



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

    Posted 12-15-2017 06:30 AM
    it works - Thanks Michael Lowry           


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

    Posted 02-01-2018 04:59 AM

    You can also use this approach to replace a BACKEND or FILELIST VARA with a JOBS + EXEC combo. It offers a lot more flexibility, such as the ability to use an agent group or to pick the agent dynamically.



  • 5.  Re: Convert an SQL VARA to an EXEC VARA + SQL job.

    Posted 08-14-2018 10:17 AM

    Here is an idea for a way to improve this EXEC VARA wrapper for SQL jobs. The EXEC VARA should automatically cache data retrieved from the remote SQL DB in a local static VARA, and use the cached data instead of querying the remote DB in the following situations:

    • The cached data were retrieved recently (e.g., within the last hour); or
    • The SQL agent is down or the remote DB is unavailable.

     

    This would improve the speed & reliability of queries. Obviously it apples to SELECT (read) statements only, and makes sense only for data that are changed infrequently.

     

    I don’t have time to work on this now, but I at least wanted to post a rough description here in case anyone else might want to take a stab at it.



  • 6.  Re: Convert an SQL VARA to an EXEC VARA + SQL job.

    Posted 09-24-2018 08:30 AM

    Here’s another advantage of using an EXEC VARA wrapper:

    • Resolve agent group & check whether the agent is up or not. Optionally return any error messages encountered in the results data set. This is useful when the VARA is used in a prompt set, either as an element data source or element default value. It prevents the prompt set from hanging if, for example, the agent needed to generate a pick list is not running.