SQL*Engine - Dynamic SQL Script Processor - Part 3 - Command Guide

SQL*Engine - Part 1 - Initial Overview / Demonstration
SQL*Engine - Part 2 - Launching PeopleSoft Process Requests

Welcome back to another blog entry. This one continues with the SQL*Engine discussion. SQL*Engine is a Dynamic SQL Script Processor I created that's somewhat of a hybrid mix of SQL*Plus, Application Engine & MVS/JCL. The main module XX_TDSQL.SQR is written in SQR. I use this utility frequently for general purposes, testing & production updates.

Below is a simple Command Guide that highlights the various directives used to Control/Execute SQL Statements, Queue PeopleSoft Process Requests, Set & Manipulate Dynamic Variables & much more....

SQL*Engine Command Guide - Developed by Tony DeLia


<SQL Statement>
/

   SQL Statement - May span multiple lines. Executed with slash (/) character.

      Any line without a recognizable directive or comment (*) is considered part of an SQL statement.

@STEP=<Step_Name>

   Assign a Step_Name to a Step. A step is any executable statement such as
   an SQL statement or an executable directive: @SELECT=, @COUNT= or @RUN=

@COND=<Step_Condition>[.<Step_Name>]

   Control Execution of step based on results of a prior step.

      Optional <Step_Name> used to reference a specific step.
      If omitted the prior step is assumed.    

      Step Conditions - PASS/FAIL/NULL/SKIP/ZERO/SOME

@RUN=<Process Request Parameters>
/

   Load a PeopleSoft Process Request.

      Parameter Format: <Type>|<Prcs>|<Oprid>|<RunControl>

      Obtain Process Instance via @SET/@ROWS Directive.
      Step Row Status: ZERO / SOME

@WAIT=<Process Instance>

   Wait for a PeopleSoft Process Request to End.

      SQL*Engine checks run status every 30 seconds & resumes with script once the
      designated process completes (Success/Failure).

@STALL=<Number of Seconds>

   Stall SQL*Engine script for X number of seconds.

      Just a simple delay mechanism.

@COUNT=<Table Name>
<Where Clause Criteria>
/

   Select Number of Rows in <Table Name>
 
      Row count can be retrieved using @SET/@ROWS directive.
      Step Row Status: ZERO / SOME
      Use with @COND Directive for Conditional Control.

@SET=<Variable>=<Value>
 
  Set Substitution Variable (Name/Value).

  Any occurance of the substitution variable found in the script will be
  resolved with latest value from the @SET= Directive.

  @SET Extensions:

     @SET=<Variable>=@PROMPT[:msg_id]
     @SET=<Variable>=@UPPER
     @SET=<Variable>=@LOWER
     @SET=<Variable>=@REVERSE
     @SET=<Variable>=@XLATE(old, new)
     @SET=<Variable>=@REPLACE(old, new)
     @SET=<Variable>=@STRIP(characters)
     @SET=<Variable>=@SUBSTR(<var/lit>, pos, len)
     @SET=<Variable>=@ROWS[:step]
     @SET=<Variable>=@COL[:seq]
     @SET=<Variable>=@XOR[:mask]
     @SET=<Variable>=@AND[:mask]
     @SET=<Variable>=@OR[:mask]
     @SET=<Variable>=@CALC(operator, value)
     @SET=<Variable>=@CONVERT(value, basefrom, baseto)
     @SET=<Variable>=@PAD(direction, length, value)
     @SET=<Variable>=@TRIM(direction, value)
     @SET=<Variable>=@NUMLIT(value)
     @SET=<Variable>=@LENGTH(value)
     @SET=<Variable>=@DBNAME
     @SET=<Variable>=@SEQ
     @SET=<Variable>=@DATE
     @SET=<Variable>=@TIME
     @SET=<Variable>=@XLTITEM(fieldname, fieldvalue)
     @SET=<Variable>=@CONCAT(string)
     @SET=<Variable>=@NVL(value)

   Meta-Operators:

     Some functions such as @CALC, @CONVERT, @PAD & @TRIM
     may use Meta-Operators: %ADD, +, %SUB, -, %DIV, /,
     %MUL, *, %EXP, ^, %MOD, %, %RND, %AND, %OR, %XOR,
     %BIN, %OCT, %DEC, %HEX, %LEFT, %RIGHT & %BLANK.
     
