SQL*Engine is a custom scripting language I developed that is powered by a single SQR source module named XX_TDSQL.SQR. The language itself is best described as a cross between SQL*Plus, Application Engine & JCL (Job Control Language). Originally developed in 1999 I've been adding considerable functionality - the latest being full integration with PeopleSoft Process Scheduler, Automatic Script Detection/Security/Execution/Archiving & a Script Result Audit Page (as well as full reporting history).
Below is a sample of the SQL*Engine Audit Page - Each step is displayed in the scroll bar with the resolved SQL along with the associated results - Step Name, Status, Counts, Timings & Condition (if applicable). This particular example is deleting run control records from 5 different tables. The full set of expected results must match the actual counts for the COMMIT to be performed - otherwise a ROLLBACK is issued.
Below is the raw executable script - XXSQI_RC_DELETE.SQX - the OPRID "PSBATCH" & RUN_CONTROL_ID pattern "AP_XXACH_R2_%" are assigned to dynamic variables &&OPRID and &&RUNCONTROL which will be utilized in each DELETE statement. Variables are also set for 5 expected results & initialized for the related SQL results. Notice the steps are given an optional "Step Name". The steps are named as follows:
- @STEP=DELETE_XX_RUN_CNTL_AP
- @STEP=DELETE_PRCSRUNCNTLDIST
- @STEP=DELETE_PRCSRUNCNTLEOPT
- @STEP=DELETE_PRCSRUNCNTLDTL
- @STEP=DELETE_PRCSRUNCNTL
- @STEP=COMPARE_COUNTS - Sets the Comparison Result
- @STEP=COMMIT - Conditional depending on COMPARE_COUNTS results [@COND=SOME.COMPARE_COUNTS]
- @STEP=ROLLBACK - Conditional - Executes Rollback if Commit is skipped [@COND=SKIP.COMMIT]
*****************************************************************
* *
* MODULE: XXSQI_RC_DELETE.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 10/28/2015. *
* DESC: SQL*ENGINE SCRIPT - RUN CONTROL DELETE. *
* *
*****************************************************************
* STANDARD SQL*ENGINE DIRECTIVES *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@HDR_REF=CRM_1210301
@HDR_DESCR=Delete Run Controls AP_XXACH_R2_<BU>
*****************************************************************
* SET OPRID/RUNCONTROL VARIABLES *
*****************************************************************
@SET=&&OPRID=PSBATCH
@SET=&&RUNCONTROL=AP_XXACH_R2_%
*****************************************************************
* SET EXPECTED RESULTS / INITIALIZE COUNTS TO ZERO *
*****************************************************************
@SET=&&EXP_A=47
@SET=&&EXP_B=361
@SET=&&EXP_C=44
@SET=&&EXP_D=47
@SET=&&EXP_E=47
@SET=&&CTR_A=0
@SET=&&CTR_B=0
@SET=&&CTR_C=0
@SET=&&CTR_D=0
@SET=&&CTR_E=0
*****************************************************************
* A - DELETE XX_RUN_CNTL_AP *
*****************************************************************
@STEP=DELETE_XX_RUN_CNTL_AP
delete
from ps_xx_run_cntl_ap
where oprid = '&&OPRID'
and run_cntl_id like '&&RUNCONTROL'
/
@SET=&&CTR_A=@ROWS
*****************************************************************
* B - DELETE PRCSRUNCNTLDIST *
*****************************************************************
@STEP=DELETE_PRCSRUNCNTLDIST
delete
from ps_prcsruncntldist
where oprid = '&&OPRID'
and runcntlid like '&&RUNCONTROL'
/
@SET=&&CTR_B=@ROWS
*****************************************************************
* C - DELETE PRCSRUNCNTLEOPT *
*****************************************************************
@STEP=DELETE_PRCSRUNCNTLEOPT
delete
from ps_prcsruncntleopt
where oprid = '&&OPRID'
and runcntlid like '&&RUNCONTROL'
/
@SET=&&CTR_C=@ROWS
*****************************************************************
* D - DELETE PRCSRUNCNTLDTL *
*****************************************************************
@STEP=DELETE_PRCSRUNCNTLDTL
delete
from ps_prcsruncntldtl
where oprid = '&&OPRID'
and runcntlid like '&&RUNCONTROL'
/
@SET=&&CTR_D=@ROWS
*****************************************************************
* E - DELETE PRCSRUNCNTL *
*****************************************************************
@STEP=DELETE_PRCSRUNCNTL
delete
from ps_prcsruncntl
where oprid = '&&OPRID'
and run_cntl_id like '&&RUNCONTROL'
/
@SET=&&CTR_E=@ROWS
*****************************************************************
* COMPARE ROW COUNTS VS. EXPECTED *
*****************************************************************
@STEP=COMPARE_COUNTS
@COUNT=PSCLOCK
WHERE TO_NUMBER('&&EXP_A') = TO_NUMBER('&&CTR_A')
AND TO_NUMBER('&&EXP_B') = TO_NUMBER('&&CTR_B')
AND TO_NUMBER('&&EXP_C') = TO_NUMBER('&&CTR_C')
AND TO_NUMBER('&&EXP_D') = TO_NUMBER('&&CTR_D')
AND TO_NUMBER('&&EXP_E') = TO_NUMBER('&&CTR_E')
/
*****************************************************************
* COMMIT *
*****************************************************************
@STEP=COMMIT
@COND=SOME.COMPARE_COUNTS
commit
/
*****************************************************************
* ROLLBACK *
*****************************************************************
@STEP=ROLLBACK
@COND=SKIP.COMMIT
rollback
/
*****************************************************************
* END OF SQL*ENGINE SCRIPT *
*****************************************************************
After each SQL Execution (/) the assigned variable is set with the Row Count (@ROWS). For example @SET=&&CTR_A=@ROWS populates the &&CTR_A variable with the result of the first delete step (DELETE_XX_RUN_CNTL_AP) - 47 rows (See Audit Page for Step 1).
SQL*Engine assigns results to each step - the STEP Status will be set to PASS, SKIP or FAIL while the Row Status will be set to SOME or ZERO (or blank in the event of SKIP/FAIL). These step status values may be used to control subsequent SQL execution. In this example the COMPARE_COUNTS step has produced the expected results for each step. The SELECT COUNT construct (@COUNT) sets the Row Status to SOME (the Step Status is set to PASS).
The COMMIT step is performed depending on the condition setting - @COND=SOME.COMPARE_COUNTS - The condition matches & commit is executed.
The ROLLBACK step has the condition of @COND=SKIP.COMMIT - the COMMIT step wasn't skipped - it has a step status of PASS - so the ROLLBACK step is not executed (set to SKIP).
Report Snippets:
The generated report displays the resolved SQL statements, directives, variable assignments, messages, begin/end/elapsed times & more...
A closer look at how the SQL*Engine directives control the flow of the process...
At the end of the report, a summary of each step is presented with the results & pertinent information.
This was a very simple application but a good example of how SQL*Engine works. This is a slick, high quality utility so I'll be posting more brief articles about this.
