Automic Workload Automation

 View Only
  • 1.  VARA.EXEC and Parameters

    Posted Jun 24, 2020 09:54 AM
    Edited by Kyle Harlow Jun 24, 2020 10:04 AM
    Good day

    I have tried the following on both of our Test (12.3.2) and Prod (12.3.1-HF1) environments. 

    I am trying to create a VARA.EXEC that runs a SQL job* (I've also tested this with a Windows job).  Adding parameters doesn't seem to be working for me. I'm assuming the problem is somewhere between the keyboard and chair (and right in front of my face if history is anything to go by).
    *We have the misfortune of having a split of MSSQL and Oracle databases, and a couple of other odds and ends due to COTS application restrictions and what not. It is my understanding that DB_SERVICE drives VARA.SQL and can only use one provider. So while this works great for our MSSQL databases, this does not work for other providers. 

    Edit: Forgot to add that the jobs are configured to inherit variables from their parents. 

    Here is what I have:


    I am just testing this with an echo. Nothing special:

    And then when I run it, it just shows the variable name in the log


    What exactly am I doing wrong here? 

    Thanks!


  • 2.  RE: VARA.EXEC and Parameters
    Best Answer

    Posted Jun 24, 2020 11:58 AM
    Edited by Christopher Hackett Jul 01, 2020 03:53 PM
    Regarding VAR.SQL restrictions, I thought you could customize what database it uses via the CONN object it is associated with.  Have you tested the ability of associating it with CONN objects that point to different database technologies? (I could be mistaken)

    I have an EXEC solution that passes three parameters.  I defined them in the "variables" area of the job, and the process script begins with three :READ statements to capture them;
    :READ &ESVC_Section#,,
    :READ &ESVC_Key#,,
    :READ &ESVC_Track#,,​


    ------------------------------
    Pete Wirfs
    SAIF Corporation
    Salem Oregon USA
    ------------------------------



  • 3.  RE: VARA.EXEC and Parameters

    Posted Jun 24, 2020 03:13 PM
    Edited by Kyle Harlow Jun 24, 2020 03:16 PM
    The READ statement seemed to do the trick. Thank you!

    And of course it's in the paragraph I ignored in the documentation... 

    Anyways, the problem with VARA.SQL's is that they use the DB_SERVICE agent to get the value. Since our AE database is using MSSQL, DB_SERVICE is running off of an MSSQL JDBC driver. If I try to do anything with a database other than MSSQL (Postgres for the example below), I get an error saying that it is unable to load the appropriate driver:

    !


  • 4.  RE: VARA.EXEC and Parameters

    Posted Jun 25, 2020 11:14 AM
    Edited by Leonard Olteanu Jun 25, 2020 11:15 AM
    Actually, you can use VARA SQL's with any kind of database that has a JDBC driver. All you have to do is to copy the corresponding JDBC driver (.jar file) to jdbc folder for DB_SERVICE agent on your automation engine server. In the example below, I have JDBC drivers for SQL Server, Oracle, and MySQL (actually for MongoDB BI Connector which exposes a MongoDB database as a MySQL database; notice the included MongoDB authentication jar file):
    JDBC drivers vor VARA.SQL

    Then you can define a connection for each database:
    SQL Server Connection
    MySQL Connection
    Oracle Connection


    And finally you can define a VARA.SQL for each type of database:

    VARA.SQL.MSSQL
    VARA.SQL.MYSQL
    VARA.SQL.ORACLE


    ------------------------------
    Cheers,
    Leonard
    ------------------------------