Re: Actual Used space
Mike Segel <mikey@segel.com> writes:
> You want to calculate the actual size of the table?
> Like by taking the number of rows and multiplying by the size
> of the row?
>
> That should tell you the size of the tables, but then you need to
> figure out the size of the indexes too. (Same type of calculation
> I guess.)
>
> Anyone know of an easier way? Also, do you need to run
> update statistics?
Here is a different approach, using tbcheck. May not be what you want,
though. Use at own risk.
--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--
#!/usr/local/bin/perl
eval 'exec /usr/local/bin/perl -S $0 ${1+"$@"}'
if 0;
#
# FILE: @(#)dbdf 1.5
#
# DATE: 96/09/13
#
# BY: 'yvind Gjerstad, TOLLPOST-GLOBE AS
#
#
# DO: Depending on options either prints the space occupied by
# each database (-d), the space occupied by each table (-t)
# or the number of extents for each table (-e)
require 'getopts.pl';
&Getopts("dte");
$usage=<<EOT;
Usage: $0 {-d|-t|-e}
Where
-d means sum by database,
-t means sum by table and
-e means sum by extent
EOT
($opt_d) || ($opt_t) || ($opt_e) || die "$usage\\n";
$debug = $ENV{"DEBUG"};
# finn "pagesize" i Online
open(TBSTAT,"tbstat -c |") || die "$!";
while(<TBSTAT>) {
/(^RSAM|^INFORMIX-Online) Version (\\S+)/ && ($version=$2);
/^BUFFSIZE\\s+(\\d+)/ && ($pagesize=$1);
}
close(TBSTAT);
# Version 6 and up do not have BUFFSIZE in the config file.
# Use tbstat -b. This also works in 5.X, btw.
($version=~m/7\\./) && do {
open(TBSTAT,"tbstat -b |") || die "$!";
while(<TBSTAT>) {
/(\\d+) buffer size/ && ($pagesize=$1);
}
close(TBSTAT);
};
$debug && print "version=$version\\n";
$debug && print "pagesize=$pagesize\\n";
($version=~m/^5\\./) || ($version=~m/^7\\./) ||
die "Version $version of Informix Online is not supported\\n";
open(TBCHECK,"tbcheck -pe |") || die "Can't open tbcheck: $!";
while (<TBCHECK>) {
tr/ //s;
s/^ *//;
/^$/ && next;
/^FREE / && next;
/^[a-zA-Z]/ || next;
/^(\\S+) (\\d+) (\\d+)/ && do {
$table=$1;
$start=$2;
$length=$3;
$length *= ($pagesize/1024); # tbcheck -pe reports in pages
($database=$table)=~s/:.*$//; # database name in front of ':'
$NEXTENTS{$table}++;
$TABSIZE{$table}+=$length;
$DBSIZE{$database}+=$length;
}
}
if ($opt_t) {
foreach (sort by_tabsize keys %TABSIZE) {
print $_, " ", $TABSIZE{$_}, " Kb\\n";
}
}
if ($opt_e) {
foreach (sort by_extents keys %NEXTENTS) {
print $_, " ", $NEXTENTS{$_}, " extents\\n";
}
}
if ($opt_d) {
foreach (sort by_dbsize keys %DBSIZE) {
print $_, ": ", $DBSIZE{$_}, " Kb\\n";
}
}
sub by_tabsize { $TABSIZE{$b} <=> $TABSIZE{$a} }
sub by_dbsize { $DBSIZE{$b} <=> $DBSIZE{$a} }
sub by_extents { $NEXTENTS{$b} <=> $NEXTENTS{$a} }
--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--snip--
--
'yvind Gjerstad Systems dept Tollpost-Globe AS N-6301 'ndalsnes/Norway
E-mail: ogj@it.tollpost.no Phone: +47 7122 6663 Fax: +47 7122 6694