Status: Unverified
GOAL:
How to compare two DBANALYS Files
GOAL:
How to see where a database has changed (during a performance and tuning)
GOAL:
Perl Program to compare Two DBANALYS Files
FACT(s) (Environment):
Windows
UNIX
FIX:
The program is run as follows:
perl dbanalcomp.cgi dbanalys1.txt dbanalys2.txt (Windows)
dbanalcomp.cgi dbanalys1.txt dbanalys2.txt (UNIX)
The output is displayed on the screen and also is written to a log file (log.txt).
#!/usr/bin/perl
#
# Perl program to process two DBANALYS output files, extract the
# information from the TABANALYS segments and compare them.
#
# Author: Steven C. Robbins
# Progress Software Technical Support
# 1/21/1001
#
# Revised: 3/7/01 - fixed code to expand metric suffixes
#
# Disclaimer: This program is offered as-is, with no warranties
# expressed or implied.
#
# Command lines:
#
# dbanalcomp
# Set the log file name
$log_file = "log.txt";
$count = @ARGV;
if ($count > 1) {
$input_file1 = @ARGV[0];
$input_file2 = @ARGV[1];
if (@ARGV[2] ne "") {
$log_file = @ARGV[2];
}
open(LOGFILE, ">$log_file") || die "Can't open file $log_file";
} else {
print "Invalid program syntax - command:\n";
print "\n";
print "dbanalcomp \n";
exit(0);
}
print "\n";
print "DBANALYS input file 1: $input_file1\n";
print "DBANALYS input file 2: $input_file2\n\n";
print LOGFILE "DBANALYS input file 1: $input_file1\n";
print LOGFILE "DBANALYS input file 2: $input_file2\n\n";
%tablestat1 = process_dbanalys($input_file1);
%tablestat2 = process_dbanalys($input_file2);
# Check the existance of tables from DBANALYS #1 against #2.
# If the table is in both reports, check the record count.
print "Checking DBANALYS 1 against DBANALYS 2...\n";
print LOGFILE "Checking DBANALYS 1 against DBANALYS 2...\n";
foreach $table (sort keys %tablestat1) {
$mytable = $table;
if ($table eq "\177Totals:") {
$mytable = "Totals:"
}
if ($tablestat2{$table}[0] eq "") {
print "Table $mytable missing from $input_file2\n";
print LOGFILE "Table $mytable missing from $input_file2\n";
} elsif ($tablestat1{$table}[0] ne $tablestat2{$table}[0]) {
print "Table $mytable record count mismatch\n";
print "Analysis 1: $tablestat1{$table}[0] - ",
"analysis 2: $tablestat2{$table}[0]\n";
print LOGFILE "Table $mytable record count mismatch\n";
print LOGFILE
"Analysis 1: $tablestat1{$table}[0] - ",
"analysis 2: $tablestat2{$table}[0]\n";
}
}
# Check the existance of tables from DBANALYS #2 against #1.
# Don't check the record counts, since we did them in the
# previous loop.
print "Checking DBANALYS 2 against DBANALYS 1...\n";
print LOGFILE "Checking DBANALYS 2 against DBANALYS 1...\n";
foreach $table (sort keys %tablestat2) {
$mytable = $table;
if ($table eq "\177Totals:") {
$mytable = "Totals:"
}
if ($tablestat1{$table}[0] eq "") {
print "Table $mytable missing from $input_file1\n";
print LOGFILE "Table $mytable missing from $input_file1\n";
}
}
print "Processing complete.\n";
print LOGFILE "Processing complete.\n";
close(LOGFILE);
exit(0);
# Subroutine to trim the metric suffixes the V9 puts on numbers
# in the DBANALYS report (i.e. k = 1000, m = 1000000, etc)
sub trim_suffix {
my ($val) = @_;
$val =~ s/\.0B\b//i;
$val =~ s/K\b/00/i;
$val =~ s/M\b/00000/i;
$val =~ s/G\b/00000000/i;
$val =~ s/T\b/00000000000/i;
$val =~ s/\.//i;
return $val;
}
# Subroutine to build a hash/array structure with the names
# of the tables as the key and the array of DBANALYS data
# as its value.
sub process_dbanalys {
my($dbanalys_file) = @_;
my %hashresults = ();
# Process the DBANALYS file as follows:
#
# Read until we find the string "RECORD BLOCK SUMMARY".
# Read the analysis information for each table. Stop when
# we read a line where the first token is "Totals:"
$processing_records = 0;
open(ANALYSIS, "$dbanalys_file") ||
die "Can't open file $dbanalys_file";
#. Start processing the DBANALYS
while ($line = <ANALYSIS>) {
# Discard any blank lines
while ($line eq "\n") {
$line = <ANALYSIS>;
}
# Remove any multiple spaces from the line and split it. There are a
# maximum of nine fields (in any of the lines we care about).
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
# Check to see if we've hit the end of the block.
# If so, process the totals fields just like a table.
if ((@fields[0] eq "Totals:") && ($processing_records)) {
if (@fields[8] ne "") {
@fields[1] = trim_suffix(@fields[1]);
@fields[2] = trim_suffix(@fields[2]);
@fields[3] = trim_suffix(@fields[3]);
@fields[4] = trim_suffix(@fields[4]);
@fields[5] = trim_suffix(@fields[5]);
@fields[6] = trim_suffix(@fields[6]);
$tablename = @fields[0];
$reccount = @fields[1];
$tablesize = @fields[2];
$recmin = @fields[3];
$recmax = @fields[4];
$recmean = @fields[5];
$fragcnt = @fields[6];
$fragfact = @fields[7];
$scatfact = @fields[8];
# Add an octal 177 to the beginning of "Totals" to make it appear
# at the end of the list.
$tablename = "\177".$tablename;
$hashresults{$tablename} =
[$reccount, $tablesize, $recmin, $recmax,
$recmean, $fragcnt, $fragfact, $scatfact];
} else {
print "Error processing totals\n";
print LOGFILE "Error processing totals\n";
}
close(ANALYSIS);
return %hashresults;
}
# Check to see if we've hit the record block summary for an area
# while processing records. Since there is no delimiter between
# record block summaries for each area, we need to discard the
# next three lines (they are header information).
if ((@fields[0] eq "RECORD") && (@fields[1] eq "BLOCK") &&
(@fields[2] eq "SUMMARY") && (@fields[3] eq "FOR") &&
(@fields[4] eq "AREA") && ($processing_records)) {
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
}
# Split the fields into eight values (table name, record count,
# size, min record size, max record size, mean record size,
# fragment count, fragment factor and scatter factor).
if (($processing_records) &&
(substr(@fields[0],0,5) ne "-----")) {
if (@fields[8] ne "") {
@fields[1] = trim_suffix(@fields[1]);
@fields[2] = trim_suffix(@fields[2]);
@fields[3] = trim_suffix(@fields[3]);
@fields[4] = trim_suffix(@fields[4]);
@fields[5] = trim_suffix(@fields[5]);
@fields[6] = trim_suffix(@fields[6]);
$tablename = @fields[0];
$reccount = @fields[1];
$tablesize = @fields[2];
$recmin = @fields[3];
$recmax = @fields[4];
$recmean = @fields[5];
$fragcnt = @fields[6];
$fragfact = @fields[7];
$scatfact = @fields[8];
$hashresults{$tablename} =
[$reccount, $tablesize, $recmin, $recmax,
$recmean, $fragcnt, $fragfact, $scatfact];
} else {
print "Error processing table @fields[0]\n";
print LOGFILE "Error processing table @fields[0]\n";
}
}
# Check to see if we've hit the record block summary.
if ((@fields[0] eq "RECORD") && (@fields[1] eq "BLOCK") &&
. (@fields[2] eq "SUMMARY")) {
$processing_records = 1;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
print "Processing records...\n";
print LOGFILE "Processing records...\n";
}
}
close(ANALYSIS);
return %hashresults;
}
.