Subject: Help: Reducing Extents
From: John Clutterbuck <John.Clutterbuck@sbs.siemens.co.uk>
Newsgroups: comp.databases.informix
Date: Mon, 23 Jun 1997 17:45:37 +0100

> I am running a electronic document management system (EDMS) that uses
> Informix 7.22 as a back end to manage the documents.  The EDMS system
> warns me when there are database problems that slow performance.  In
> this case it warned me that there were too many extends for the tables
> listed below:
> 
> DBMS Tables With More Than 5 Extents
>  # of Extents          Type          Name
>            36         Table arrowdev:dm_sysobject_s
>            22         Table arrowdev:syscolumns
>            21         Table arrowdev:dmr_content_s
>            19         Table arrowdev:sysviews
>            17         Table arrowdev:dm_sysobject_r
>            15         Table arrowdev:dmr_content_r
>            14         Table arrowdev:dmi_object
>            12         Table arrowdev:dm_type_r
>             8         Table arrowdev:sysobjstate
>             7         Table arrowdev:dmi_queue_item_s
>             7         Table arrowdev:sysconstraints
>             6         Table sysmaster:syscolumns
> 
> How do I reduce the number of extents to improve performance??


Here is a perl script I use to defrag tables and the script it uses to
run the SQL under a transaction and only commit if no errors.

These are supplied without warrenty of any kind. If they are useful I am
glad. If they trash you're database, I or my company are not
responsible.

But please let me know of any bugs or improvements.



#!/usr/local/bin/perl5
#defrag_table.pl - de-fragment an informix table

#This script is supplied without warrenty of any kind. If it is useful I
am glad.
#If it trashes your database, I or my company are not responsible.

$usage="Usage: defrag_table.pl [-u|-d] [-s] [-n numrecs] database
tablename tempdir\n\t-u = do not unload the data (assumes
tempdir/tablename.unl exists)\n\t-d = delete the unload file at
end\n\t-s = produce script file:tempdir/tablename.sql - do NOT run
it\n\t-n x = use x as the number of records\n";

# runs the SQL file in a transaction and only commits of no errors

#-------------------------------------------------------------------------------
# check args
#-------------------------------------------------------------------------------

require ('getopts.pl');

&Getopts('udsn:');

if ( $#ARGV != 2 ) {
  die $usage;
}

#preset do unload and do NOT delete
$do_delete=0;
$do_unload=1;
$do_script=0;

#check for -s option
if ( $opt_s ) {
    $do_script=1;
    $do_delete=0;

} else {
    #check for -u option
    if ( $opt_u ) {
	$do_unload=0;
    } elsif ( $opt_d ) {
	$do_delete=1;
    }
}

#check for -u option
if ( $opt_n ) {
    $new_count=$opt_n;
} else {
    $new_count=0;
}


#get other args
$database=$ARGV[0];
$table=$ARGV[1];
$tempdir=$ARGV[2];


#check for existing unload file or writeable temp dir
if (! $do_unload) {
    if ( ! -r "$tempdir/$table.unl" ) {
	die "Unload file: $tempdir/$table.unl not found\n";
    }
} elsif ( ! -d $tempdir or ! -w $tempdir ) {
    die "Temp dir: $tempdir must be a writeable directory with enough
space\n";
}

#-------------------------------------------------------------------------------
# setup permanent or temporary script file
#-------------------------------------------------------------------------------

if ( $do_script ) {
    $tmpsql="$tempdir/$table.sql";
} else {
    $tmpsql="/tmp/dfg_$$.sql";
}

#-------------------------------------------------------------------------------
#setup exit handling
#-------------------------------------------------------------------------------

sub exithandler {  # 1st argument is signal name
	local($sig) = @_;
	&cleanNdie("Caught a SIG$sig--shutting down");
	exit(0);
}

#exit cleanup
sub cleanNdie {

    local ($message) = @_;	 # 1st argument is message

    chomp $message;

    die $message."\n";
}

$SIG{'INT'} = 'exithandler';
$SIG{'QUIT'} = 'exithandler';

#-------------------------------------------------------------------------------
# take optional 3rd arg get the number of records for the table
#-------------------------------------------------------------------------------


if ( $new_count > 0 ) {
    $count=$new_count;
}
#get count from database
 else {
    $count=`dbaccess $database <<!
select count(*) count from $table;
!`;


    if ( $count =~ /ERROR/im ) {
	die "Error on database access for $table\n";
    }
}

#remove the header etc.
$count =~ s/\A\s*count\s*([0-9]+)\s*/$1/m;
if ( ! $count) {
    die "Cannot establish number of records\n";
}

#-------------------------------------------------------------------------------
# get the schema for the table and convert
#-------------------------------------------------------------------------------

