Re: dbexport, dbschema do not keep chunk, blob info. Bug? Feature?
>
> >
> >Have you checked out a little utility called pres_ext? It should be hiding
> >out on the archives. It is a shell which generates a schema which retains
> >dbspace names and extent information. You can just replace the dbimport sql
> >file with it. Or you can upgrade to 6.0 where all that is taken care of. If
> >you want the script and can't get it easily from the archives, let me know,
> >I have a copy around here somewhere.
> >
> Jack: You just knew someone was going to take you up on your offer. Would
> you please encode it (if you can; if not, good ol' ascii) and send it my way ?
> I wrote a routine to do this once but forgot to take it with me when my contract
> ended. Thanks.
>
I guess I did, I guess I also made the offer in public. Wonder where I left
my brain. So here it is in public.
Note: the produced schema contains the "Informix dbschema" header which should
be removed before running. I was going to take it out at one point and am not
really sure why I didn't.
--- cut here -----
# 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 Fri Mar 4 18:06:19 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
--- and here -----
_____________________________________________________________________________
Jack Parker |
Hewlett Packard, BSMC Boise, Idaho, USA| No soap - radio!
jparker@hpbs3645.boi.hp.com |
_____________________________________________________________________________
Any opinions expressed herein are my own and not those of my employers.
_____________________________________________________________________________