Shell Programming and Scripting

20/11/11
SQL CD : 100
query
select name,class from table school

date=`head -1 file`
sql=`head -2 file | tail -1`
fullfile=`cat file`

echo $date,$sql,$fullfile > CSV_file

                           SYSTEMS AREA

                   PRODUCTION PROBLEM/ABEND DATA


 1.  RUN DATE: 06/06/11  2. SYSTEM:   EP0Z     3. PROBLEM TYPE: S222
               --------               ----                      ----
 4.  JOB NUMBER:  J9140  5. JOB NAME: EP0Z080P 6. STEPNAME: EPZSTP06
                  -----               --------              --------
 7.  TIME NOTIFIED: 01:17       8. WHO NOTIFIED: VAIBHAV MATHUR
                    -----------                  -------------------
                       9. HELPERS:
                                   ---------------------------------

 10. WHAT WAS WRONG:

   JOB WAS LOOPING :- RUNNING SINCE 19.54 SO I CANCELLED AT 01:17 ON         RED
   06/07/11

 11. WHEN WAS THE PROBLEM CORRECTED:

     CHCECKED EARLIER SCENARIO IN EP00T.ABEND.DATA WHICH OCCURRED ON
     02/02/2011. BASED ON THAT, DELETED THE SUBSCRIBER FROM INPUT FILE
     EP00T.EPZ00080.INPUT.TRIGG.FEB0211G

     1) REPORT :- EPZ00804, LAST PROCESS RECORD AND NEXT RECORD.
       142924152
       142924164
     2) REPORT :- EPZ00802, LAST PROCESS RECORD AND NEXT RECORD.
       141844527
       141844665
     3) REPORT :- EPZ00803, LAST PROCESS RECORD AND NEXT RECORD.
       141460794
       141461097

     JOB WAS RESTARTED FROM THE ABENDED STEP AT 02:16
     WITH OVERRIDE
     //EPZSTP06.TRIGGFLI DD DSN=EP00T.EPZ00080.INPUT.TRIGG.JUN0711

      JOB AGAIN WENT INTO INFINITE LOOP. SUBSEQUENTLY IDENTIFIED THE
      SUBSCRIBER CAUSING THE ISSUE, USING THE BELOW SET OF QUERIES -

      STEP 1 -
      FOUND THE TIMESTAMP OF LAST DAY'S RUN USING QUERY
      SELECT * FROM PROD01.BTCH_CYCL_PARM
      WHERE CYCL_TYP_CD = 'NMS'
      ORDER BY CYCL_STRT_TMP DESC
      WITH UR;
      TIMESTAMP WAS 2011-06-04-18.01.08.922740

      STEP 2 -
      SELECT SUBS_ID, MAIN_GRP_ID, SGRP_ID
       FROM PROD01.SUBS_PRD
      WHERE PRD_ID LIKE 'P%'
        AND SP_LST_UPD_TMP > '2011-06-04-18.01.08.922740'
      GROUP BY SUBS_ID, MAIN_GRP_ID, SGRP_ID
      HAVING COUNT(*) >= 3
      WITH UR;

      THIS QUERY GAVE FOLLOWING RESULT -
      SUBS_ID     MAIN_GRP_ID  SGRP_ID
      143222959   00001Y       006
      153705667   05938S       078

      STEP 3 -
      FOR BOTH THESE SUBSCRIBERS, EXECUTED FOLLOWING QUERY:
      SELECT * FROM PROD01.SUBS_PRD
      WHERE MAIN_GRP_ID = '00001Y' AND SGRP_ID = '006' AND SUBS_ID =
      '143222959'
        AND PRD_ID LIKE 'P%' AND SP_LST_UPD_TMP >
      '2011-06-04-18.01.08.922740'
      UNION ALL
      SELECT * FROM PROD01.SUBS_PRD_SS
      WHERE MAIN_GRP_ID = '00001Y' AND SGRP_ID = '006' AND SUBS_ID =
      '143222959'
        AND PRD_ID LIKE 'P%' AND SP_LST_UPD_TMP >
      '2011-06-04-18.01.08.922740'
      ORDER BY SP_LST_UPD_TMP DESC WITH UR
      SIMILAR QUERY FOR THE OTHER SUBSCRIBER.

     RESULT -

     MAIN
     GRP     SGRP  PRD      SUBS         SP          SP
      ID      ID   ID        ID         EFDT        TRMDT
     ------  ----  -------  ----------  ----------  ----------
     00001Y  006   P1006    143222959   05/01/2009  06/01/2011
     00001Y  006   P1006    143222959   05/01/2009  12/31/9999
     00001Y  006   P1006    143222959   02/01/2009  05/01/2009
     00001Y  006   P1006    143222959   01/01/2009  01/01/2009
     00001Y  006   P1006    143222959   05/01/2009  05/01/2009


     MAIN
     GRP     SGRP  PRD      SUBS         SP          SP
      ID      ID   ID        ID         EFDT        TRMDT
     ------  ----  -------  ----------  ----------  ----------
     05938S  078   P1615    153705667   06/01/2011  12/31/9999
     05938S  078   P1615    153705667   05/07/2011  06/01/2011
     05938S  078   P1615    153705667   05/07/2011  12/31/9999
     05938S  078   P1615    153705667   10/28/2010  05/07/2011

     STEP 4 -
     CHECK BELOW CONDITIONS-
     - EFFECTIVE DATE OF THE FIRST RECORD SHOULD BE GREATER THAN 3RD OR
       GREATER RECORD.
     - 1ST RECORD SHOULD NOT BE A NULLED RECORD.

     OF THESE TWO SUBSCRIBERS, SUBSCRIBER 143222959 DOESNT SATISFY
     FIRST CONDITION.
     THE FIRST ROW HAS AN EFFECTIVE DATE 05/01/2009, AND ALSO THE 5TH
     ROW HAS THE SAME EFFECTIVE DATE.
     THE 1ST ROW'S DATE SHOULD IDEALLY BE GREATER TO AVOID THIS ISSUE.

     STEP 5 -
     DELETED SUBSCRIBER 143222959 FROM THE ORIGINAL INPUT FILE, AND
     PROVIDED FOLLOWING OVERRIDE -
     JOB WAS RESTARTED FROM THE ABENDED STEP AT 03:48
     WITH OVERRIDE
     //EPZSTP06.TRIGGFLI DD DSN=EP00T.EPZ00080.INPUT.TRIGG.JUN0711B

     THE JOB COMPLETED SUCCESSFULLY THIS TIME AT 04:27

 13. FINAL STATUS:

     THE JOB COMPLETED TO CREATE OUTPUT FILE FOR NDM TO CAREMARK
     SUCCESSFULLY.


 14. PRODUCTION SUPPORT RESPONSE:
                                                    SC TICKET# IM213122

