Question on adding value to variable from a sql query

[Santhosh Gollapudi]

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. 

[Lucy Zhang]

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

[Santhosh Gollapudi]

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.

[powdo03]

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

 

2¢

[Santhosh Gollapudi]

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>