I'll start here with an animation GIF of the SQL*Engine Audit page which displays the results of our demonstration online. *Spoiler Alert* - the demonstration was a complete success... Notice the ROW count has been commandeered to hold the Process Instance generated for the RUN steps.
Module: XXSQI_RUN_test.SQX - Primary Script that is executed.
This is the primary script that is launched. It uses the @SCRIPT= directive to call a library routine script I set up (XXSQL_RUN.LIB) to make the actual @RUN= calls (A second example will follow that shows a more basic approach). Before we continue let's take a look at the delimited run parameter string:
<Type> | <Process> | <Oprid> | <Run Control ID>
*The Type is the generic process type - 1=SQR, 5=A/E, 7=RptBook & 9=PSJob
(There are no embedded spaces between the delimiters).
*****************************************************************
* *
* MODULE: XXSQI_RUN_test.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 03/16/2019. *
* DESC: SQL*ENGINE SCRIPT - RUN TEST. *
* *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@HDR_REF=TD_RUN_201903
@HDR_DESCR=TD RUN Demonstration
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
* TEST SQR *
*****************************************************************
@SET=&&RUNPARMS=1|XXAPI143|PSBATCH|AP_XLSUPL_19000
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
* TEST JOB *
*****************************************************************
@SET=&&RUNPARMS=9|XXPCX01J|PSBATCH|PCXRPB_AM_A_TDELIA_A_M
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
* TEST A/E *
*****************************************************************
@SET=&&RUNPARMS=5|AP_PSTPYMNT|PSBATCH|ESCHEAT_15000_51221
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
* TEST RBK *
*****************************************************************
@SET=&&RUNPARMS=7|RPTBOOK|PSBATCH|SP_RB_GRP01
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
Module: XXSQL_RUN.LIB - Library routine called via @SCRIPT= directive.
Library routines may be created/called by SQL*Engine to eliminate repetitive coding. Here the &&RUNPARMS variable contains the delimited run parameter string & is passed to the XXSQL_RUN.LIB script. First it is forced to uppercase with the @SET/@UPPER directives. Next it is parsed using @PARSE so individual variables may hold the Type, Process, Oprid & Run Control values - they are assigned using @SET/@COL:[x] directives. The @SET/@PAD directive is used to ensure there's a single byte with data (not necessary for this demonstration but coded anyway). The @MSG= directive lists each of the resolved variables on the report. The first actual step performs a SELECT COUNT(*) on the PS_PRCSRUNCNTL table using the parsed OPRID & RUN_CNTL_ID. If the combination exists a ROW STATUS of "SOME" is set (otherwise ZERO is set). The next step, which is simply the @RUN=&&RUNPARMS directive, will execute if the Condition SOME is met for the preceding step - if not the step is SKIPPED. This demonstrates the Step-By-Step control of SQL*Engine. In all these cases the @RUN= is executed. The Process Instance generated is passed to the &&RUN_pi variable using the @SET/@ROWS directive (@ROWS is typically used for SQL results but holds the Process Instance (or zero) after the @RUN= is performed).
*****************************************************************
* *
* MODULE: XXSQL_RUN.LIB *
* AUTHOR: TONY DELIA. *
* DATE: 03/16/2019. *
* DESC: SQL*ENGINE SCRIPT - RUN PEOPLESOFT PROCESS. *
* *
*****************************************************************
* *
* Required Substitution Variables *
* -------------------------------------- *
* &&RUNPARMS = <PRCSTYPE>|<PRCSNAME>|<OPRID>|<RUNCNTL> *
* *
* EXAMPLE: *
* @SET=&&DBNAME=@DBNAME *
* @SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound *
* @SET=&&RUNPARMS=1|XXRBKMGR|PSBATCH|SP_RPTBOOK_MGR *
* @SCRIPT=&&LIB/XXSQL_RUN.LIB *
* *
*****************************************************************
* PARSE RUN PARAMETERS *
*****************************************************************
@DELIMITER=|
@SET=&&RUNPARMS=@UPPER
@PARSE=&&RUNPARMS
@DELIMITER=,
@SET=&&RUN_type=@COL:1
@SET=&&RUN_prcs=@COL:2
@SET=&&RUN_user=@COL:3
@SET=&&RUN_cntl=@COL:4
@SET=&&RUN_type=@PAD(%RIGHT,1,$)
@SET=&&RUN_prcs=@PAD(%RIGHT,1,$)
@SET=&&RUN_user=@PAD(%RIGHT,1,$)
@SET=&&RUN_cntl=@PAD(%RIGHT,1,$)
*****************************************************************
* VALIDATE RUN PARAMETERS *
*****************************************************************
@STEP=RUN_VAL_&&RUNPARMS
@COUNT=ps_prcsruncntl
where oprid = '&&RUN_user'
and run_cntl_id = '&&RUN_cntl'
/
@SET=&&RUN_cntr=@ROWS
*****************************************************************
* DISPLAY RUN PARAMETERS *
*****************************************************************
@MSG=RUNPARMS: [&&RUNPARMS]
@MSG=RUN TYPE: [&&RUN_type]
@MSG=RUN PRCS: [&&RUN_prcs]
@MSG=RUN USER: [&&RUN_user]
@MSG=RUN CNTL: [&&RUN_cntl]
@MSG=RUN_CNTR: [&&RUN_cntr]
*****************************************************************
* LAUNCH PROCESS *
*****************************************************************
@DELIMITER=|
@STEP=RUN_PGM_&&RUNPARMS
@COND=SOME.RUN_VAL_&&RUNPARMS
@RUN=&&RUNPARMS
/
@SET=&&RUN_pi=@ROWS
@MSG=Process Instance=&&RUN_pi
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
After the Script is processed you can see the expected results in Process Monitor. First, the SQL*Engine Script Processor itself XX_TDSQL is listed followed by each individual process that we launched within the script. PSJob actually uses three process instances - 4920159 for the main job and 4920160 & 4920161 for the two steps within it. All processes have a run status of Success.
A portion of the SQL*Engine shows variable substitutions & the progress of the script. You can see the nested @SCRIPT (multiple levels supported) - the script Begin/End points are mapped on the report. Also, as shown in the beginning of this post, the step output is saved to the online SQL*Engine Audit screen. It remains there permanently to view as needed.
Module: XXSQI_RUN_test2.SQX - Primary script Bare-Bones No-Frills version.
This version is going to perform the same test but without all the bells & whistles. It's just going to pass the &&RUNPARMS directly to the @RUN= directive. *FYI - The @RUN= directive is executed with a slash (/) just like a SQL command.
*****************************************************************
* *
* MODULE: XXSQI_RUN_test2.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 03/16/2019. *
* DESC: SQL*ENGINE SCRIPT - RUN TEST 2 (MINIMALIST). *
* *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@HDR_REF=TD_RUN_201903_2
@HDR_DESCR=TD RUN Demonstration 2 [Minimalist Version]
*****************************************************************
* TEST VARIOUS RUN CALLS - SQR, JOB, A/E & RBK *
*****************************************************************
@RUN=1|XXAPI143|PSBATCH|AP_XLSUPL_19000
/
@RUN=9|XXPCX01J|PSBATCH|PCXRPB_AM_A_TDELIA_A_M
/
@RUN=5|AP_PSTPYMNT|PSBATCH|ESCHEAT_15000_51221
/
@RUN=7|RPTBOOK|PSBATCH|SP_RB_GRP01
/
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
The results on Process Monitor are virtually the same.
The report is much shorter too. It only has the raw @RUN= step results. On all reports a final summary is included at the end - since we didn't explicitly name the steps the sequential step number is provided.
And finally, the SQL*Engine Audit page for the second test. Each step is comprised of a RUN_Init() function that is executed within the XX_TDSQL.SQR Script Processor. *The page itself has been slightly altered for display purposes (The edit box is much larger on the actual page).
How does SQL*Engine run? You may be asking yourself that since I didn't include any method to process the script. I did mention XX_TDSQL.SQR is the name of the SQL*Engine Script Processor - but not how it's invoked. It could be launched from SQRW assuming your userid has all the required permissions to run your script. Here we have something much more sophisticated which is part of our overall 24/7 Interface Methodology. In short, the script is dropped in a designated folder on our Unix file server (special permissions required of course). Within 5 minutes the file is picked up & linked to the XX_TDSQL.SQR process & executed. The process is performed by a simple Drag-and-Drop. When complete the script is automatically placed in the /archive folder. The library scripts XXSQL*.LIB remain resident for future use - they are not executable scripts.
This concludes another edition of TDXBITS... stay tuned for more coming your way.
