SQL*Engine - Part 2 - Launching PeopleSoft Process Requests
SQL*Engine - Part 3 - Command Guide
SQL*Engine - Part 4 - @SET Variables
SQL*Engine - Part 5 - Synonyms/Grants
Welcome back. This is Part 6 of my Custom SQL*Engine series. I added some additional functionality (Part 3 Command Guide updated) which I will highlight here. The @WAIT= directive is a useful companion to the @RUN= directive I explained in Part 2. Once a process is launched it suspends the script until it completes (Success or Failure). I also added Email Notifications that can be controlled through several optional Email-Based directives (@EMAIL=, @EMAILX=, @EMAIL_SUB & @EMAIL_TXT).
To illustrate these new features I'll use a script that resets our nightly GL batch jobset in the case of a failure due to network or database issues. It will call a standard script to update/delete process schedule info, then launch the FS_JGEN Application Engine to clear Temp Table Locks & WAIT for the called process to complete. Then it will retrieve & display the status of the FS_JGEN job and send an email notification.
To execute the script XXSQI_GL_RESET.SQX I simply drag and drop into the proper File Depot location - that's it. File Depot is the name given to our file server structure. The filename pattern XXSQI*.SQX is configured to be automatically processed by the SQL*Engine module XX_TDSQL.SQR. This is part of the broader 24/7 Interface Methodology I developed. The script will be detected automatically within 5 minutes, moved to the /process folder & archived when complete. These actions occur as part of my API used for all inbound/outbound interfaces - SQL*Engine scripts are files just like any other data extract.
Above: XXSQI_GL_RESET.SQX dropped in the server directory. This script will be using the XXSQL_JOB_RESET.LIB for some standard updates & XXSQL_RUN.LIB to launch the FS_JGEN job.
Above: FS_JGEN is processing while XX_TDSQL is still in a wait state. Althought Process Monitor lists the run status as "Processing" it has not moved past the @WAIT= directive.
Main Demonstration Script - XXSQI_GL_RESET.SQX:
Before displaying the XXSQI_GL_RESET.SQX script it is worth pointing out that the process instance for the called FS_JGEN job is populated in the XXSQL_RUN.LIB module. It is passed to the &&RUN_pi dynamic variable - then used in the @WAIT=&&RUN_pi directive.
From the called SQL*Engine Script - XXSQL_RUN.LIB:
@RUN=&&RUNPARMS
/
@SET=&&RUN_pi=@ROWS
The &&RUN_pi variable has been set with the process instance. This will be used by the @WAIT= directive in our main script. Scroll down to find it highlighted in YELLOW.
*****************************************************************
* *
* MODULE: XXSQI_GL_RESET.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 09/15/2019. *
* DESC: SQL*ENGINE SCRIPT - XXGLPRCS/FS_JGEN RESET. *
* *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@SET=&&YMD=@DATE
@HDR_REF=TD_GLRESET_&&YMD
@HDR_DESCR=GL_RESET_&&YMD XXGLPRCS Reset
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
* RESET NIGHTLY GL JOBSET (STANDARD) *
*****************************************************************
@SET=&&JOBSET=XX_GL_ALL_BUS_JOBSET
@SCRIPT=&&LIB/XXSQL_JOB_RESET.LIB
*****************************************************************
* CALCULATE NEXT START DATE/TIME (10:30PM) *
*****************************************************************
@STEP=CALCULATE_NEXT_DTTM
@LIST=to_char(sysdate+(1/12),'YYYYMMDD')||'2230'
@SELECT=DUAL
/
@SET=&&DTTM=@COL:1
@MSG=Next &&JOBSET Start Date[&&DTTM]
*****************************************************************
* APPLY NEXT START DATE/TIME (10:30PM) *
*****************************************************************
@STEP=CALCULATE_NEXT_DTTM
@COND=PASS
update ps_schdldefn
set startdatetime = to_date('&&DTTM','YYYYMMDDHH24MI'),
recurname = 'XX_GL_DAILY_RCR',
schedulestatus = '1',
lastupddttm = sysdate,
lastupdoprid = 'PSBATCH'
where schedulename = '&&JOBSET'
/
@SET=&&TEMP=@ROWS
@MSG=Jobset[&&JOBSET] DttmUpdated[&&TEMP]
*****************************************************************
* COMMIT NEXT START DATE/TIME *
*****************************************************************
@STEP=COMMIT_NEXT_DTTM
@COND=PASS
commit
/
*****************************************************************
* RUN JOURNAL GENERATOR (CLEAR LOCKS) *
*****************************************************************
@SET=&&RUNPARMS=5|FS_JGEN|PSBATCH|GL_AP_GJ_ALLBUS_NIGHTLY
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
* WAIT FOR JOURNAL GENERATOR COMPLETION *
*****************************************************************
@WAIT=&&RUN_pi
*****************************************************************
* SELECT JOURNAL GENERATOR RUN STATUS *
*****************************************************************
@STEP=SELECT_RUN_STATUS
@COND=NULL
@LIST=runstatus
@SELECT=psprcsrqst
where prcsinstance = &&RUN_pi
/
@SET=&&RUN_STAT=@COL:1
@DELIMITER=,
@SET=&&RUN_DESC=@XLTITEM(RUNSTATUS,&&RUN_STAT)
@MSG=Process[&&RUN_pi] Status[&&RUN_STAT] Desc[&&RUN_DESC]
*****************************************************************
* EMAIL NOTIFICATION *
*****************************************************************
@EMAIL=ON
@EMAILX=TDELIA
@EMAIL_SUB=SQL*Engine[%ID_S] %YMD - GL Reset/JGEN
@EMAIL_TXT=SQL*Engine GL Reset - %DBASE^^
@EMAIL_TXT= File: %FILE^^
@EMAIL_TXT=Batch: %ID_B^^
@EMAIL_TXT=Owner: %OWNER^^
@EMAIL_TXT=FSGEN: &&RUN_pi^^
*****************************************************************
* DISPLAY ALL VARIABLES *
*****************************************************************
@DISPLAY
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
After the @WAIT=&&RUN_pi the script continues and extracts the FS_JGEN run status & displays it on the report. The @XLTITEM= @SET directive translates the value into the long description (found in PSXLATITEM). Then it proceeds with the Email Notification functionality.
@EMAIL=ON activates the email notification (actually ON by default). If this is the only EMAIL option used a generic email will be sent to all recipients on the distribution list of the job.
@EMAILX=TDELIA overrides the distribution list. The email address from the OPRID will be used. Multiple @EMAILX= directives may be used with one or more delimited recipients specified. Recipients may be defined as a PeopleSoft OperatorID or an explicit email address.
A generic Subject & Text will be sent unless they are overridden.
@EMAIL_SUB= will override the generic subject while @EMAIL_TXT= is used for the email text. Reference the Command Guide in Part 3 for more details including meta-variable use.
At the end of the script the Email Notification is sent to the appropriate users.
Above: Email Notification arrives in my Inbox.
Above: Full Email Notification - Both the Subject & Text have been overridden using a combination of Text & Meta-Variables: %ID_S, %YMD, %DBASE, %FILE, %ID_B and %OWNER. The FS_JGEN process instance was passed using the dynamic variable &&RUN_pi.
NOTE: Several other updates were made to the SQL*Engine module - all of which have been updated in the Part 3 - Command Guide post. @STALL= (generic delay for x seconds) and @SET directives @CONCAT, @XLTITEM (used above) and @DATE (formats current date in YYYYMMDD format).
To view the PDF report for this SQL*Engine Script CLICK HERE