$schema = `dbschema -d $database -t $table`;
$schema =~ s/^DBSCHEMA.*$//m;
$schema =~ s/^Copyright.*$//m;
$schema =~ s/^Software Serial.*$//m;
$schema =~ s/\A\s*(\{ TABLE)/$1/m;

#$schema =~ s/^revoke\s*(.*)\s*from\s*(.*);$/grant $1 to $2/;

#get the column details etc.

if ( $schema =~ /^\s*\{ TABLE.*row size =\s*([0-9]+)\s*number of columns
=\s*([0-9]+)\s*index size =\s*([0-9]+)\s*\}/i ) {

    $row_size=$1;
    $num_columns=$2;
    $index_size=$3;

    #caluclate the current extent used 
    if ( $schema =~ /\b(?:TEXT|BYTE)\s+in\s+/i ) {
	$ext=(($count/(1+(2016/$row_size))) +
(($index_size/(1+(2016/$row_size)))*1.25));

    } else {
	$ext=10*(($count/(1+(2016/$row_size))) +
(($index_size/(1+(2016/$row_size)))*1.25));
    } 

    $ext=int($ext);

    # set the xtent according to the current size
    if ( $ext > 10000) {
	$extstuff='EXTENT SIZE 5120 NEXT SIZE 5120';
    } elsif ( $ext > 5000) {
	$extstuff='EXTENT SIZE 5120 NEXT SIZE 2048';
    } elsif ( $ext > 2000) {
	$extstuff='EXTENT SIZE 2048 NEXT SIZE 1024';
    } elsif ( $ext > 1000) {
	$extstuff='EXTENT SIZE 1024 NEXT SIZE 256';
    } elsif ( $ext > 500) {
	$extstuff='EXTENT SIZE 512 NEXT SIZE 128';
    } elsif ( $ext > 100) {
	$extstuff='EXTENT SIZE 256 NEXT SIZE 64';
    } elsif ( $ext > 50) {
	$extstuff='EXTENT SIZE 64 NEXT SIZE 16';
    } else {
	$extstuff='';
    }
    
    # add the unload info after the TABLE stuff
    $unlinfo="{ unload file name = $tempdir/$table.unl number of rows =
$count }";
    $schema =~ s/(\{ TABLE.*row size .*index size =.*\})/$1\n$unlinfo/;

} else {
    die "Cannot establish column details\n";
}

#-------------------------------------------------------------------------------
# get the permissions and schema for the table
#-------------------------------------------------------------------------------

$permissions='';
$drop_indeces='';

open(PERM,"dbschema -t $table -p all -d $database|") || die "Cannot
establish permissions\n";
while (<PERM>) {

    #look for current table only
    if ( /^(.*\s+(?:on|from)\s+.*\b$table\s+to\s+.*;)/ ) {
	$permissions .= $1."\n";
    } elsif ( /^create.*index\s+(.*) on .*/ ) {	#look for any indeces to
drop
	$drop_indeces .= "DROP INDEX $1;\n";
    }
}
close(PERM);


#-------------------------------------------------------------------------------
# create the new sql
#-------------------------------------------------------------------------------

$rn_table=$table.'X';

$new_sql  = "BEGIN WORK;\n";
$new_sql .= "SET LOCK MODE TO WAIT 300;\n";
$new_sql .= "LOCK TABLE $table IN EXCLUSIVE MODE;\n";
$new_sql .= "\n{Move the table to one side}\nRENAME TABLE $table TO
$rn_table;\n";
$new_sql .= "\n{Unload the data - quicker than table to table}\nUNLOAD
TO $tempdir/$table.unl SELECT * FROM $rn_table;\n" if $do_unload;
$new_sql .= "\n{Drop the indexes now before creating copy
table}\n".$drop_indeces."\n"; 

#add extent info and the load from file
$loadstuff="\n\n{Reload the data}
	LOAD FROM $tempdir\/$table.unl INSERT INTO $table;
	SELECT 1 id,count(*) numrecords FROM $rn_table INTO TEMP tmp_countorig;
	SELECT 1 id,count(*) numrecords FROM $table INTO TEMP tmp_countnew;
	SELECT 'FORCED ERROR' info from tmp_countorig o,tmp_countnew n
	  WHERE o.id=n.id AND o.numrecords <> n.numrecords;
\n";
$schema =~ s/^\s*\);\s*$/ ) $extstuff LOCK MODE ROW;$loadstuff/;

$new_sql .= $schema;

$new_sql .= "{Now drop the original table}\nDROP TABLE $rn_table;\n";

#-------------------------------------------------------------------------------
# open script file
#-------------------------------------------------------------------------------

