#!/usr/bin/perl -w
#
# $Id: check-space.pl,v 1.1 2001/12/13 01:05:26 mpeppler Exp $
#
# List the spaceusage of a database, and the space usage of each
# user table in the DB.
use strict;
use DBI;
use Getopt::Long;
my %args;
GetOptions(\%args, '-U=s', '-P=s', '-S=s', '-D=s');
my $dbh = DBI->connect("dbi:Sybase:server=$args{S};database=$args{D}", $args{U}, $args{P});
$dbh->{syb_do_proc_status} = 1;
my $dbinfo;
# First check space in the DB:
my $sth = $dbh->prepare("sp_spaceused");
$sth->execute;
do {
    while(my $d = $sth->fetch) {
        if($d->[0] =~ /$args{D}/) {
            $d->[1] =~ s/[^\d.]//g;
            $dbinfo->{size} = $d->[1];
        } else {
            foreach (@$d) {
                s/\D//g;
            }
            $dbinfo->{reserved} = $d->[0] / 1024;
            $dbinfo->{data} = $d->[1] / 1024;
            $dbinfo->{index} = $d->[2] / 1024;
        }
#       print "@$d\n";
    }
} while($sth->{syb_more_results});
# Get the actual device usage from sp_helpdb to get the free log space
$sth = $dbh->prepare("sp_helpdb $args{D}");
$sth->execute;
do {
    while(my $d = $sth->fetch) {
        #print "@$d\n";
        if($d->[2] && $d->[2] =~ /log only/) {
            $d->[1] =~ s/[^\d\.]//g;
            $dbinfo->{log} += $d->[1];
        }
        if($d->[0] =~ /log only .* (\d+)/) {
            $dbinfo->{logfree} = $1 / 1024;
        }
    }
} while($sth->{syb_more_results});
$dbinfo->{size} -= $dbinfo->{log};
#if(($dbinfo->{reserved} / $dbinfo->{size}) > 0.75) {
#    warn "WARNING: smlive free space is below 25%\n";
#}
my $freepct = ($dbinfo->{size} - $dbinfo->{reserved}) / $dbinfo->{size};
print "$args{S}/$args{D} spaceusage report\n\n";
printf "Database size: %10.2f MB\n", $dbinfo->{size};
printf "Log size:      %10.2f MB\n", $dbinfo->{log};
printf "Free Log:      %10.2f MB\n", $dbinfo->{logfree}; 
printf "Reserved:      %10.2f MB\n", $dbinfo->{reserved};
printf "Data:          %10.2f MB\n", $dbinfo->{data};
printf "Indexes:       %10.2f MB\n", $dbinfo->{index};
printf "Free space:    %10.2f %%\n", $freepct * 100;
if($freepct < .25) {
    printf "**WARNING**: Free space is below 25%% (%.2f%%)\n\n", $freepct * 100;
}
print "\nTable information (in MB):\n\n";
printf "%15s %15s %10s %10s %10s\n\n", "Table", "Rows", "Reserved", "Data", "Indexes";
my @tables = getTables($dbh);
foreach (@tables) {
    my $sth = $dbh->prepare("sp_spaceused $_");
    $sth->execute;
    do {
        while(my $d = $sth->fetch) {
            foreach (@$d) {
                s/KB//;
                s/\s//g;
            }
            printf("%15.15s %15d %10.2f %10.2f %10.2f\n",
                   $d->[0], $d->[1], $d->[2] / 1024, $d->[3] / 1024,
                   $d->[4] / 1024);
#           print "@$d\n";
        }
    } while($sth->{syb_more_results});
}
sub getTables {
    my $dbh = shift;
    my $sth = $dbh->table_info;
    my @tables;
    do {
        while(my $d = $sth->fetch) {
            push(@tables, $d->[2]) unless $d->[3] =~ /SYSTEM|VIEW/;
        }
    } while($sth->{syb_more_results});
    @tables;
}