Re: size of a database
> Hi,
> does anyone know how to check the size of a database within a dbspace ?
> I know it's an easy question, but I haven't found any solutions for this
> problem.
> If there's just one database in exactly one chunk it'll be easy to
> determine the size. But if there are more than one I don't know how to
> check it.
> I think there must be any entry in any sysmaster or sysutils table but I
> haven't found it, yet.
> I'm using IDS 7.31.UC2.
> Thanx in advance.
Here is a perl script I wrote which gives the percentage dbspace used (and
breaks out the usage by database of you supply the argument '1'). You can
modify the output to produce the actual disk usage if necessary.
SC
#!/usr/local/bin/perl
#
foreach $key (sort (keys %ENV)) {
SWITCH: {
if ($key eq "INFORMIXDIR") { $INFORMIXDIR = $ENV{$key}}
if ($key eq "INFORMIXSERVER") { $INFORMIXSERVER = $ENV{$key}}
if ($key eq "ONCONFIG") { $ONCONFIG = $ENV{$key}}
}
}
# Set the Informix environment variables.
$ENV{INFORMIXDIR}=$INFORMIXDIR;
$ENV{INFORMIXSERVER}=$INFORMIXSERVER;
$ENV{ONCONFIG}=$ONCONFIG;
$ENV{PATH}="/bin:/usr/bin:$INFORMIXDIR/bin:/tools/Perl/bin";
sub getDatabaseInfo ($) {
if ($_[0] eq "root") {
$where = "WHERE a.dbsnum = b.dbsnum AND b.dbsnum = 1";
$group = "GROUP BY 4, 2";
$order = "ORDER BY 4, 2;";
}
else {
$where = "WHERE a.dbsnum = b.dbsnum AND b.dbsnum != 1";
$group = "GROUP BY 2, 4";
$order = "ORDER BY 2, 4;";
}
open (SQL_CMD, "echo \\"
SET ISOLATION to DIRTY READ; SELECT 'SQL_TAG', a.name,
ROUND (((sum(b.chksize)) - (sum(b.nfree))) / (sum(b.chksize)) * 100,
2),
b.dbsnum
FROM sysdbspaces a, syschunks b
$where
$group
$order
\\" | dbaccess sysmaster 2>/dev/null |");
while (<SQL_CMD>) {
if (/SQL_TAG/) {
chomp;
tr/ / /s;
@toks = split / /, $_;
$total = formatBarGraph ($toks[1], $toks[2]);
print "$total\\n";
}
}
close (SQL_CMD);
} # end getDatabaseInfo
sub formatBarGraph ($$) {
# Generate the bar graph
# $1 == dbspace name (eq. appdbs1printf "%-18s %6s
# $2 == percent to be filled in (eq. 86.27)
undef ($tmp); undef ($buf); undef ($buffer);
$cnt = int ($_[1] / 2);
$cnt1 = $_[1] % 2;
if ($cnt1 != 0) {$cnt++;}
@buf = ('.', '.', '.', '.', '|', '.', '.', '.', '.', '|', '.',
'.', '.', '.', '|', '.', '.', '.', '.', '|', '.', '.', '.', '.',
'|', '.', '.', '.', '.', '|', '.', '.', '.', '.', '|', '.', '.',
'.', '.', '|', '.', '.', '.', '.', '|', '.', '.', '.', '.');
for ($a=0; $a<$cnt; $a++) {
$buf[$a] = "#";
}
$buf[4] = "|"; $buf[9] = "|"; $buf[14] = "|"; $buf[19] = "|";
$buf[24] = "|"; $buf[29] = "|"; $buf[34] = "|"; $buf[39] = "|";
$buf[44] = "|"; $buf[49] = "|";
$tmp = sprintf "%-18s %6s [", $_[0], $_[1];
for ($a=0; $a<50; $a++) {
$buffer = $buffer . $buf[$a];
}
$tmp = $tmp . $buffer;
return $tmp;
} # end formatBarGraph
##
##
# MAIN
##
##
open (SQL_CMD, "echo \\"
SET ISOLATION to DIRTY READ; SELECT 'SQL_TAG',
ROUND (((sum(b.chksize)) - (sum(b.nfree))) / (sum(b.chksize)) * 100, 2)
FROM sysdbspaces a, syschunks b
WHERE a.dbsnum = b.dbsnum
AND a.name not matches 'root*'
AND a.name not matches 'temp*'
\\" | dbaccess sysmaster 2>/dev/null |");
while (<SQL_CMD>) {
if (/SQL_TAG/) {
chomp;
tr/ / /s;
@toks = split / /, $_;
$total = formatBarGraph ($INFORMIXSERVER, $toks[1]);
}
}
close (SQL_CMD);
print "\\nServer Name % Used Graph\\n";
print
"==========================0====1====2====3====4====5====6====7====8====9====A\\n";
print "$total\\n";
print
"=============================================================================\\n\\n";
print "Database % Used Graph\\n";
print
"==========================0====1====2====3====4====5====6====7====8====9====A\\n";
getDatabaseInfo ("root");
getDatabaseInfo ("rest");
print
"=============================================================================\\n\\n";
Great script ! Copied , ran it , liked it. > Here is a perl script I wrote which gives the percentage dbspace used (and > breaks out the usage by database of you supply the argument '1'). You can > modify the output to produce the actual disk usage if necessary.
In article <89uiqn$n4o$1@news.xmission.com>,
"Stephen F. Cawley" <sfcawley@interaccess.com> writes:
>
>> Hi,
>> does anyone know how to check the size of a database within a dbspace ?
>> I know it's an easy question, but I haven't found any solutions for this
>> problem.
..
>
> Here is a perl script I wrote which gives the percentage dbspace used (and
> breaks out the usage by database of you supply the argument '1'). You can
> modify the output to produce the actual disk usage if necessary.
>
..
The perl script did not report sizes of a database. Here is my version
(SQL found in IIUG-archive as tablayout.sql from Lester B. Knutsen):
#!/usr/bin/perl -w
open SQL, 'echo "
select dbinfo( \\"DBSPACE\\" , pe_partnum ) dbspace,
dbsname,
pe_size size
from sysptnext, systabnames
where pe_partnum = partnum
order by dbspace
" | dbaccess sysmaster 2>/dev/null |' or die $!;
my %size;
while(<SQL>)
{
if ( /^(\\w+)\\s+(\\w+)\\s+(\\d+)\\s+$/ )
{
my ($dbspace, $dbname, $size) = ($1, $2, $3);
$size{"$dbname.$dbspace"} += $size;
}
}
print "dbname dbspace size\\n",
"------------------------------------------\\n";
foreach (sort keys (%size) )
{
my ($dbname, $dbspace) = split(/\\./);
my $size = $size{$_};
printf "%-15s %-15s %10d\\n", $dbname, $dbspace, $size;
}
close SQL or die $!;