@DELIMITER=<delimiter value>

   Sets delimiter character to be used by various functions such as
   @PARSE & @SET directives. Default is pipe character (|).

@PARSE=<variable>

   Parses contents of variable using currently defined delimiter.
   Access the parsed contents using @COL parameter of @SET.

@MSG=<MessageText/Substitution Variable(s)>

   Display Messages and/or 'resolved' variables on report listing.

@SCRIPT=<Path/Filename>

   Call and Execute another Script File within the current script.
   Utilize @SET= to pass values to the embedded script.

@REPORT=<Path/Filename>

   Redirect XX_TDSQL.LIS file (similar to -f flag)

@LIST=<Column;Column;Column; etc>

   Select List (Columns separated by semi-colon). For use with @SELECT
   directive. Values can be extracted using using @SET/@COL directive.

@SELECT=<Table Name>
<Where Clause Criteria>
/

   Select Column(s) from <Table Name>.
   Columns specified using @LIST directive.

@TIMING=<ON/OFF>

   Activates SQL Statement Timings on Report.

@SAFETY=<ON/OFF>

   Controls implicit COMMIT performed by SQRW.

      ON  - Default. Issues a ROLLBACK prior to EOJ.
      OFF - Bypass ROLLBACK (implicit COMMIT by SQRW in effect).

@SHOW=<ON/OFF> 

   Controls display of @SET Variable Results.

      ON  - Default. Print @SET variable contents.
      OFF - Do NOT print @SET variable contents report

@SPOOL=<ON/OFF>

   Saves all @SELECT= data to a delimited SPOOL File.

      OFF - Default. No Spool File Output.
      ON  - Open SPOOL File / Write SELECT Output.

            Creates XX_TDSQL_<SQLID>_<DTTM>.TXT
            [Can toggle OFF/ON to generate multiple Files]

@DISPLAY <No Parameters>

   Displays snapshot of all variables/values.

@ON_ERROR=<Condition>

   Designate how to handle errors.

      STOP     - Default - Halts Processing for all Errors...
                 except those specified as WARNING level.
      CONTINUE - Continues Processing for all Errors... except
                 those specified as FATAL level.

      FATAL=<Error Code>   - Specify FATAL level errors.
      WARNING=<Error Code> - Specify WARNING level errors.

      CLEAR    - Clears all prior WARNING/FATAL level errors.

      *<Error Code> used as FATAL/WARNING parameter is a pattern
                    as opposed to an actual error code.

@HDR_REF=<ReferenceID>

   Add a Help Desk Reference or other Reference ID.

      This is for the online Audit Tables/Page.

@HDR_DESC=<Descriptive Text>

   Compliment to @HDR_REF - For Audit Tables/Page.

@EMAIL=<ON/OFF>

   Enable Email Notifications (Default or Custom Override).

      By default a generic email is sent to the SQL*Engine distribution list.

@EMAILX=<Recipient|Recipient|etc>

   Override Email Notification Distribution List.

      Recipient may be an explicit email address or PeopleSoft Oprid. Code as many as needed.
      Can be a delimited list or multiple @EMAILX= directives.

@EMAIL_SUB=<Email Subject>

   Override Generic Email Notification Subject Line.

      Generic: SQL*Engine[xxx] YYYY-MM-DD
      * xxx=SQL*Engine ID#


@EMAIL_TXT=<Email Text>

   Override Generic Email Notification Text.

      Generic Text includes SQL*Engine Script Name, Owner, Reference ID, Description and
      the PeopleSoft Report Manager link to easily access the PDF/Log. 
      Override using combination of text, meta-variables & carriage controls.
      Multiple @EMAIL_TXT directives are used to build the body text.
      Use @EMAIL_TXT=@CLEAR to reset the text to NULL.

      Override Meta-Variables (Subject/Text):
        %ID_S  - SQL*Engine ID#
        %ID_B  - Interface Batch# (System Standard)
        %FILE  - SQL*Engine Script Filename
        %YMD   - Current Date YYYY-MM-DD
        %REF   - Reference ID
        %DESC  - Reference Description
        %OWNER - SQL*Engine Script Owner
        %DBASE - Database Name
        ^      - Carriage Return (Text Only)



More examples coming soon including in-depth coverage of the powerful @SET= Directive.