How to automatically update global variables when a database table has been updated
Article ID: 54534
Updated On: 10-12-2023
Products
Issue/Introduction
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.
- Monitor the database table for update
- Pass the table column values back to ESP
- Update a Global Variable Table (GVT) variable
- Get the global variables when needed
Environment
Release : 12.0
Component : ESP WORKLOAD AUTOMATION
Resolution
Using the following sample database table RUN_PROC...
PROC_1 PROC_2 MOD_BY MOD_TIME
----------- ------------ -------------------- ---- -----------------------------
Y N <user> 2008-10-17 13:03:20.0
N N <user> 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 prefix.name:
EVENT ID(prefix.name) SYSTEM(ESPx) REPLACE
INVOKE 'hlq.proclib(DBTRG)'
Application DBTRG:
APPL DBTRG
DB_TRIG DBTRG
AGENT <agent>
USER <user>
DB_URL <url>@<host>:####:<dbname>
TABLE_NAME <tblname>
TRIG_TYPE UPDATE
RELEASE QRYTAB
RUN ANY
ENDJOB
SQL_JOB QRYTAB
AGENT <agent>
USER <user>
DB_URL <url>@<host>:####:<dbname>
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 prefix.name
DBTRG J2354, COMPLETED (FORCED), CC 0,
AT 13.44 ON FRIDAY OCTOBER 17TH, 2008
PROC_1=N|PROC_2=N|MOD_BY=userxxx|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
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=userxxx|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 userxxx
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.
Feedback
