#!/bin/sh
#
# This is a shell archive.  To extract its contents,
# execute this file with /bin/sh to create the file(s):
#
# README		    reorg
#
# This shell archive created: Tue Feb 25 11:10:43 EST 1997
#
echo "Extracting file README"
sed -e 's/^X//' <<\SHAR_EOF > README
XREADME
X
XAllows a DBA to change the dbspace, the initial and next extent sizes
Xfor a table. What it does is unload the data, dump the schema into a
Xtemporary file, edit it using the user supplied parameters for table
Xdbspace, initial and next extent sizes and the index dbspace, drop the
Xtable and recreate it using the new (edited) schema and the data. It
Xalso allows the DBA to put the indexes into a separate dbspace for
Xperformance reasons. Can be run on a single table or on all tables in
Xthe database. Can also be run with the existing (default) parameters to
Xeliminate fragmentation in tables with high i/o.
SHAR_EOF
if [ `wc -c < README` -ne      614 ]
then
    echo "Lengths do not match -- Bad Copy of README"
fi
echo "Extracting file reorg"
sed -e 's/^X//' <<\SHAR_EOF > reorg
X#!/usr/local/bin/perl
X#
X# reorg
X#
X# Allows reorg for a database or database table. Allows the user to unload
X# a table, drop it and recreate it with new parameters for dbspace, initial
X# and next extent size, then reloads the data. Can be used iteratively against
X# all tables in the given database. System prompts for new values for dbspace
X# and the extent sizes, and also suggests values based on current data. User
X# can choose to simply drop and create a table to eliminate fragmentation.
X#
X# Command format: reorg database_name[:table_name] \
X#                       [-d dbspace] [-e first_extent] [-n next_extent] \
X#                       [-i indexspace]
X#
X# Author: Sujit Pal
X# Dated : 11/19/96
X#
X
X#
X# Read the command line arguments and print the usage if command is
X# incorrect
X#
Xif ((($#ARGV == 0) && ($ARGV[0] eq "--")) || ($#ARGV > 8) || ($#ARGV < 0))
X{
X   die "Usage: reorg database_name[:table_name] \\\n" .
X       "             [-d dbspace] [-e initial_extent] [-n next_extent] \\\n" .
X       "             [-i indexspace] \n";
X}
X$dbtabname = $ARGV[0];
X($dbname, $tabname) = split(":", $dbtabname);
Xfor ($i=1; $i<=7; $i+=2)
X{
X   if ($ARGV[$i] eq "-d")
X   {
X      $dbspace = $ARGV[$i+1];
X   }
X   elsif ($ARGV[$i] eq "-e")
X   {
X      $extsize = $ARGV[$i+1];
X   }
X   elsif ($ARGV[$i] eq "-n")
X   {
X      $nextsize = $ARGV[$i+1];
X   }
X   elsif ($ARGV[$i] eq "-i")
X   {
X      $idxspace = $ARGV[$i+1];
X   }
X}
X#
X# Check if any other users already logged in. If yes, die
X#
Xchop(@onstat_u = &Runinfxcmd("onstat -u"));
X$data_started = 0;
Xforeach (@onstat_u)
X{
X   if ($data_started == 0)
X   {
X      if (index($_, "address") < 0)
X      {
X	 next;
X      }
X      $data_started = 1;
X      next;
X   }
X   else
X   {
X      if (index($_, "active") < 0)
X      {
X	 if (index($_, "informix") < 0)
X	 {
X	    $users_logged++;
X	    next;
X	 }
X	 else
X	 {
X	    next;
X	 }
X      }
X      last;
X   }
X}
Xif ($users_logged > 0)
X{
X   die "There are " . $users_logged . " non-informix users in system. " .
X      "Log them off before doing a reorg\n";
X}
X#
X# If table name is specified, process only for that table, otherwise generate
X# a list of tables from systables.
X#
Xif ($tabname eq '')
X{
X   if ($ver < 6)
X   {
X      chop(@tablist = &Runsql("SELECT tabname, hex(partnum) FROM systables
X         WHERE tabid > 99 ORDER BY tabname"));
X   }
X   else
X   {
X      chop(@tablist = &Runsql("SELECT tabname, dbinfo('DBSPACE', partnum)
X	 FROM systables WHERE tabid > 99 ORDER BY tabname"));
X   }
X}
Xelse
X{
X   if ($ver < 6)
X   {
X      chop(@tablist = &Runsql("SELECT tabname, hex(partnum) FROM systables
X         WHERE tabname = \'$tabname\'"));
X   }
X   else
X   {
X      chop(@tablist = &Runsql("SELECT tabname, dbinfo('DBSPACE', partnum)
X	 FROM systables WHERE tabname = \'$tabname\'"));
X   }
X}
X#
X# Process for each table in the array @tablist
X#
Xforeach (@tablist)
X{
X   if ((index($_, "tabname") > -1) || ($_ eq ''))
X   {
X      next;
X   }
X   ($tabname, $hex_partnum) = split(' ', $_);
X   print "\nReorganizing Table: ", $dbname, ":", $tabname, "...\n";
X   #
X   # Get the arguments interactively if not supplied
X   #
X   if ($dbspace eq '')                             # Not supplied on command
X   {                                               # line, hence interactive
X      if ($ver < 6)
X      {
X         $old_dbspace = &Find_db($hex_partnum);
X      }
X      else
X      {
X	 $old_dbspace = $hex_partnum;
X      }
X      print "DBSPACE before reorg: " . $old_dbspace . "\n";
X      print "Choose a DBSPACE from the list below:\n";
X      &Disp_dbspaces;
X      print "DBSPACE after reorg? ";
X      chop($dbspace = <STDIN>);
X   }
X   $dbtabname = $dbname . ":" . $tabname;
X   ($old_extsize, $old_nextsize, $old_num_exts) = &Find_extents($dbtabname);
X   if ($extsize eq '')                            # Not supplied on command
X   {                                              # line, hence interactive
X      print "Initial extent size before reorg: " . $old_extsize . "\n";
X      if ($old_num_exts > 1)
X      {
X	 $extsize = int($old_extsize * $old_num_exts * 1.2);
X      }
X      else
X      {
X	 $extsize = $old_extsize;
X      }
X      print "Initial extent size after reorg (suggested: " . $extsize . "): ";
X      chop($new_extsize = <STDIN>);
X      if ($new_extsize ne '')
X      {
X	 $extsize = $new_extsize;
X      }
X   }
X   if ($nextsize eq '')                          # Not supplied on command
X   {                                             # line, hence interactive
X      print "Next extent size before reorg: " . $old_nextsize . "\n";
X      if ($extsize <= 8)
X      {
X	 $nextsize = $extsize;
X      }
X      else
X      {
X         $nextsize = int($extsize / 2);
X      }
X      print "Next extent size after reorg (suggested: " . $nextsize . "): ";
X      chop($new_nextsize = <STDIN>);
X      if ($new_nextsize ne '')
X      {
X	 $nextsize = $new_nextsize;
X      }
X   }
X   if ($idxspace eq '')                            # Not supplied on command
X   {                                               # line, hence interactive
X      print "Choose an IndexSpace from the list below:\n";
X      &Disp_dbspaces;
X      print "IndexSpace after reorg? ";
X      chop($idxspace = <STDIN>);
X   }
X   #
X   # Now unload the table
X   #
X   $unl_fname = $tabname . "\.dat";
X   @dummy = &Runsql("UNLOAD TO $unl_fname SELECT * FROM $tabname");
X   chop($rows_unl = `cat $unl_fname | wc -l`);
X   print $rows_unl . " rows unloaded\n";
X   #
X   # Now unload the structure and modify it with the new parameters
X   #
X   $dbf_fname = $tabname . "\.sql";
X   $dbf_temp = $tabname . "\.tmp";
X   system("dbschema -d $dbname -t $tabname -ss $dbf_fname 1>/dev/null");
X   $command = "sed -e \"/{[A-Za-z0-9 ]/d\"" .
X      " -e \"/[A-Za-z0-9 ]}/d\"" .
X      " -e \"s/) extent size/) in $dbspace extent size/\"" .
X      " -e \"s/) in db[1-9]* /) in $dbspace /\"" .
X      " -e \"s/extent size [1-9]* /extent size $extsize /\"" .
X      " -e \"s/next size [1-9]* /next size $nextsize /\"" .
X      " -e \"s/);/) in $idxspace;/\"" .  " " .
X      $dbf_fname . ">" . $dbf_temp;
X   system($command);
X   system("mv $dbf_temp $dbf_fname");
X   #
X   # Now drop the table and recreate it
X   #
X   @dummy = &Runsql("DROP TABLE $tabname");
X   if ($ver < 6)
X   {
X      system("isql $dbname < $dbf_fname 2>/dev/null");
X   }
X   else
X   {
X      system("dbaccess $dbname < $dbf_fname 2>/dev/null");
X   }
X   #
X   # Now load the data back
X   #
X   @dummy = &Runsql("LOAD FROM $unl_fname INSERT INTO $tabname");
X   chop(@dummy = &Runsql("SELECT COUNT(*) FROM $tabname"));
X   foreach (@dummy)
X   {
X      if ((index($_, "count") > -1) || ($_ eq ''))
X      {
X         next;
X      }
X      ($rows_unl) = split(' ', $_);
X      last;
X   }
X   print $rows_unl . " rows loaded\n";
X   #
X   # Remove temporary files and Update statistics for the table
X   #
X   unlink($unl_fname);
X   unlink($dbf_fname);
X   $updstlvl = "";
X   if ($ver >= 6)
X   {
X      $updstlvl = "MEDIUM";
X   }
X   @dummy = &Runsql("UPDATE STATISTICS $updstlvl FOR TABLE $tabname");
X   print "\n...Reorg complete\n";
X}
X
X
X#
X# Runinfxcmd
X#
X# Runs an Informix command with the appropriate environment variables
X# set up. Converts the command for versions less that 7.x
X#
Xsub Runinfxcmd
X{
X   local ($command);
X   $command = $_[0];
X   $ver = `tbmonitor -V` || `onmonitor -V`;
X   @ver = split(/ /, $ver);
X   $ver = @ver[2];
X   $ver = substr($ver, 0, 1);
X   if ($ver < 6)
X   {
X      $tbconfig = $ENV{'TBCONFIG'} ||
X	 die "Environment variable TBCONFIG not set\n";
X      if (substr($command,0,2) eq "on")
X      {
X	 $command = "tb" . substr($command, 2, 9999);
X      }
X      @retvals = `TBCONFIG=$onconfig; export TBCONFIG; $command`;
X   }
X   else
X   {
X      $onconfig = $ENV{'ONCONFIG'} ||
X	 die "Environment variable ONCONFIG not set\n";
X      $informixserver = $ENV{'INFORMIXSERVER'} ||
X	 die "Environment variable INFORMIXSERVER not set\n";
X      @retvals = `export INFORMIXSERVER=$informixserver;
X	 export ONCONFIG=$onconfig; $command`;
X   }
X   @retvals;
X}
X
X#
X# Runsql
X#
X# Runs an SQL command and returns the results in a designated array
X#
Xsub Runsql
X{
X   local ($command);
X   $command = $_[0];
X   $command = "\"" . $command . "\"";
X   if ($ver < 6)                         # use isql for these
X   {
X      @retvals = `echo $command | isql $dbname 2>/dev/null`;
X   }
X   else                                  # use dbaccess for these
X   {
X      @retvals = `echo $command | dbaccess $dbname 2>/dev/null`;
X   }
X   @retvals;
X}
X
X#
X# Find_db
X#
X# Function to perform similar function to dbinfo('DBSPACE',hex(partnum)).
X# The wheel was reinvented so as to accomodate 5.x databases which dont have
X# the dbinfo function. Surprisingly the algorithm does not hold true for
X# version 7.x databases.
X#
Xsub Find_db
X{
X   local($hex_partnum);
X   local($data_started);
X   $hex_partnum = $_[0];
X   if ($#dbinfo > 0)
X   {
X      $fdbspace = $dbinfo{$hex_partnum};
X   }
X   else
X   {
X      $hex_partnum = substr($hex_partnum, 2, 2);
X      for ($i=0; $i<2; $i++)
X      {
X         $byteval = substr($hex_partnum, $i, 1);
X         if (index("ABCDEF", $byteval) >= 0)
X         {
X            $byteval = 10 + index("ABCDEF", $byteval);
X         }
X         $dbspacenum += $byteval * (16 ** (1 - $i));
X      }
X      chop(@onstat_d = &Runinfxcmd("onstat -d"));
X      $data_started = 0;
X      foreach (@onstat_d)
X      {
X         if ($data_started == 0)
X         {
X            if (index($_, "address") < 0)
X            {
X	       next;
X            }
X            $data_started = 1;
X            next;
X         }
X         else
X         {
X            if (index($_, "active") < 0)
X            {
X	       $_ =~ s/N T/NT/;
X	       ($junk1, $number, $junk2, $junk3, $junk4, $junk5, $junk6, $name)
X		  = split(' ', $_);
X	       $dbinfo{$number} = $name;
X            }
X	    else
X	    {
X               last;
X	    }
X         }
X      }
X      $fdbspace = $dbinfo{$dbspacenum};
X   }
X   $fdbspace;
X}
X
X#
X# Runinfxcmd
X#
X# Runs an Informix command with the appropriate environment variables
X# set up. Converts the command for versions less that 7.x
X#
Xsub Runinfxcmd
X{
X   local ($command);
X   $command = $_[0];
X   $ver = `tbmonitor -V` || `onmonitor -V`;
X   @ver = split(/ /, $ver);
X   $ver = @ver[2];
X   $ver = substr($ver, 0, 1);
X   if ($ver < 6)
X   {
X      $tbconfig = $ENV{'TBCONFIG'} ||
X	 die "Environment variable TBCONFIG not set\n";
X      if (substr($command,0,2) eq "on")
X      {
X	 $command = "tb" . substr($command, 2, 9999);
X      }
X      @retvals = `TBCONFIG=$onconfig; export TBCONFIG; $command`;
X   }
X   else
X   {
X      $onconfig = $ENV{'ONCONFIG'} ||
X	 die "Environment variable ONCONFIG not set\n";
X      $informixserver = $ENV{'INFORMIXSERVER'} ||
X	 die "Environment variable INFORMIXSERVER not set\n";
X      @retvals = `export INFORMIXSERVER=$informixserver;
X	 export ONCONFIG=$onconfig; $command`;
X   }
X   @retvals;
X}
X
X#
X# Find_extents
X#
X# Function to find the first extent size, the next extent size and the number
X# of extents.
X#
Xsub Find_extents
X{
X   local($dbtab);
X   $dbtab = $_[0];
X   chop(@oncheck_pt = &Runinfxcmd("oncheck -pt $dbtab"));
X   foreach (@oncheck_pt)
X   {
X      if (index($_, "Number of extents") != -1)
X      {
X         ($junk1, $junk2, $junk3, $no_of_ext) = split(' ', $_);
X      }
X      elsif (index($_, "Next extent size") != -1)
X      {
X         ($junk1, $junk2, $junk3, $next_ext) = split(' ', $_);
X      }
X      elsif (index($_, "First extent size") != -1)
X      {
X         ($junk1, $junk2, $junk3, $first_ext) = split(' ', $_);
X      }
X      else
X      {
X	 next;
X      }
X   }
X   $fextents[0] = $first_ext;
X   $fextents[1] = $next_ext;
X   $fextents[2] = $no_of_ext;
X   @fextents;
X}
X
X#
X# Disp_dbspaces
X#
X# Function to display the dbspaces for the menu
X#
Xsub Disp_dbspaces
X{
X   if ($#dbinfo <= 0)
X   {
X      $dbs = &Find_db("0x01000000");      # Dummy call to populate @dbinfo
X   }
X   foreach (keys(%dbinfo))
X   {
X      print "\t", $dbinfo{$_}, "\n";
X   }
X}
SHAR_EOF
if [ `wc -c < reorg` -ne    11790 ]
then
    echo "Lengths do not match -- Bad Copy of reorg"
fi
echo "Done."
exit 0