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

Document ID : KB000054534
Last Modified Date : 14/02/2018
Show Technical Document Details

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.