#!/usr/local/bin/perl
################################################################################
# perlsch : Front end for dbschema on 5.x platform
# This script will take a dbspace, database and optional table name
# as arguments, prompt to clone or not and if not cloning,
# prompt for the number of records to save for each
# table, and output the schema with dbspace, extent size (30%) lager
# and lock mode row to a file dbname.sch. or table_name.sch
# Douglas R. Probst 01-04-98
#
# NOTE: This is written for AIX pages are 4K
################################################################################
$numofargs = @ARGV; # number of args
$dbspace = shift; # argval 1
$dbname = shift; # argval 2
$tablename = shift; # argval 3
# initialize variables
$cnt = 0;
$clone = 0;
$recs_to_keep = 0;
$next_size = 0;
if ($numofargs == 3 ) {
$dbschemastring = "dbschema -d ".$dbname." -t ".$tablename;
$out_name = $tablename;
}elsif ($numofargs == 2 ){
$dbschemastring = "dbschema -d ".$dbname ;
$out_name = $dbname;
}else{
print "USAGE: perlsch dbspace dbname [tabname] \n";
exit
}
&get_clone_from_user; # ask user if they want to clone the table extents
system ("$dbschemastring > /tmp/dbschemaout1"); # run dbschema to file
$outputfile1 = "/tmp/dbschemaout1";
$outputfile2 = "/tmp/dbschemaout";
#Open tmp output file for read and print message if the open fails
if (!open(OUTPUTFILE1,"<$outputfile1")) {
print "Unable to open OUTPUTFILE1 $outputfile1\n";
print "ERROR: $!\n";
exit;
}
#Open tmp output file for write and print message if the open fails
if (!open(OUTPUTFILE2,">$outputfile2")) {
print "Unable to open OUTPUTFILE2 $outputfile2\n";
print "ERROR: $!\n";
exit;
}
&fix_up_schema(); # fix bug in dbschema version 5.x
system("> $out_name.sch"); # remove old schema output file
open (OUTPUTFILE, ">> $out_name.sch ") || die "Can't open $dbname.sch for write";
open( DBSCH, "cat /tmp/dbschemaout |") || die "Can't open dbschema\n";
while () {
if (/revoke/||/DBSCH/||/Copyright/||/Software/) { # skip lines with paterns
next;
}
if (/^{ TABLE\s+\W\w+\W\.(\w+)\s+row size = (\d+)\s+.* index size =\s+(\d+)/) {
&write_the_file("\n");
$tablestring = $_; # grab intire string in variable
$tablename = $1; # grab table name
$rowsize = $2; # grab row size
$indexsize = $3; # grab index size
if ($clone != 1) { # not cloning
&get_num_rows_from_user; # call sub routine to get user data
}
&get_systable_info; # call sub routine to get # of indexs
&calc_extents; # call sub routine to calc extent
&write_the_file($tablestring); # call sub routine to write to file
next; # skip to next iteration of loop
}
if (/create index/||/create unique/||/create cluster/) {
&write_the_file($_); # call to sub routine to write to file
next; # skip to next iteration of loop
}
if (/ \);/) { # if end of table is found );
&write_the_file(")\n"); # write the ) and a carriage return
&write_the_file("in $dbspace\n"); # write "in dbspace name
if ($extent_size > 0) { # if user put in data write extent si
&write_the_file("extent size $extent_size");
}
if ($next_size > 0) { # when cloning
&write_the_file("next size $next_size \n");
}
&write_the_file("lock mode row ; \n"); # "write lock mode row ;"
next; # skip to next iteration of loop
}
if (/grant.*to.*/) {
if ($cnt == 0) {
++$cnt;
&write_the_file("grant dba to public;");
next;
}else{
next;
}
}
&write_the_file("$_"); # Write the line if all else
# falls through
}
close DBSCH; # close dbschema
&write_the_file("grant resource to public;\n"); # make sure everyone can connect
&write_the_file("grant dba to public;\n");
&write_the_file("grant connect to public;\n");
close OUTPUTFILE; # close output file
printf "%-25s %-17d %-18s \n", "You will need a total of ", " $total_k_bytes", " K bytes of space";
system("echo total k for $dbname $total_k_bytes > /tmp/$dbname.tot.k");
################################################################################
# Sub routine to get # of records to keep for the given table.
################################################################################
sub get_num_rows_from_user {
print "Enter number of records to keep for $tablename >> ";
chop($recs_to_keep = );
}
################################################################################
# Sub routine to get info from user on cloning
################################################################################
sub get_clone_from_user {
print "Do you what to clone tables from database being read? [Yy/Nn] >> ";
chop($ans = );
if ($ans =~ /y/i) {
$clone = 1;
}
}
################################################################################
# sub routine to get the number of indexes for the table.
# open file handle called ISQL which consists of echoing a command to isql and
# catching the output in $tabid. Then using $tabid open another filehand and
# get the number of indexes for that $tabid and put it in $numofidx
################################################################################
sub get_systable_info {
open (ISQL, "echo \"select nindexes, fextsize, nextsize from systables where tabname = '$tablename'\" | isql -s $dbname 2>/dev/null |") || die "Can't open isql\n";
isqlloop: while () {
if (/^\s+(\d+)\s+(\d+)\s+(\d+)\s+/) {
$numofidx =$1;
if($clone) { # if cloning get extent sizes
$extent_size = $2;
$next_size = $3;
}
last isqlloop;
}
}
close ISQL;
}
################################################################################
# Sub routine to calculate extent size (includes 30% for growth) taken from
# calc_exten.4gl NOTE: int returns an integer value of whatever is passed to it
################################################################################
sub calc_extents {
if ($recs_to_keep == 0 && $clone != 1) { # no user data an not cloning
$extent_size = 32; # so set default table size
$total_k_bytes += $extent_size;
return; # return with out calculating extent
}
if ($clone == 1) { # user is cloning extents from tables
$total_k_bytes += $extent_size;
return; # return with out calculating extent
}
$tmpsize = ($indexsize * 1.25) + ($numofidx * 8);
$idxspace = int (($tmpsize * $recs_to_keep) / 1024);
if (($tmpsize * $recs_to_keep) % 1024 != 0) {
++$idxspace;
}
$page_size = 4096;
$rows_page = int(($page_size - 32) / ($rowsize + 4));
$num_pages = int($recs_to_keep / $rows_page);
if (($recs_to_keep % $rows_page) != 0) {
++$num_pages;
}
$page_space = int($num_pages * $page_size);
$data_space = ($page_space / 1024);
if (($page_space % 1024) != 0) {
++$data_space;
}
$extent_size = (($data_space + $idxspace) * 1.300);
if ($extent_size < 32) { # if first ext is smaller than default us def.
$extent_size = 32;
}
$total_k_bytes += $extent_size;
}
################################################################################
# Sub routine to write what ever is passed into the output file.
# if line comming through function is the n extent size we need to format
# with printf because the $extent_size is in decimal form out to about 8 poss.
################################################################################
sub write_the_file {
if ($_[0] =~ /extent size\s+\d+/) {
printf OUTPUTFILE "%-11s %-17d \n", "extent size", " $extent_size";
} else {
($myvar = $_[0]) =~ s/"\w+"\.//g;
print OUTPUTFILE "$myvar";
}
}
sub fix_up_schema {
while () {
if (/\{ TABLE /&& ! /}\n$/) {
chop;
$savefirstline = $_;
$nextline = ;
print OUTPUTFILE2 "$savefirstline$nextline";
}else{
print OUTPUTFILE2 $_ ;
}
}
close OUTPUTFILE1;
close OUTPUTFILE2;
}