open (SQL_S,">$tmpsql") || &cleanNdie("Cannot open $tmpsql for
writing\n");
print SQL_S $new_sql;
print SQL_S $permissions;
close(SQL_S);


#-------------------------------------------------------------------------------
# either print or run sql script
#-------------------------------------------------------------------------------

if ( $do_script ) {
    print "script file:$tmpsql created\n";

} else {

    print STDOUT "Dropping and recreating table using the following
sql:\n----------------------------------------------------\n$new_sql\n\napply
table permissions\n...\n ";

    if ( system("/usr/local/bin/commit_ifok.pl $database $tmpsql 3000")
!= 0 ) {
	&cleanNdie("*ERROR* SQL error on $database while running: $tmpsql\n");
    }

    unlink $tmpsql;
}

#cleanup
unlink $tempdir.'/'.$table.'.unl' if $do_delete;
#eof




#!/usr/local/bin/perl5
# commit_ifok.pl - script to run an SQL file and only commit if no
errors

#This script is supplied without warrenty of any kind. If it is useful I
am glad.
#If it trashes your database, I or my company are not responsible.

$usage="Usage: commit_ifok.pl [-W] database sqlfile [wait_in_secs]
	-W = wait until backups are finished\n";

use strict 'subs';
use strict 'refs';


#-------------------------------------------------------------------------------
#check for -W argument (wait for backups)
#-------------------------------------------------------------------------------
if ( $ARGV[0] eq '-W' ) {
    $check_backups=1;
    shift;
} else {
    $check_backups=0;
}


#-------------------------------------------------------------------------------
#check args
#-------------------------------------------------------------------------------
if ( $#ARGV != 1 && $#ARGV != 2 ) {
  die $usage;
}

$dbname=$ARGV[0];
$sqlfile=$ARGV[1];


#-------------------------------------------------------------------------------
#get optional wait time (3rd arg)
#-------------------------------------------------------------------------------
if ( $ARGV[2] ) {
    $sleepsupplied=1;
    $sleeptime=$ARGV[2];
} else {
    $sleepsupplied=0;
    $sleeptime=600;
}

#-------------------------------------------------------------------------------
#setup temporary files
#-------------------------------------------------------------------------------
$tmp="/tmp/cok$$";
$tmpfin="$tmp.fin";
$tmpout="$tmp.out";

umask(000);
unlink $tmpfin;
unlink $tmpout;

#-------------------------------------------------------------------------------
#exit cleanup subroutine
#-------------------------------------------------------------------------------
sub cleanNdie { # 1st argument is message
    local($message) = @_;

    #close the pipe
    close(PIPE);
    close(SQLFILE);
    close(OUTFILE);
    unlink $tmpfin if $tmpfin;
    unlink $tmpout if $tmpout;
    print STDERR "\n$message\n";
    exit(1);
}

#-------------------------------------------------------------------------------
#setup exit handling for ^C etc
#-------------------------------------------------------------------------------

sub exithandler {  # 1st argument is signal name
    local($sig) = @_;
    &cleanNdie("Caught a SIG$sig--shutting down");
    exit(0);
}


$SIG{'INT'} = \&exithandler;
$SIG{'QUIT'} = \&exithandler;


#-------------------------------------------------------------------------------
#open sql file for reading
#-------------------------------------------------------------------------------
if ( ! -s $sqlfile || ! open(SQLFILE,"<$sqlfile") ) {
  die "Cannot open $sqlfile or it is empty\n";
}

#-------------------------------------------------------------------------------
# ensure stderr has forces flushing
#-------------------------------------------------------------------------------

select(STDERR); $|=1;	#force flushing

#-------------------------------------------------------------------------------
#check if backups are running and suspend
#-------------------------------------------------------------------------------
if ( $check_backups ) {

    $trytime=180;	#try for three hours
    $wait_time=60;	#wait for XX secs per iteration
    $message_times=5;	#print message every XX waits

    #check the dbpath variable for remote server
    $DBPATH=$ENV{'DBPATH'};
    if ( $ENV{'DBPATH'} gt " " ) {
	$server=$DBPATH;
	$server=~ s/^\/+//;	#remove leading slashes
	$rsh='rsh '.$server;
    } else {
	$rsh='';
    }
	
    $tryit=0;
    $message=$message_times;
    $running=1;
    while ($running && ++$tryit < $trytime ) {
	$ps_info=`$rsh ps -ef`;
	if ( $ps_info !~ /tbtape -s/m ) {
	    $running=0;
	} else {
	    if ( ++$message > $message_times ) {
		print STDERR " Waiting for backup to finish\n";
		$message=1;
	    }
	    sleep($wait_time);	#wait for one minute
	}
    }

    #die if backups still running
    if ( $running ) {
	print STDERR "*ERROR* Backups still running after $trytime minutes -
aborting\n";
	exit 1;
    }
 
}

#-------------------------------------------------------------------------------
#run dbaccess via a pipe
#-------------------------------------------------------------------------------
open(PIPE,"|dbaccess $dbname > $tmpout 2>&1 ") || &cleanNdie("Cannot run
dbaccess");


#-------------------------------------------------------------------------------
#write the intiial transaction
#-------------------------------------------------------------------------------
select(PIPE); $|=1;	#force flushing
print PIPE "BEGIN WORK;\n" || &cleanNdie("Cannot output to dbaccess");


#-------------------------------------------------------------------------------
#read each line in file and write to pipe unless begin/commit work
#-------------------------------------------------------------------------------

while (<SQLFILE>) {
      
    	$line=$_;

    	#delete any begin/commit works
	$line =~ s/(BEGIN|COMMIT)\s+WORK(\s*;)*//i;

	select(PIPE); $|=1;	#force flushing
	print PIPE $line || &cleanNdie("Cannot output to dbaccess");

	#clockup sleep counter - allow 40 lines per sec
	if( ! $sleepsupplied && ++$numlines > 40 ) {
	   $sleeptime += 1;
	   $numlines=0;	#reset counter
	}
}

close(SQLFILE);

#-------------------------------------------------------------------------------
#command to ensure buffers flushed by dbaccess
#-------------------------------------------------------------------------------
select(PIPE); $|=1;	#force flushing
print PIPE "CREATE TEMP TABLE TMP_cok$$(foo date,bar date);\n";
select(PIPE); $|=1;	#force flushing
print PIPE "INSERT INTO TMP_cok$$ values(TODAY,TODAY);\n";
select(PIPE); $|=1;	#force flushing
print PIPE "UNLOAD TO $tmpfin SELECT 'END_OF_COMP_CHECKING',* FROM
TMP_cok$$;\n";

sleep(5);	#let everything settle down

#-------------------------------------------------------------------------------
#wait for dbaccess to finish processing data
#-------------------------------------------------------------------------------
select(STDERR); $|=1;	#force flushing
$numtrys=0;
print STDERR " All SQL sent to dbaccess - waiting...  ";
while ( ! -f $tmpfin && $numtrys < $sleeptime ) {
  sleep(1);
  ++$numtrys;
}

if ( -f $tmpfin && $numtrys == 0 ) {
  print STDERR "Finished ";
} elsif ( -f $tmpfin ) {
  print STDERR "Finished after $numtrys secs ";
} else {
  &cleanNdie("*ERROR* not finished within time limit ($sleeptime secs) -
aborting");
}


#-------------------------------------------------------------------------------
#now look for errors in the output file
#-------------------------------------------------------------------------------
open (OUTFILE,"<$tmpout") || &cleanNdie("Cannot open dbaccess output");

$found_err=0;
$numfound=0;
$last_line='';
nextout: while (<OUTFILE>) {

    ++$numfound;
    #debug display the errors found;
    chop;
    if ( /^  256: Transaction not available/) {
	$found_err=-1;	#not much we can do about this
	last nextout;
    }
    #check for errors - NOTE 'FORCED ERROR' can be used within select to
force an error
    elsif ( /^Error in line/ || /FORCED ERROR/ || /ISAM error/ ||
/^\s*[0-9]+:\s+.*/ ) {
	if ( ! $found_err ) {
          $error_line=$last_line."\n".$_;
	  $found_err=1;
	}
	last nextout;
    } elsif ( /END_OF_COMP_CHECKING/ ) {
	last nextout;
    }
    $last_line=$_;
}

if ( $numfound < 1 ) {
   print STDERR "No output found\n";
} elsif ( $found_err == 1 ) {
   select(PIPE); $|=1;	#force flushing
   print PIPE "ROLLBACK WORK;\n" || &cleanNdie("Cannot output to
dbaccess");
   &cleanNdie("*SQL ERROR* - ROLLED BACK\n$error_line");
} elsif ( ! $found_err ) {
   select(PIPE); $|=1;	#force flushing
   print PIPE "COMMIT WORK;\n" || &cleanNdie("Cannot output to
dbaccess");
   #print STDOUT "Committing Transaction\n";
}

#-------------------------------------------------------------------------------
#exit cleanly and remove temporary files
#-------------------------------------------------------------------------------
close(PIPE);
close(OUTFILE);
close(SQLFILE);
unlink $tmpfin if $tmpfin;
unlink $tmpout if $tmpout;
print STDOUT "RAN OK\n";

exit(0);

#eof

-- 
# John Clutterbuck               Tel: +44 1703 765021
# Siemens Business Services      email: jclutterbuck@sbs.siemens.co.uk
# Phi House, Enterprise Road
# Chilworth, Hampshire, SO16 7NS