Re: Help: Reducing Extents
Chad Laity wrote:
>
> 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??
>
> Thanks,
> Chad Laity
> chad_laity@arrowintl.com
> wick@fast.net
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 $dat