/* REXX */
 
/**********************************************************************/
/*  This Macro generates a CRUD matrix from an App Engine Trace.      */
/*  Enter the App Engine Trace File without quotes when prompted.     */
/*  The output is written to:  yourId.temp.crud.                      */
/*Use this REXX to generate a CRUD matrix from a PS 8.0, MVS/DB2      */ 
/*Application Engine trace.  Move the trace to an MVS /*sequential    */
/*dataset.  FTP the attached REXX to an MVS PDS member.  Enter the    */ 
/*following without the double quotes                                 */
/* “TSO EXEC ‘AAA.BBB.CCC(AECRUD)’”                                   */ 
/*Where AAA.BBB.CCC is the PDS you FTP’d the REXX to and AECRUD is the*/ 
/*member.  The REXX will ask /*you for the name of the sequential     */ 
/*MVS dataset that contains the Application Engine trace.The REXX will*/  
/*put the CRUD matrix in a sequential dataset                         */
/*named ‘YourTSOID.TEMP.CRUD’.                                        */
/*                                                                    */
/**********************************************************************/
 
PRESENT = 'N'
OR.  = ''
OR.0 = 0
ORNO = 0
 
LINE. = ''
LINE.0 = 0
N = 0
 
UPDARAY. = ''
UPDARAY.0 = 0
UPDARAYIND = 0
 
DELARAY. = ''
DELARAY.0 = 0
DELARAYIND = 0
 
INSARAY. = ''
INSARAY.0 = 0
INSARAYIND = 0
 
SELARAY. = ''
SELARAY.0 = 0
SELARAYIND = 0
 
  USER = USERID()
  CALL GET_INPUT_FILE
  CALL READ_MEMBER
  IF RC = 0 THEN
    DO
      CALL READ_TRACE
      CALL WRITE_OUTPUT_STEM
      CALL WRITE_OUTPUT
    END
   ELSE
      SAY 'Invalid File Name Entered'
