How can I automatically update Global Variables when a database table has been updated?

Products

• ESP Workload Automation

Issue/Introduction

Description:

When an Oracle Database table column is modified, there is a need to automatically update a Global Variable within ESP.  A process can then need to be initiated based on the value of the Global Variable.

Steps that are used to automate process.

1. Monitor the database table for update
2. Pass the table column values back to ESP
3. Update a Global Variable Table (GVT) variable
4. Get the global variables when needed

Solution:

Using the following sample database table RUN_PROC...

 

PROC_1  PROC_2  MOD_BY                     MOD_TIME               

------- ------- -------------------------- ---------             

Y       N       SACKA01                    2008-10-17 13:03:20.0 

N       N       ARMSU01                    2008-10-17 14:36:12.0 

 

Where columns:

PROC_1 (Run Process 1 (Y/N)

PROC_2 (Run Process 2 (Y/N)

MOD_BY (Who modified the table...)

MOD_TIME (...and when)

 

and ESP definitions...

 

Event SACKA01.DBTRG:

 

EVENT ID(SACKA01.DBTRG)  SYSTEM(X550M)  REPLACE 

INVOKE 'SACKA01.PSS.PROCLIB(DBTRG)'             

 

Application DBTRG:

 

APPL DBTRG                                                       

                                                                 

DB_TRIG DBTRG                                                   

  AGENT KSWINR7                                                   

  USER ESP5                                                      

  DB_URL jdbc:oracle:thin:@supintl1:1521:espresso                

  TABLE_NAME KARL_RUN                                            

  TRIG_TYPE UPDATE                                                

  RELEASE QRYTAB                                                 

  RUN ANY                                                        

ENDJOB                                                           

                                                                  

SQL_JOB QRYTAB                                                   

  AGENT KSWINR7                                                  

  USER ESP5                                                      

  DB_URL jdbc:oracle:thin:@supintl1:1521:espresso                

  SQL 'SELECT * FROM RUN_PROC +                                  

    WHERE MOD_TIME = (SELECT MAX(MOD_TIME) FROM RUN_PROC)';      

  RUN ANY                                                        

  RELEASE SETGVT                                                 

ENDJOB                                                           

                                                                 

JOB SETGVT LINK PROCESS                                           

  REXXON                                                         

    N=TRAPOUT('LINE.')                                           

    "ESP LAP DBTRG.0 JOB(QRYTAB)"                                

    N=TRAPOUT('OFF')                                              

    SAY LINE.5                                                   

    PARSE VAR LINE.5 'PROC_1=' RUN1 '|' 'PROC_2=' RUN2 '|'        

    "VSET RUN1 '"RUN1"' TABLE(RUN_PROC)"                         

    "VSET RUN2 '"RUN2"' TABLE(RUN_PROC)"                         

  REXXOFF                                                        

  RUN ANY                                                        

ENDJOB                                                           

 

Where jobs...

 

DBTRG (Monitor the table RUN_PROC for UPDATE)

Once the table has been updated, job DBTRG will complete and RELEASE QRYTAB.

The URL format is: jdbc:oracle:thin:<host>:<port>:<database name>

 

QRYTAB (Run an SQL query against a database table RUN_PROC to extract the latest PROC_1, and PROC_2 values)

These will be sent back from the Database Agent to the Manager thru the LStatus parm in the AFM.

This information will be displayed when you do an LA next to the job.

 

LAP DBTRG.15 ALL                                                          

APPL DBTRG    GEN 15 COMPLETE                                            

  CREATED AT 13.44 ON FRIDAY OCTOBER 17TH, 2008                          

    ENDED AT 13.44 ON FRIDAY OCTOBER 17TH, 2008                          

           BY EVENT SACKA01.DBTRG                                        

  DBTRG J2354, COMPLETED (FORCED), CC 0,                                 

   AT 13.44 ON FRIDAY OCTOBER 17TH, 2008                                 

PROC_1=N|PROC_2=N|MOD_BY=ARMSU01|MOD_TIME=2008-10-17 14:36:12.0          

  QRYTAB J2355, COMPLETED, CC 0,  AT 13.44 ON FRIDAY OCTOBER 17TH, 2008  

  SETGVT, COMPLETED, CC 0,  AT 13.44 ON FRIDAY OCTOBER 17TH, 2008        

                                                                          

----- 1 ENTRY DISPLAYED                                                                                                                                                    

 

Note: Only one result row can be retrieved through the LAP command. Otherwise you will see the following line...

 

"Multiple rows in response. See spool file. "

 

instead of...

 

PROC_1=Y|PROC_2=N|MOD_BY=SACKA01|MOD_TIME=2008-10-17 13:03:20.0         

                        

 

SETGVT (Set the Global Variable Table variable RUN1 and RUN2 in TABLE (RUN_PROC) to the values in the RUN_PROC database.

RUN1 is either "Y" or "N" depending on the value of PROC_1 in the database table RUN_PROC.

RUN2 is either "Y" or "N" depending on the value of PROC_2 in the database table RUN_PROC.

 

You can verify the results of the Global Variable Table by issuing the VTLIST RUN_PROC command in page mode...

 

vtlist run_proc                                              

Global Variable Table RUN_PROC                               

 Created at 12.10.42 on FRIDAY OCTOBER 17TH, 2008 by SACKA01 

  Last update at 13.44.17 on FRIDAY OCTOBER 17TH, 2008       

  Currently 2 variables, 0 triggers                          

  Table size 512, 112 bytes imbedded free space              

RUN1='N'                                                      

RUN2='N'                                                                                                           

 

Now these variables can be used anytime in another Application by retrieving the global variables using the VGET command.

 

APPL RUN_PROC

 

VGET RUNPROC1 TABLE(RUN_PROC)

 

IF %RUNPROC1 = ?Y? THEN DO

  JOB RUNPROC1

    RUN TODAY

  ENDJOB

ENDDO

 

VGET RUNPROC2 TABLE(RUN_PROC)

 

IF %RUNPROC2 = ?Y? THEN DO

  JOB RUNPROC2

    RUN TODAY

  ENDJOB

ENDDO

 

Note:  You can split up the DBTRG Application into two parts and continuously monitor the database table. Then Issue an ALERT to trigger another Event which will invoke another Application to update the GVT.

Environment

Release: WKLASG00200-5.5-Workload Automation-Service Governor-EE
Component: