eTutorials.org

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;
}