#!/usr/local/bin/perl 
## Script: extent-report.pl
## Author: Matthew Devlin mdevlin@reserveamerica.com
## Date:   12/17/1999
## Notes:  The purpose of this script is to query the sysmaster database
##         on an informix instance and return extent size and usage info
##         based on the criteria entered at the command line. Output 
##         is directed to <STDOUT> and could be sent to other utilities
##         if desired.
##         
## Installation: You need to verify that perl is in the location specified
##               at the top of the script, and make sure the page size
##               is set correctly.
##   
## Credits:  The query used was written by Carlton Doe
##           I took it from a script he has written named
##           check_table_size.
##
## Please feel free to use, distribute(freely), modify this script
## All I ask is that you send me any improvements or sugesstions
## So that I can benefit as well.  I hope this is of use to someone. 
##
## ***************** USE AT YOUR OWN RISK! *************************
## *** The author or his company will not be held responsible for **
## ******** any problems caused by the use of this script. *********
##

if ($#ARGV < 2) {
 print "\nUSAGE: extent-report.pl <dbserver> <database> <min. num. extents>\n";
 print "         The target number should be a whole number, representing,\n";
 print "         the starting criteria for tables you want returned.\n";
 print "\nFOR EXAMPLE: extent-report.pl nrrslive2 crrd 7\n";
 print "   This will return inormation on all the tables in the crrd\n";
 print "         Database that have 7 or more extents.\n\n";
exit;
} # End if

$date = `date`;

     $ENV{'INFORMIXSERVER'} = $ARGV[0]; 
     
# Is this your page size? if not change it
$pagesize = 2;

## Query written by Carton Doe
$sql_data = "unload to extents.tmp ";
$sql_data .= "select systabnames.tabname tab_name, ";
$sql_data .= "sum(fextsiz) * $pagesize first_ext_kb, ";
$sql_data .= "sum(nextsiz) * $pagesize next_ext_kb, ";
$sql_data .= "sum(nptotal) * $pagesize totsize_kb, ";
$sql_data .= "sum(sysptnhdr.npused) * $pagesize used_kb, ";
$sql_data .= "sum(nextns) num_extents, ";
$sql_data .= "sum(sysptnhdr.nrows) num_rows, ";
$sql_data .= "sum(unused1) percent_free ";
$sql_data .= "from sysptnhdr, systabnames, $ARGV[1]:systables ";
$sql_data .= "where dbsname = \"$ARGV[1]\" and ";
$sql_data .= "systabnames.partnum = sysptnhdr.partnum and ";
$sql_data .= "$ARGV[1]:systables.tabname = systabnames.tabname and ";
$sql_data .= "$ARGV[1]:systables.tabid >= 100 ";
$sql_data .= "group by 1 ";
$sql_data .= "order by 6desc; ";

## The handling of the sql and data is a bit sloppy but it works!
open (SQL, ">sql.tmp") or die "could not open sql";
print SQL $sql_data;
close SQL;
system "cat sql.tmp | /usr/informix/bin/dbaccess sysmaster 2>/dev/null";

$lne = "-" x 77;
open (DATA, "<extents.tmp") or die "couldnt open report";
open (REPORT, ">myreport.tmp") or die "couldnt open myreport";

print REPORT "$lne\n";
print REPORT "$date\n";
print REPORT "Extent Report for Server: $ARGV[0]\n"; 
print REPORT "                Database: $ARGV[1]\n";
print REPORT "$lne\n";

format REPORT_TOP =
                     First   Next   Total     Used         Num      Num 
Table                Extent  Extent Size      Kb           Extents  Rows
-----------------------------------------------------------------------------
.
format REPORT =                      
@<<<<<<<<<<<<<<<<<<<<@<<<<<<<@<<<<<<@<<<<<<<<<@<<<<<<<<<<<<@<<<<<<<<@<<<<<<<<<<<
$tab,                $first, $next, $tot,     $used,       $num,    $rows 
.


## Trims of the .0's and splits the data up for formatting
while (<DATA>) {
(s/\.0//g);
($tab,$first,$next,$tot,$used,$num,$rows) = split(/\|/,$_);
 
     if ($ARGV[2] <= $num) {
                     write REPORT;
                  } else { 
                     next; 
     } # End if
} # End while

close REPORT;
open (REPORT, "<myreport.tmp");
   while (<REPORT>) {
         print STDOUT;
   } # End while

## Cleanup
unlink "extents.tmp";
unlink "sql.tmp";
unlink "myreport.tmp";