eTutorials.org

Appendix B. parseSQL.pl

The following PERL program will parse the output of the SQL snapshot and convert the information to comma-delimited format so that it can be imported into a spreadsheet.

#!/usr/local/bin/perl -w
#!/usr/bin/perl -w
#use English;

#==================================================================
#
# parseSQL.pl
#
# Parses a Dynamic SQL snapshot, outputs the report lines in delimited
# ascii format so that is can be read into a spread sheet
# for analysis and sorting of the output.
#
# USAGE:
# parseSQL.pl input-file "string to start a new row in the output file"
# match_character output-file
#
# e.g. parseSQL.pl dynsql.out "^ Number of executions" = > temp.out
# The above commands will open and parse the file dynsql.out
# and write the information on the right side of the '='
# and write a new line each time it finds "^ Number of executions"
#
# example command
# perl parseSQL.pl sql.snap "^ Number of executions" = > temp.txt
#
#==================================================================

if ( $#ARGV < 2 )
   {
     die "For usage info - Read the comments at the top of the source file\n\n";
   }

my $file = $ARGV[0];
my $filetmp = $file . ".temp-file1";
open (FILE,$file) || die "Cannot read from $file";
open (TMP, ">$filetmp") || die "Cannot write to $filetmp\n";
my $counter = 0;

#print $ARGV[1];

$title = "";
$count=0;
while (<FILE>) {
   #$counter++ if s/=/x/gi;
   chomp;
   if ( m/$ARGV[1]/gi )
   {
      $count = $count + 1;
      if ($count > 0 )
         {print TMP "\n";}
   }
   $temppos = index($_, $ARGV[2]);
   #print  TMP "\n$_\n";
   @words = split(/$ARGV[2]/, $_);
   #if ($#words >= 1)
   if ($temppos >= 1)
   {
      if ($count == 1)
      {
          grep(s/ *$|\t|\n//, $words[0]);
          grep(s/^ *|\t|\n//, $words[0]);
          $title = $title . $words[0] . "\t";
      }
      #print TMP "$words[1]\t";
      #print  TMP "\n$_\n";
      $temps = substr($_, $temppos+1);
      print TMP "$temps\t";
   }
   else
   {
     #print "\nstart\n" ;
   }
}

print "$title\n";

close TMP;

open (TMP, "<$filetmp") || die "Cannot read from $filetmp\n";
while (<TMP>)
{
   print $_;
}