#!/bin/ksh

filename=`ls $1 | grep -v ".*.csv"`
output=output`date +%m%d%Y`.csv
cd $1
echo $filename

ls $filename|while read filename
do
jdate=`grep 'RUN DATE' $filename | awk '{print $4}'`
jtime=`grep 'TIME NOTIFIED' $filename | awk '{print $4}'`
jname=`grep "JOB NAME" $filename | awk '{print $8}'`
jstepname=`grep "STEPNAME" $filename | awk '{print $11}'`
jprogramname=`grep "PROGRAM" $filename | awk '{print $6}'`
jproblemtype=`grep 'PROBLEM TYPE:' $filename | awk '{print $11}'`
jsqlcode=`sed -nr 's/.*SQL CD : *([-+]*[0-9]*).*/\1/p' $filename | head -1`

jproblem=`sed -n '/10\./,/11\./ p' $filename | egrep -v "10\.|11\.|^$|------------------" | tr "\n" '\.'`
jtemp=`cat $filename`

echo `cat $filename` > temp

jtotal=`cat temp`


if [[ `ls | grep $output` ]]
then
echo File is exist.
else
echo File is not exist.
echo 'Job Date'';''Job Time'';''Job Name'';''Step Name'';''Program Name'';''Error Code'';''SQL CODE'';''Problem'';''Incident & Solution' > $output
fi



