PREServe EXTent info
Pholks,
The following is a script I cobbled together last year on impulse. It
preserves extent information. Please let me know if you have problems
running it or if you like it.po
cheers
j.
_____________________________________________________________________________
Jack Parker yauib* - Hewlett Packard, BSMC Boise, Idaho, USA
jparker@hpbs3645.boi.hp.com
_____________________________________________________________________________
* yet another unix/informix bigot
_____________________________________________________________________________
Any opinions expressed herein are my own and not those of my employers.
_____________________________________________________________________________
# This is a shell archive. Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# Wrapped by /Informix <informix@hpbs2651> on Tue Sep 27 12:30:21 1994
#
# This archive contains:
# pres_ext
#
LANG=""; export LANG
PATH=/bin:/usr/bin:$PATH; export PATH
echo x - pres_ext
cat >pres_ext <<'@EOF'
############################################################################
#
# pres_ext: PREServe EXTents. This is a shell script to generate an SQL
# command file (suitable for dbimport) which preserves the
# dbspace, first extent and (what we really wanted) next extent
# info.
#
# pres_ext usage: pres_ext database dbspace [$TBCONFIG]
#
# Notes: It is advised that you check the output very carefully before running
# it. Output is written to newschema.sql. Currently none of the
# temporary files are removed - just so you can test.
#
# Not all awks were created equal and this script uses a lot of awk.
# check to ensure that it does what its supposed to.
#
# This is offered to the world at large. No warranties expressed or implied.
# If you run the SQL command and it trashes your database NMFP. The reason
# for offering this is so that y'all can tell me what's wrong with it so
# WE won't have problems with it. I therefore ask that you let me know
# how it works and any problems you run into. I will make updates available.
#
# Jack Parker jparker@hpbs2561.boi.hp.com
############################################################################
if [ $# -lt 2 ]
then
echo "pres_ext usage: pres_ext database dbspace [$TBCONFIG]"
exit -1
fi
database=$1
dbspace=$2
if [ $# -eq 3 ]
then
TBCONFIG=$3
elif [ -z "$TBCONFIG" ]
then
echo "TBCONFIG is not set"
exit -1
fi
tbcnf=$INFORMIXDIR/etc/$TBCONFIG
if [ ! -r "$tbcnf" ]
then
echo "Can't read $tbcnf"
echo "file is not readable by this process."
exit -1
fi
pgsize=`grep BUFFSIZE $INFORMIXDIR/etc/$TBCONFIG | awk '{print $2}'`
pgsize=`expr $pgsize / 1024`
# this give you a sorted of each table and how many extents.
# the awk file adds the duplicated tablename extents together.
# the grep -v "WARNING" gets rid of the WARNING messages.
rls=`grep Version $INFORMIXDIR/etc/OnLine-cr | awk '{print substr($3,1,1)}'`
tbcheck -pe | grep $database | sort | awk '
BEGIN {
old_tab="XXXXXXXXXX"
old_ext=0
}
{
#
# check for "." vs ":", if ":" then is online 4.0 use tab[3]
# perhaps check release_level instead?
# Note: this fix is untested.
#
if ( rls > 4 ) {
n=split($1,tab,".")
tabname=tab[2]
}
else {
n=split($1,tab,":")
tabname=tab[3]
}
if (tabname != old_tab) {
if (NR > 1) {
print old_tab, old_ext }
old_ext=0
}
old_tab=tabname
old_ext+=$3
}
END { print old_tab, old_ext } ' rls=$rls | grep -v "WARNING" > extents
# we still don't have nextsize. get it.
echo "select tabname, nextsize from informix.systables where tabid > 99" > tmp.sql
isql -s $database tmp > nextsize
rm -f tmp.sql
dbschema -d $database > $database.schm
# the schema is in any old order - the extent files
# are guaranteed not to match. We want to do serial I/O later on
# so we need them in the same order. Make it so....
grep "create" $database.schm |grep "table" | while read tabname
do
tab=`echo $tabname | awk '{ n=split($3,tab,".")
print tab[2]}'`
grep "^$tab " extents >> extents2
grep "^$tab " nextsize >> extents2
done
# we now have a file with every second line being the nextsize
# and in the same order as the schema file. join these lines
# so that we have "table first_ext next_ext"
awk '
BEGIN { old_tabname = "" }
{
tabname = $1
ext=$2
if (old_tabname == tabname) {
print tabname, old_ext, ext
}
else { old_ext=$2 }
old_tabname= tabname
} ' < extents2 > extents3
# now we read the schema - each time we run into the last line of
# a CREATE TABLE we tack on the extent and dbspace info.
#(sunos needs nawk here)
awk '
{
if ( $1 == "create" && $2 == "table" ) {
n = split($3,tb,".")
tbname = tb[2]
exteof = getline extline < "extents3"
n = split(extline, inline)
if (inline[1] == tbname) {
fxt = inline[2]
nxt = inline[3]
}
else print "Wrong table ! " tbname inline[1]
}
if ( $1 == ");") print ") IN " dbspace " EXTENT SIZE " fxt*pgsize " NEXT SIZE " nxt*pgsize ";"
else print $0
}' < $database.schm pgsize=$pgsize dbspace=$dbspace > newschema.sql
# clean up
# rm -f extents extents2 extents3 nextsize $database.schm
@EOF
chmod 644 pres_ext
exit 0