RE: Informix ReOrg Scripts/Programs
Eric
Heres the script that does reorgs of a database (one table or all):
------------------------------ cut here ---------------------------------------
#!/usr/local/bin/perl
#
# reorg
#
# Allows reorg for a database or database table. Allows the user to unload
# a table, drop it and recreate it with new parameters for dbspace, initial
# and next extent size, then reloads the data. Can be used iteratively against
# all tables in the given database. System prompts for new values for dbspace
# and the extent sizes, and also suggests values based on current data. User
# can choose to simply drop and create a table to eliminate fragmentation.
#
# Command format: reorg database_name[:table_name] \\
# [-d dbspace] [-e first_extent] [-n next_extent] \\
# [-i indexspace]
#
# Author: Sujit Pal
# Dated : 11/19/96
#
#
# Read the command line arguments and print the usage if command is
# incorrect
#
if ((($#ARGV == 0) && ($ARGV[0] eq "--")) || ($#ARGV > 8) || ($#ARGV < 0))
{
die "Usage: reorg database_name[:table_name] \\\\\\n" .
" [-d dbspace] [-e initial_extent] [-n next_extent] \\\\\\n" .
" [-i indexspace] \\n";
}
$dbtabname = $ARGV[0];
($dbname, $tabname) = split(":", $dbtabname);
for ($i=1; $i<=7; $i+=2)
{
if ($ARGV[$i] eq "-d")
{
$dbspace = $ARGV[$i+1];
}
elsif ($ARGV[$i] eq "-e")
{
$extsize = $ARGV[$i+1];
}
elsif ($ARGV[$i] eq "-n")
{
$nextsize = $ARGV[$i+1];
}
elsif ($ARGV[$i] eq "-i")
{
$idxspace = $ARGV[$i+1];
}
}
#
# Check if any other users already logged in. If yes, die
#
chop(@onstat_u = &Runinfxcmd("onstat -u"));
$data_started = 0;
foreach (@onstat_u)
{
if ($data_started == 0)
{
if (index($_, "address") < 0)
{
next;
}
$data_started = 1;
next;
}
else
{
if (index($_, "active") < 0)
{
if (index($_, "informix") < 0)
{
$users_logged++;
next;
}
else
{
next;
}
}
last;
}
}
if ($users_logged > 0)
{
die "There are " . $users_logged . " non-informix users in system. " .
"Log them off before doing a reorg\\n";
}
#
# If table name is specified, process only for that table, otherwise generate
# a list of tables from systables.
#
if ($tabname eq '')
{
if ($ver < 6)
{
chop(@tablist = &Runsql("SELECT tabname, hex(partnum) FROM systables
WHERE tabid > 99 ORDER BY tabname"));
}
else
{
chop(@tablist = &Runsql("SELECT tabname, dbinfo('DBSPACE', partnum)
FROM systables WHERE tabid > 99 ORDER BY tabname"));
}
}
else
{
if ($ver < 6)
{
chop(@tablist = &Runsql("SELECT tabname, hex(partnum) FROM systables
WHERE tabname = \\'$tabname\\'"));
}
else
{
chop(@tablist = &Runsql("SELECT tabname, dbinfo('DBSPACE', partnum)
FROM systables WHERE tabname = \\'$tabname\\'"));
}
}
#
# Process for each table in the array @tablist
#
foreach (@tablist)
{
if ((index($_, "tabname") > -1) || ($_ eq ''))
{
next;
}
($tabname, $hex_partnum) = split(' ', $_);
print "\\nReorganizing Table: ", $dbname, ":", $tabname, "...\\n";
#
# Get the arguments interactively if not supplied
#
if ($dbspace eq '') # Not supplied on command
{ # line, hence interactive
if ($ver < 6)
{
$old_dbspace = &Find_db($hex_partnum);
}
else
{
$old_dbspace = $hex_partnum;
}
print "DBSPACE before reorg: " . $old_dbspace . "\\n";
print "Choose a DBSPACE from the list below:\\n";
&Disp_dbspaces;
print "DBSPACE after reorg? ";
chop($dbspace = <STDIN>);
}
$dbtabname = $dbname . ":" . $tabname;
($old_extsize, $old_nextsize, $old_num_exts) = &Find_extents($dbtabname);
if ($extsize eq '') # Not supplied on command
{ # line, hence interactive
print "Initial extent size before reorg: " . $old_extsize . "\\n";
if ($old_num_exts > 1)
{
$extsize = int($old_extsize * $old_num_exts * 1.2);
}
else
{
$extsize = $old_extsize;
}
print "Initial extent size after reorg (suggested: " . $extsize . "): ";
chop($new_extsize = <STDIN>);
if ($new_extsize ne '')
{
$extsize = $new_extsize;
}
}
if ($nextsize eq '') # Not supplied on command
{ # line, hence interactive
print "Next extent size before reorg: " . $old_nextsize . "\\n";
if ($extsize <= 8)
{
$nextsize = $extsize;
}
else
{
$nextsize = int($extsize / 2);
}
print "Next extent size after reorg (suggested: " . $nextsize . "): ";
chop($new_nextsize = <STDIN>);
if ($new_nextsize ne '')
{
$nextsize = $new_nextsize;
}
}
if ($idxspace eq '') # Not supplied on command
{ # line, hence interactive
print "Choose an IndexSpace from the list below:\\n";
&Disp_dbspaces;
print "IndexSpace after reorg? ";
chop($idxspace = <STDIN>);
}
#
# Now unload the table
#
$unl_fname = $tabname . "\\.dat";
@dummy = &Runsql("UNLOAD TO $unl_fname SELECT * FROM $tabname");
chop($rows_unl = `cat $unl_fname | wc -l`);
print $rows_unl . " rows unloaded\\n";
#
# Now unload the structure and modify it with the new parameters
#
$dbf_fname = $tabname . "\\.sql";
$dbf_temp = $tabname . "\\.tmp";
system("dbschema -d $dbname -t $tabname -ss $dbf_fname 1>/dev/null");
$command = "sed -e \\"/{[A-Za-z0-9 ]/d\\"" .
" -e \\"/[A-Za-z0-9 ]}/d\\"" .
" -e \\"s/) extent size/) in $dbspace extent size/\\"" .
" -e \\"s/) in db[1-9]* /) in $dbspace /\\"" .
" -e \\"s/extent size [1-9]* /extent size $extsize /\\"" .
" -e \\"s/next size [1-9]* /next size $nextsize /\\"" .
" -e \\"s/);/) in $idxspace;/\\"" . " " .
$dbf_fname . ">" . $dbf_temp;
system($command);
system("mv $dbf_te