echo -e "\nStart appending......"
echo "$jdate"';'"$jtime"';'"$jname"';'"$jstepname"';'"$jprogramname"';'"$jproblemtype"';'"$jsqlcode"';'"$jproblem"';'"$jtotal" >>$output
echo  -e "done\n"
done

rm temp

$ cat script.pl
use warnings;
use strict;

my %csv;
my @headers = (
        [ 'run date' => 'Job Date' ],
        [ 'time notified' => 'Job Time' ],
        [ 'job name' => 'Job Name' ],
        [ 'stepname' => 'Step Name' ],
        [ 'program name' => 'Program Name' ],
        [ 'problem type' => 'Error Code' ],
        [ 'sql code' => 'SQL Code' ],
        [ 'problem' => 'Problem' ],
        [ 'solution' => 'Incident & Solution' ]
);


while ( my $line = <> ) {

        ## Get headers and its data between points 1 and 10.
        if ( $line =~ /^\s*1\./ .. $line =~ /^\s*$/ ) {
                chomp $line;
                next unless $line =~ /^\s*\d+\./;
                my @points = split /\s*\d+\.\s+/, $line;
                for my $point ( @points ) {
                        next if index( $point, ":" ) == -1;
                        my ($h, $d) = split /\s*:\s+/, $point;
                        $csv{ $h } = $d;
                }
        }

        ## Get header and its data of point 10.
        if ( ( $line =~ /^\s*10\./ .. $line =~ /^\s*11\./ ) =~ /^\d+(?<!^1)$/ ) {
                $csv{ PROBLEM } .= $line;
        } 

        ## Get header and its data of point 11.
        if ( ( $line =~ /^\s*11\./ .. $line =~ /^\s*13\./ ) =~ /^\d+(?<!^1)$/ ) {
                $csv{ SOLUTION } .= $line;
        }
}

## Remove leading and trailing spaces from next headers.
$csv{ PROBLEM } =~ s/^\s*(.*\S)\s*$/$1/s;
$csv{ SOLUTION } =~ s/^\s*(.*\S)\s*$/$1/s;

## Print header.
print join "|", map { ${$headers[$_]}[1] } 0 ..$#headers;
print "\n";

## Print lines of data.
print join "|", map { $csv{ uc ${$headers[$_]}[0] } || "" } 0 ..$#headers;
print "\n";
$ perl script.pl infile
... output suppressed ...

#!/bin/ksh

filename=`ls $1 | grep -v ".*.csv"`
output=output`date +%m%d%Y`.csv
cd $1
echo $filename

ls $filename|while read file
do
jdate=`grep 'RUN DATE' $file | awk '{print $4}'`
jtime=`grep 'TIME NOTIFIED' $file| awk '{print $4}'`
jname=`grep "JOB NAME" $file| awk '{print $8}'`
jstepname=`grep "STEPNAME" $file | awk '{print $11}'`
jprogramname=`grep "PROGRAM" $file | awk '{print $6}'`
jproblemtype=`grep 'PROBLEM TYPE:' $file | awk '{print $11}'`
jsqlcode=`sed -nr 's/.*SQL CD : *([-+]*[0-9]*).*/\1/p' $file | head -1`

jproblem=`sed -n '/10\./,/11\./ p' $file | egrep -v "10\.|11\.|^$|------------------" | tr "\n" '\.'`
jtemp=`cat $file`

echo `cat $file` > temp

jtotal=`cat temp`


if [ -f $output ]
then
echo File is exist.
else
echo File is not exist.
echo 'Job Date'';''Job Time'';''Job Name'';''Step Name'';''Program Name'';''Error Code'';''SQL CODE'';''Problem'';''Incident & Solution' > $output
fi



echo -e "\nStart appending......"
echo "$jdate"';'"$jtime"';'"$jname"';'"$jstepname"';'"$jprogramname"';'"$jproblemtype"';'"$jsqlcode"';'"$jproblem"';'"$jtotal" >>$output
echo  -e "done\n"
done

rm temp

Problem
this is a problem I want the output as I have in my file. but this gave me in one line

Problem
this is a problem I want the output as I have in my file. 
but this gave me in one line