Re: extent sizes
Attached is a PERL script I wrote to calculate extent sizes. Very basic I
know but I just did it to help me try to learn PERL. Hope it helps.
BTW feel free to say if its a pile of cack !
#!/usr/bin/perl -w
#############################################################
# Calculate the extent size and next size for a database table
# Input required
# number of rows -- Obtained from the dbschema
# row size -- Obtained from the dbschema ( bytes )
# index size -- Summ of all index sizes ( bytes )
# growth rate ( % ) -- Rate the table will grow in 1 year ( % )
system("clear");
# Init Vars
print "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\\n";
print " Extent Size Calculator v1.01\\n";
print "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\\n";
print "Page = 2048K\\n";
print "Assumes 2 extents allocated per year.\\n";
print "MIN EXTENT = 8 MIN NEXT SIZE = 16\\n";
$tab_size = 0;
$n_rows = 0;
$r_size = 0;
$i_size = 0;
$g_rate = 0;
$e_size = 0;
$n_size = 0;
# Get the input values
( $n_rows, $r_size, $i_size, $g_rate ) = get_in_vars();
( $tab_size ) = ( $r_size + $i_size ) * $n_rows;
print "\\n---------------------------------\\n";
print "Table size (bytes) : $tab_size\\n";
# Now Work Out 1st Extent Size
( $e_size ) = calc_extent( $tab_size, 8 );
print "EXTENT SIZE is : $e_size\\n";
# Work out Next extent size assuming 2 extents per year
( $tab_size ) = ( $tab_size * ( $g_rate / 100 ) ) /2;
( $n_size ) = calc_extent( $tab_size,16 );
print "NEXT SIZE is : $n_size\\n\\n";
###########################################################
sub get_in_vars{
$g_rate = 0;
print "\\n Please enter the number of rows : ";
$n_rows = <STDIN>;
print "\\n Please enter the row size ( bytes ) : ";
$r_size = <STDIN>;
print "\\n Please enter the index size ( bytes ) : ";
$i_size = <STDIN>;
print "\\n Please enter the yearly growth rate (%): ";
$g_rate = <STDIN>;
return $n_rows, $r_size, $i_size, $g_rate;
}
###########################################################
sub calc_extent{
my ($tab_size,$min_ext) = @_;
# SCO Page Size is 2048 bytes, extent must be divisable by that
$rem = $tab_size % 2048;
# Round up to the nearest page
if ( $rem > 0 ){
$tab_size = $tab_size + ( 2048 - $rem );
}
$e_size = $tab_size / 1024;
# Got to have a minimum, no point in a 0 extent
if ( $e_size < $min_ext ){
$e_size = $min_ext;
}
return $e_size;
}
No your script is not cack, it is actually quite nice. However, there's
always a 'however' isn't there), as a generic script, Robert, you need to
make just a couple of adjustments:
o If you do not get the index size from dbschema/myschema you have to add 4
to the keysize of EACH index for the rowid and multiply the total by 1.5
for index overhead. (Dbschema and myschema do this for you.)
o Adjust the calculation of $tabsize by adding 4 to rowsize to allow for the
per row overhead on each page of the slot table entry.
o You use "% 2048" to calculate the number of pages which does not allow for
the page header and trailer. This should be (PAGESIZE - 24) where
PAGESIZE is either 2048 or 4096 depending on the system page size (AIX &
WinNT are 4K pagesize releases most others are 2K).
Do this and submit it to the IIUG Software Repository (email it to
software@iiug.org) for all to see. Then you too can plug your creation
constantly ;-) like other humble folk.
Art S. Kagel
Robert Taylor wrote:
>
> Attached is a PERL script I wrote to calculate extent sizes. Very basic I
> know but I just did it to help me try to learn PERL. Hope it helps.
>
> BTW feel free to say if its a pile of cack !
>
> #!/usr/bin/perl -w
>
> #############################################################
> # Calculate the extent size and next size for a database table
> # Input required
> # number of rows -- Obtained from the dbschema
> # row size -- Obtained from the dbschema ( bytes )
> # index size -- Summ of all index sizes ( bytes )
> # growth rate ( % ) -- Rate the table will grow in 1 year ( % )
>
> system("clear");
> # Init Vars
> print "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\\n";
> print " Extent Size Calculator v1.01\\n";
> print "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-\\n";
> print "Page = 2048K\\n";
> print "Assumes 2 extents allocated per year.\\n";
> print "MIN EXTENT = 8 MIN NEXT SIZE = 16\\n";
> $tab_size = 0;
> $n_rows = 0;
> $r_size = 0;
> $i_size = 0;
> $g_rate = 0;
> $e_size = 0;
> $n_size = 0;
>
> # Get the input values
> ( $n_rows, $r_size, $i_size, $g_rate ) = get_in_vars();
>
> ( $tab_size ) = ( $r_size + $i_size ) * $n_rows;
> print "\\n---------------------------------\\n";
> print "Table size (bytes) : $tab_size\\n";
>
> # Now Work Out 1st Extent Size
> ( $e_size ) = calc_extent( $tab_size, 8 );
> print "EXTENT SIZE is : $e_size\\n";
>
> # Work out Next extent size assuming 2 extents per year
> ( $tab_size ) = ( $tab_size * ( $g_rate / 100 ) ) /2;
> ( $n_size ) = calc_extent( $tab_size,16 );
> print "NEXT SIZE is : $n_size\\n\\n";
>
> ###########################################################
> sub get_in_vars{
>
> $g_rate = 0;
>
> print "\\n Please enter the number of rows : ";
> $n_rows = <STDIN>;
> print "\\n Please enter the row size ( bytes ) : ";
> $r_size = <STDIN>;
> print "\\n Please enter the index size ( bytes ) : ";
> $i_size = <STDIN>;
> print "\\n Please enter the yearly growth rate (%): ";
> $g_rate = <STDIN>;
>
> return $n_rows, $r_size, $i_size, $g_rate;
> }
> ###########################################################
> sub calc_extent{
> my ($tab_size,$min_ext) = @_;
>
> # SCO Page Size is 2048 bytes, extent must be divisable by that
> $rem = $tab_size % 2048;
>
> # Round up to the nearest page
> if ( $rem > 0 ){
> $tab_size = $tab_size + ( 2048 - $rem );
> }
> $e_size = $tab_size / 1024;
>
> # Got to have a minimum, no point in a 0 extent
> if ( $e_size < $min_ext ){
> $e_size = $min_ext;
> }
> return $e_size;
> }