ESP Workload Automation

Expand all | Collapse all

Question on adding value to variable from a sql query

Jump to Best Answer
  • 1.  Question on adding value to variable from a sql query

    Posted 02-08-2017 12:43 PM

    Hi,

     

    Is there a way to add a value to variable dynamically from sql query output in CA ESP that I can use across the application.

     

    Basically, we need to use a variable across ESP application and the value for it should come from a sql query that means it changes everytime whenever we run the application. 



  • 2.  Re: Question on adding value to variable from a sql query

    Broadcom Employee
    Posted 02-10-2017 07:14 AM

    Hello,

     

    Could you provide the job definition for the SQL query? And what type of plugin is in use for this purpose?

     

    Thank you,

     

    Lucy



  • 3.  Re: Question on adding value to variable from a sql query

    Posted 02-10-2017 04:34 PM

    Hi Santhosh, 

    This may work for you.  I would only recommend this if the output of the SQL is a small amount of data. 

     

    The idea is to put it in an ESP variable using the ESPLMI command to send it back to the manager. 

    The script would look like the one below. 

    #!/bin/ksh

    SQL HERE and store output in $VARIABLE_HERE

    /opt/agents/agentu_01/Esplmi -smainframehere:8988 -uuserhere -ppasswordhere "-cVSET ABCDF $VARIABLE_HERE TABLE(DPVARS)"

    echo 'returncode' $?

     

    Then the next jobs could get the variable using a VGET command like the one below

    VGET (ABCDF) TABLE(DPVARS) CLANG

     



  • 4.  Re: Question on adding value to variable from a sql query

    Posted 02-10-2017 07:35 AM

    Lucy, 


    We are running the SQL query from a shell script an the job type is LINUX_JOB. 


    But we are looking for an option if there is any chance to run the query from SQL_JOB and output of it can be stored into a variable to use across all the jobs in application.



  • 5.  Re: Question on adding value to variable from a sql query
    Best Answer

    Posted 02-12-2017 06:26 AM

    Thank you powdo03. It worked !!

     

    But as this command requires username and password which we should not share with others, I have followed the below procedure:

    • When running the sql query through Linux script, we are storing the output of query into a flat file as variable: <FILE>
    • And in each job definition wherever we require this variable we are calling it with ENVAR EWAGLOBALPROFILE statement.
    • So the job definition is:

                      

             CMDNAME /bin/echo

             ARGS $VARIABLENAME

             ENVAR EWAGLOBALPROFILE=<FILE>