PREServe EXTents dbschema utility
The preserve extents utility that Peter requested. I had something similar
laying around and cleaned it up a bit.
------ cut here ---------------------
############################################################################
#
# 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.
#
# I highly recommend that you run this in its own subdirectory
# so you look at the files without distraction.
#
# 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 2/18/94
############################################################################
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.
tbcheck -pe | grep $database | sort | awk '
BEGIN {
old_tab="XXXXXXXXXX"
old_ext=0
}
{
n=split($1,tab,".")
tabname=tab[2]
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 } ' | grep -v "WARNING" > extents
# we still don't have nextsize. get it.
echo "select tabname, nextsize from 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 -e "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.
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
------- and here --------------------------
cheers
j.
_____________________________________________________________________________
Jack Parker | If you took all of the accountants
Hewlett Packard, BSMC Boise, Idaho, USA| in the world and placed them
jparker@hpbs2561.boi.hp.com | end to end...
(208) 396-5388 (W) (208) 384-1623 (H) | it would be a good thing.
_____________________________________________________________________________
Any opinions expressed herein are my own and not those of my employers.
_____________________________________________________________________________