RE: Reducing Extents on Informix 5.0 and hp-ux
>From: ilist
>To: informix-list
>Subject: Reducing Extents on Informix 5.0 and hp-ux
>Date: 10 July 1995 11:01
>I am wondering if anybody has done the follwoing:
>1) Trying to reduce Extents for performance improvement on an Informix
>database version 5.0.
>2) any other issues that we can use to improve the performance of Informix
>online in an hp-ux version 9.04 environment.
>Thanks
>Saeid Yazdanmehr
The script below may go a little way to helping you. You can probably modify
it to suit
your requirements. Basically it will modify the dbschema sql script created
by
dbexport and add extent and lock information for each table with an entry in
a control
file. Try it on a test database to ensure that you are happy with it.
Mark Denham
BBC
London, UK
Mark.Denham@bbc.co.uk
------------------------------------------- Start of shell script
-----------------------------------------------------
:
############################################################################
###
#
# Author: M. Denham
#
# Date: 26-5-95
#
# Purpose: Edit the schema file generated by dbexport/dbschema to include
# DBSPACE, EXTENT and LOCK information when creating tables. This
# information is not present in the default files (a pain). This
# file does not need to contain an entry per table. It probably
# makes it easier to ensure that the tables are setup correctly
# however. It works using a control file which contains the
tablename,
# new extent information and lock mode required (all optional) and
edits
# the dbschema file you specify on the command line.
#
############################################################################
###
progname=$0
errorexit() {
echo "USAGE: $progname [-c <controlfile> -s <schemafile>] | -h"
exit 1
}
helpmessage() {
echo "USAGE: $progname [ -c <controlfile> -s <schemafile> ] | -h\\n"
echo "WHERE:\\n"
echo "controlfile - Full pathname of file containing extent size and"
echo " lock level information for tables that are to"
echo " have different values from INFORMIX DEFAULT values"
echo "schemafile - Schema file created by DBEXPORT/DBSCHEMA that
needs"
echo " modifying prior to installation."
exit 0
}
readopts() {
while getopts c:s:h option $*
do
case $option in
c)
controlfile=$OPTARG
;;
s)
schemafile=$OPTARG
;;
h)
helpmessage
;;
?)
errorexit
;;
esac
done
# Check program is being used correctly
shift $(($OPTIND - 1))
if [ ! -z "$*" -o -z "$controlfile" -o -z "$schemafile" ]
then
errorexit
fi
if [ ! -r $controlfile ]
then
echo "$progname: Controlfile does not exist or is not readable!"
fi
if [ ! -r $schemafile ]
then
echo "$progname: Schemafile does not exist or is not readable!"
fi
}
readopts $@
awk '
# Ignore shell comments in input files
/^#/ {
continue
}
# Ignore blank lines in control file
$1 == "" && FILENAME != schemafile {
continue
}
# read tablename from control file
/^[a-zA-Z][a-zA-Z0-9_]*:$/ {
tabname=substr($1, 1, index($1, ":")-1)
continue
}
# Has a dbspace been specified
/[ ]*dbspace/ {
dbspace[tabname]=$2
continue
}
# Has a first extent size been specified
/[ ]*first extent/ {
firstext[tabname]=$3
continue
}
# Has a next extent size been specified
/[ ]*next extent/ {
nextext[tabname]=$3
continue
}
# Has the lock mode been specified
/[ ]*lock mode/ {
lockmode[tabname]=$3
continue
}
# Read schema and find out table name
/create table/ {
stabname=substr($3,index($3,".")+1, length($3))
}
# Look for the end of the table definition
/^[ ]*\\);$/ {
# Write the information needed to define the dbspace, extent and
# lock mode for this table.
print ")"
if( dbspace[stabname] != "" ) {
printf " in %s\\n" dbspace[stabname]
}
if( firstext[stabname] != "" ) {
printf " extent size %s\\n", firstext[stabname]
}
if( nextext[stabname] != "" ) {
printf " next size %s\\n", nextext[stabname]
}
if( lockmode[stabname] != "" ) {
printf " lock mode %s\\n", lockmode[stabname]
}
print ";\\n"
continue
}
{ print $0 }
' schemafile=$schemafile $controlfile $schemafile >$schemafile.t
if [ $? = 0 ]
then
cp $schemafile.t $schemafile
fi
# End of shell script
--------------------- Example control file starts here
------------------------
############################################################################
###
#
# Author: M. Denham
#
# Date: 26-5-95
#
# Usage:Edit the schema file generated by dbexport/dbschema to include
# DBSPACE, EXTENT and LOCK information when creating tables. This
# information is not present in the default files (a pain). This
# file does not need to contain an entry per table. It probably
# makes it easier to ensure that the tables are setup correctly
# however.
#
############################################################################
###
cl_activity_code:
lock mode row
cl_activity_detail:
first extent 10240
next extent 2048
lock mode row
cl_activity_list:
lock mode row
cl_activity_pool:
first extent 7168
next extent 1024
lock mode row
cl_activity_set:
first extent 47
lock mode row
cl_activity_used:
first extent 2048
next extent 512
lock mode row
cl_alarm_list:
lock mode row
cl_alarm_pool:
first extent 6144
next extent 1024
lock mode row
cl_alarm_set:
lock mode row
cl_call_detail:
first extent 5120
next extent 1024
lock mode row
cl_call_log:
first extent 5120
next extent 1024
lock mode row
cl_call_no:
lock mode row
cl_call_type:
lock mode row