Appendix C. exfmtDIF.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/bin/perl # #  exfmtDIF.pl - Find differences between access plans obtained by #  the db2exfmt utility # #  Description: #  exfmtDIF will identify differences in two #  access plans obtained using the db2exfmt tool with -g option. # #  Details: #  For each statement that is the same in the two plans, #  retreive the access plan and compare them. # #  - If the second file contains a query not found in the first, #    the difference will not be revealed. # use English; if (scalar(@ARGV) != 2) {   print "$PROGRAM_NAME requires 2 plans to compare\n";   exit; } $filename1 = @ARGV[0]; $filename2 = @ARGV[1]; open(FILE1, "<$filename1"); open(FILE2, "<$filename2"); @file1 = <FILE1>; @file2 = <FILE2>; close(FILE1); close(FILE2); main(); # # Main body of program.  Gets the statements and plans for each # file and compares queries and plans to find differences # sub main2 {   my(@queries1) = get_statements(@file1);   my(@queries2) = get_statements(@file2);   my(@plans1) = get_plans(@file1);   my(@plans2) = get_plans(@file2);   my($querycount1) = $#queries1+1;   my($querycount2) = $#queries2+1;   my($index1) = 0;   my($found) = 0;   #  For each query from the first file   while ($index1 < $querycount1)   {     my($index2) = 0;       # Compare to every query in the second file     while ($index2 < $querycount2)     {       # If the queries are the same, compare the associated access       # plans       if (@queries1[$index1] eq @queries2[$index2])       {         my($i) = $index1 + 1;         my($j) = $index2 + 1;         print "$filename1, plan \#$i  vs.  $filename2, plan \#$j: ";         if (compare_plans(@plans1[$index1], @plans2[$index2]))         {           print "same\n";         }         else         {           print "**DIFFERENT**\n";           print "$filename1, plan \#$i:\n";           my($fmtplan) = fmt_plan(@plans1[$index1]);           print "$fmtplan\n";           print "$filename2, plan \#$j:\n";           $fmtplan = fmt_plan(@plans2[$index2]);           print "$fmtplan\n";         }         $index2 = $querycount2;         $found = 1;       }       $index2++;     }     if ($found == 0)     {       print "QUERY:\n@queries1[$index1]\n\n not found in $filename2";     }     $index1++;   } } # # Get all the 'Original Statement' queries from the # given array of text # sub get_statements {   my(@data) = @_;   my(@stmts) = ();   while (@data)   {     if (shift(@data) =~ /^Original Statement:/)     {       shift(@data);       my($line) = shift(@data);       # Get the original statement block       my(@stmt) = ();       while ($line !~ /^\s*$/)       {         push(@stmt, $line);         $line = shift(@data);       }       push(@stmt, "\n");       push(@stmts,join(' ',@stmt));     }   }   return @stmts; } # # Get all the access plans from the inputted text array # sub get_plans {   my(@data) = @_;   my(@plans) = ();   while (@data)   {     if (shift(@data) =~ /^Access Plan:/)     {       shift(@data);       shift(@data);       shift(@data);       shift(@data);       my($line) = shift(@data);       # Get the access plan block       my(@plan) = ();       while ($line !~ /^\s*$/)       {         push(@plan, $line);         $line = shift(@data);       }       push(@plan, "\n");       push(@plans,join(' ',@plan));     }   }   return @plans; } # # Compares the two access plan strings # Returns 1 if they are the same, otherwise, returns 0 # sub compare_plans {   my($plan1,$plan2) = @_;   $plan1 = fmt_plan($plan1);   $plan2 = fmt_plan($plan2);   if ($plan1 eq $plan2)   {     return 1;   }   else   {     return 0;   } } # # Formats an access plan string to discard information # that we do not care about when comparing access plans # sub fmt_plan {   my($plan) = @_;   $plan =~ s/\d*e\+\d*//g;    # take out exponent   $plan =~ s/[^a-zA-Z]\d/  /g;# replace numbers not beside a letter with blanks   $plan =~ s/[^a-zA-Z]\d/  /g;# replace numbers not beside a letter with blanks   $plan =~ s/\./ /g;          # replace periods with blanks   $plan =~ s/\(/ /g;          # replace brackets with blanks   $plan =~ s/\)/ /g;   $plan =~ s/\n\s+\n/\n/g;    # take out blank lines   return $plan; } 


Advanced DBA Certification Guide and Reference for DB2 UDB v8 for Linux, Unix and Windows
Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows
ISBN: 0130463884
EAN: 2147483647
Year: 2003
Pages: 121

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net