EXIT
 
 GET_INPUT_FILE:
 
  SAY 'PLEASE ENTER THE TRACE FILE NAME :'
  SAY 'Enter the full name without quote'
  PULL IDS
 
 RETURN
 
 
 READ_MEMBER:
   x = OUTTRAP("output",0)
   IR. = ''
   IR.0 = 0
   "ALLOC DS('"IDS"') F(IDD) SHR REUSE"
   "EXECIO * DISKR IDD (STEM IR. FINIS"
   "FREE F(IDD)"
 RETURN
 
 READ_TRACE:
 
   DO F = 6 TO IR.0
 
      IF SUBSTR(IR.F,10,33) = 'Application Engine ended normally' THEN
        F = IR.0
 
      IF SUBSTR(IR.F,1,1) <> '/'  THEN
 
        IF SUBSTR(IR.F,1,2) <> '--' THEN
 
          IF SUBSTR(IR.F,1,6) <> 'COMMIT' THEN
 
             IF SUBSTR(IR.F,1,20) <> '                    ' THEN
               DO
                 UPPER IR.F
                 CALL FIND_SQL_STMT
               END
             ELSE
 
                F = F + 1
 
   END
 RETURN
 
 FIND_SQL_STMT:
 
   IF INDEX(IR.F,'UPDATE') = 1 THEN
      DO
        PARSE VAR IR.F STMT TABNAME RESTLINE
        TABNAME = STRIP(TABNAME)
        CALL UPDATE_TABLE_IDENTIFIER
        CALL FIND_NEXT_STMT
      END
   ELSE IF INDEX(IR.F,'DELETE') = 1 THEN
          DO
            PARSE VAR IR.F STMT FROM TABNAME RESTLINE
            TABNAME = STRIP(TABNAME)
            CALL DELETE_TABLE_IDENTIFIER
            CALL FIND_NEXT_STMT
          END
   ELSE IF INDEX(IR.F,'INSERT') = 1 THEN
          DO
            PARSE VAR IR.F STMT INTO TABNAME RESTLINE
            TABNAME = STRIP(TABNAME)
            IF INDEX(TABNAME,'(') > 0 THEN
              DO
                TABNAME = SUBSTR(TABNAME,1,INDEX(TABNAME,'(') - 1)
              END
            CALL INSERT_TABLE_IDENTIFIER
            CALL FIND_NEXT_STMT
          END
   ELSE IF INDEX(IR.F,'%SELECT') = 1 THEN
          DO
            CALL SELECT_TABLE_IDENTIFIER
          END
   ELSE IF INDEX(IR.F,'%EXECUTE') = 1 THEN
          CALL EXECUTE_TABLE_IDENTIFIER
 RETURN
 
 FIND_NEXT_STMT:
 
 /*  DO WHILE INDEX(IR.F,'/') = 0   */
   DO WHILE WORDPOS('/',IR.F) <> 1   /* Till Comment reached */
     IF F > IR.0 THEN LEAVE
      ELSE IF INDEX(IR.F,'SELECT') > 0 THEN
        DO
          SELECTFOUND = 'Y'
          CALL SELECT_TABLE_IDENTIFIER
          F = F - 1
        END
     F = F + 1
     UPPER IR.F
   END
 RETURN
 
 
 UPDATE_TABLE_IDENTIFIER:
 
    DO LOOP1 = 0 TO UPDARAYIND
       PRESENT = 'N'
       IF UPDARAY.LOOP1 = TABNAME THEN
         DO
           PRESENT = 'Y'
           LOOP1 = UPDARAYIND
         END
    END
    IF PRESENT = 'N' THEN
      DO
        UPDARAY.UPDARAYIND = TABNAME
        UPDARAYIND = UPDARAYIND + 1
        PRESENT = 'Y'
      END
 
 RETURN
 
 
 DELETE_TABLE_IDENTIFIER:
 
    DO LOOP1 = 0 TO DELARAYIND
       PRESENT = 'N'
       IF DELARAY.LOOP1 = TABNAME THEN
         DO
           PRESENT = 'Y'
           LOOP1 = DELARAYIND
         END
    END
    IF PRESENT = 'N' THEN
      DO
        DELARAY.DELARAYIND = TABNAME
        DELARAYIND = DELARAYIND + 1
        PRESENT = 'Y'
      END
 
 RETURN
 
 
 INSERT_TABLE_IDENTIFIER:
 
   DO LOOP1 = 0 TO INSARAYIND
      PRESENT = 'N'
      IF INSARAY.LOOP1 = TABNAME THEN
        DO
          PRESENT = 'Y'
          LOOP1 = INSARAYIND
        END
   END
   IF PRESENT = 'N' THEN
     DO
       INSARAY.INSARAYIND = TABNAME
       INSARAYIND = INSARAYIND + 1
       PRESENT = 'Y'
     END
 
 RETURN
 
 EXECUTE_TABLE_IDENTIFIER:
 
  FIRST_LINE = F
  DELIM = SUBSTR(IR.F,10,1)
  LINE_TEMP = ''
  NO_LINES = 1
  IR.F = SUBSTR(IR.F,12,LENGTH(IR.F) - 11)
 
  DO UNTIL END_OF_PARA = 'Y'
    IF WORDPOS('/',IR.F) = 1 THEN
      DO
        P = F + 1
        IF SUBSTR(IR.P,1,1) < 2 THEN
          DO
            END_OF_PARA = 'Y'
            LINE.N = LINE_TEMP
          END
      END
    ELSE
      DO
        LINE_TEMP = LINE_TEMP || IR.F
        DO UNTIL INDEX(LINE_TEMP,DELIM) = 0
           IF INDEX(LINE_TEMP,DELIM) = 0 THEN
             DO
               IF LINE_TEMP <> '' THEN
                 DO
                   LINE.N = LINE_TEMP
                   N = N + 1
                 END
             END
           ELSE
             DO
               LINE.N = SUBSTR(LINE_TEMP,1,INDEX(LINE_TEMP,DELIM) - 1)
     LINE_TEMP=SUBSTR(LINE_TEMP,INDEX(LINE_TEMP,DELIM)+1,LENGTH(LINE_TEMP))
               N = N + 1
             END
        END
      NO_LINES = NO_LINES + 1
      END
    F = F + 1
    UPPER IR.F
  END
 
 
  DO LP = 0 TO N
    SQLSTMT1 = SUBSTR(LINE.LP,1,6)
    SELECT
       WHEN SQLSTMT1 = 'DELETE' THEN
         DO
           PARSE VAR LINE.LP SQLSTMT FROM TABNAME REST
           TABNAME = STRIP(TABNAME)
           CALL DELETE_TABLE_IDENTIFIER
         END
       WHEN SQLSTMT1 = 'INSERT' THEN
         DO
           PARSE VAR LINE.LP SQLSTMT INTO TABNAME REST
           TABNAME = STRIP(TABNAME)
           CALL INSERT_TABLE_IDENTIFIER
         END
       WHEN SQLSTMT1 = 'UPDATE' THEN
         DO
           PARSE VAR LINE.LP SQLSTMT TABNAME REST
           TABNAME = STRIP(TABNAME)
           CALL UPDATE_TABLE_IDENTIFIER
         END
       WHEN SQLSTMT1 = 'SELECT' THEN
         NOP
       OTHERWISE
         NOP
    END
  END
 
  F = FIRST_LINE + NO_LINES
 
 RETURN
 
 
 SELECT_TABLE_IDENTIFIER:
 
    DO WHILE WORDPOS('/',IR.F) <> 1
      UPPER IR.F
      IF WORDPOS('FROM',IR.F) > 0 THEN
        DO
          LINE = ' '
          FROMLINE = 'Y'
          CALL GROUP_ORDER_JUSTIFY
          PARSE VAR IR.F WITH TMP 'FROM' LINE
 
          WHERELOOP = INDEX(LINE,'WHERE')
          DO WHILE WHERELOOP < 1 THEN            /* NO WHERE */
              UPPER IR.F
              IF FROMLINE = 'Y' THEN             /* IN 1ST LINE */
               DO
                 TABNO = WORDS(LINE)
                 CALL SELECT_TABLE
                 FROMLINE = 'N'
                 F = F + 1
                 CALL GROUP_ORDER_JUSTIFY
                 WHERELOOP = INDEX(IR.F,'WHERE')
               END
             ELSE
               DO
                 IF WORDPOS('/',IR.F) = 1 THEN
                   DO
                     WHERELOOP = 2           /* FORCING OUT OF LOOP */
                     F = F - 1               /* TO ADJUST FOR INCR  */
                   END                           /*  END  */
                 ELSE
                   DO
                     IF WORDPOS('WHERE',IR.F) = 1 THEN WHERELOOP = 2
                      ELSE
                       DO
                         TABNO = WORDS(IR.F)
                         LINE = IR.F
                         CALL SELECT_TABLE
                         F = F + 1
                         CALL GROUP_ORDER_JUSTIFY
                         WHERELOOP = INDEX(IR.F,'WHERE')
                       END
                   END
               END
          END
        IF FROMLINE = 'Y' THEN                    /* WHERE IN 1ST */
          DO
            TABNO=WORDPOS('WHERE',LINE)-WORDPOS('FROM',LINE)-1
            IF TABNO > 0 THEN CALL SELECT_TABLE
          END
        END
      F = F + 1
     END
 RETURN
 
 
 SELECT_TABLE:
 
   DO TABNO
     PARSE VAR LINE TABNAME NEXT
     TABNAME = STRIP(TABNAME)
     IF LENGTH(TABNAME) > 5 THEN
       DO
         SELECT
          WHEN INDEX(TABNAME,',') = 1 THEN
             TABNAME = SUBSTR(TABNAME,2,LENGTH(TABNAME))
          WHEN INDEX(TABNAME,',') > 2 THEN
             TABNAME = SUBSTR(TABNAME,1,LENGTH(TABNAME)-1)
          OTHERWISE
             NOP
         END
         IF SUBSTR(TABNAME,1,2) = 'PS' THEN
           DO
             DO LOOP1 = 0 TO SELARAYIND
               PRESENT = 'N'
               IF SELARAY.LOOP1 = TABNAME THEN
                 DO
                   PRESENT = 'Y'
                   LOOP1 = SELARAYIND
                 END
               END
               IF PRESENT = 'N' THEN
                 DO
                   SELARAY.SELARAYIND = TABNAME
                   SELARAYIND = SELARAYIND + 1
                   PRESENT = 'Y'
                 END
           END
       END
     LINE = NEXT
   END
 RETURN
 
 GROUP_ORDER_JUSTIFY:
 
   SELECT
      WHEN INDEX(IR.F,'GROUP BY') > 0 THEN
        DO
         T1   =  SUBSTR(IR.F,1,INDEX(IR.F,'GROUP BY') - 1)
         T2   =  'WHERE   '
         T3   =  SUBSTR(IR.F,INDEX(IR.F,'GROUP BY') + 9,LENGTH(IR.F))
         IR.F =  T1 || T2 || T3
        END
      WHEN INDEX(IR.F,'ORDER BY') > 0 THEN
        DO
         T1   =  SUBSTR(IR.F,1,INDEX(IR.F,'ORDER BY') - 1)
         T2   =  'WHERE   '
         T3   =  SUBSTR(IR.F,INDEX(IR.F,'ORDER BY') + 9,LENGTH(IR.F))
         IR.F =  T1 || T2 || T3
        END
      WHEN INDEX(IR.F,'HAVING') > 0 THEN
        DO
         T1   =  SUBSTR(IR.F,1,INDEX(IR.F,'HAVING') - 1)
         T2   =  'WHERE '
         T3   =  SUBSTR(IR.F,INDEX(IR.F,'HAVING') + 9,LENGTH(IR.F))
         IR.F =  T1 || T2 || T3
        END
      OTHERWISE
         NOP
   END
 RETURN
 
 
 WRITE_OUTPUT_STEM:
 
   OR.ORNO   = ' '
   ORNO      = ORNO + 1
 
   PARSE VAR IR.6 V1 V2 V3 V4 V5 V6 V7 V8
   OUT_LINE3 = '==================  '||'==================  '
   OUT_LINE4 = '==================  '||'==================  '
   LINE1     = JUSTIFY('*',35,'*')
   LINE2     = '               *                                   *'
   LINE3     = '               *       C R U D  M A T R I X        *'
   LINE4     = CENTER(V7,35,' ')
   LINE5     = CENTER('Trace Dated :'||V2,35,' ')
   LINEPADS  = '*'
   LINEPADP  = '               *'
 
   LINE1 = LINEPADP || LINE1 || LINEPADS
   LINE4 = LINEPADP || LINE4 || LINEPADS
   LINE5 = LINEPADP || LINE5 || LINEPADS
 
   OR.ORNO   = LINE1
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE2
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE3
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE4
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE5
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE2
   ORNO      = ORNO + 1
 
   OR.ORNO   = LINE1
   ORNO      = ORNO + 1
 
   OR.ORNO   = ' '
   ORNO      = ORNO + 1
 
   OR.ORNO   = OUT_LINE3 || OUT_LINE4
   ORNO      = ORNO + 1
 
   OUT_LINE1 = 'SELECT              '||'INSERT              '
   OUT_LINE2 = 'UPDATE              '||'DELETE              '
   OR.ORNO   = OUT_LINE1 || OUT_LINE2
   ORNO      = ORNO + 1
 
   OR.ORNO   = OUT_LINE3 || OUT_LINE4
   ORNO      = ORNO + 1
 
   MAXIMUM = MAX(SELARAYIND,UPDARAYIND,INSARAYIND,DELARAYIND)
 
   DO LINE = 0 TO MAXIMUM
 
     IF LINE > SELARAYIND THEN
       SELARAY.LINE = ''
     IF LINE > UPDARAYIND THEN
       UPDARAY.LINE = ''
     IF LINE > DELARAYIND THEN
       DELARAY.LINE = ''
     IF LINE > INSARAYIND THEN
       INSARAY.LINE = ''
 
     SELECT = LEFT(SELARAY.LINE,20,' ')
 
     INSERT = LEFT(INSARAY.LINE,20,' ')
 
     UPDATE = LEFT(UPDARAY.LINE,20,' ')
 
     DELETE = LEFT(DELARAY.LINE,20,' ')
 
     OUT_LINE=SELECT || INSERT || UPDATE || DELETE
 
     OR.ORNO   = OUT_LINE
     ORNO      = ORNO + 1
 
   END
 RETURN
 
 WRITE_OUTPUT:
 
   ODSN = USER || '.TEMP.' || 'CRUD'
   "DELETE '"ODSN"'"
   "FREE F(ODD)"
   "ALLOC F(ODD) DA('"ODSN"')",
          "NEW CATALOG CYLINDERS SPACE(1,1)",
          "LRECL(80) RECFM(F B) BLKSIZE(0)"
   "EXECIO * DISKW ODD (STEM OR. FINIS"
   "FREE F(ODD)"
 
   say V7 'CRUD written to dataset : ' ODSN
   say 'Thank You for using this Marco'
 
 RETURN