Subject: Re: upgrading a database whilst moving it
From: [email-masked] (Dennis Pimple)
Date: 22 Nov 1995 17:13:00 GMT

Richard (R.) Bown ([email-masked]) wrote:
: Running Informix OnLine 5.0.

: The Informix commands dbschema, dbexport and dbimport create an
: ASCII image of the schema and data associated with a specified
: database.  dbexport produces a subdirectory consisting of the
: schema of the database plus the data files.

: As far as I can tell, dbexport provides a schema, <database>.sql
: as dbschema does but with additional 'links' to the ASCII data files
: of the actual database.

: When we reinstall the database onto a new platform with we wish
: to add some fields or tables to the schema.  In this way we will
: have created a backwards compatible superset of the original data
: which we can then 'massage' into shape.  Ha.

: So we want a copy of the data on the new machine, but under a new
: schema with some new fields or tables added.

: If it *is* do you have to alter the data files to reflect the addition
: of the new fields/tables or will dbimport allow creation of the fields
: with null value without changing the data?

No. You will get a column number mismatch error; dbimport and the LOAD
statment require a 1-for-1 match of delimited columns on each line of
the file to columns in the table.

: Would we have to hack the data files to represent the schema changes?

Yes, but see below.

: This procedure is time critical, the shorter the time taken the better.
: Would this be the most efficient way of copying the database including
: the changes?

My approach would be to do a "normal" dbexport/dbimport, then run a
script with the ALTER TABLE and other statements in it. There are
details (like: it would be faster to not have the indexes or
constraints on tables during the loads, etc.

Attached to the bottom of this message is my "dbsh" shell, which
creates a series of .sql files from an existing database (via dbschema)
that may assist in the migration. Be careful when using it; don't run
any of the scripts against a production database without knowing what
it's going to do.

: Has anyone done this procedure?  Is it possible?

: Any comments or suggestions on this procedure would be welcome.

: Rich.

--

=======================================================================
Dennis J. Pimple         [email-masked]    Opinions expressed
Principal Consultant     --------------------    are mine, and do not
Informix Software Inc    Voice:  303-850-0210    necessarily reflect
Denver Colorado USA      Fax:    303-779-4025    those of my employer.

#!/bin/sh
# dbsh - run dbschema on $DBNAME and break it into a set of
#        .sql scripts for creating/dropping tables, indexes,
#        constraints, triggers, and stored procedures.
# Also, if *.ul files exist in the directory, a LOAD.sql script
# is created with the LOAD FROM ... syntax.

echo "Running dbschema to get trigger & constraint list ..."
dbschema -q -t all -d $DBNAME 01
# file 01 has create table, create index, revoke, grant,
# alter table for constraints, and create trigger statements

# remove all the "revoke" statements
echo "Removing revokes ..."
cat 01 | grep -v "^revoke" > 02
mv 02 01

# create an sql file with only the "create table" statements
echo "Creating table scripts ..."
cat 01 | awk '/^create table/, /;$/' > creat_tabls.sql
# in case we need it, create a "drop table" set
grep "^create table" creat_tabls.sql > 02
sed 's/create/drop/' 02 > 03
sed 's/$/;/' 03 > drop_tabls.sql

# create an sql file with "update statistics for table" statements
cat drop_tabls.sql | sed "s/drop/update statistics for/" > tabl_stats.sql

# create an sql file with only the "create index" statements
echo "Creating index scripts ..."
cat 01 | awk '/^create unique/, /;$/' > creat_idxs.sql
cat 01 | awk '/^create distinct/, /;$/' >> creat_idxs.sql
cat 01 | awk '/^create cluster/, /;$/' >> creat_idxs.sql
cat 01 | awk '/^create index/, /;$/' >> creat_idxs.sql
# create a "drop index" set; name of index
grep "index" creat_idxs.sql | cut -f2 -d"." | cut -f1 -d" " > 02
sed "s/^/drop index /" 02 > 03
sed "s/$/;/" 03 > drop_idxs.sql

# create an sql file with only the "alter table" statements
echo "Creating constraint scripts ..."
cat 01 | awk '/^alter table/, /;$/' > creat_constr.sql
# create a "drop constraint" set
grep "alter table" creat_constr.sql | cut -f1-3 -d" " > 02
# get the list of constraint names
grep ");" creat_constr.sql | cut -f2- -d"." | cut -f2 -d"." > 03
paste -d "|" 02 03 > 04
# take the pipe from the paste command and complete the sql script
sed 's/|/ drop constraint \(/' 04 > drop_constr.sql

# create an sql file with only the "create trigger" statements
echo "Creating trigger scripts ..."
grep -i "create trigger" 01 | tr "[:upper:]" "[:lower:]" > 02
sed 's/create/drop/' 02 > 03
cat 03 | cut -f1-3 -d" " > 02
sed 's/$/;/' 02 > drop_trgs.sql
cat 01 | awk '/^create trigger/, /\);$/' > creat_trgs.sql

echo "Running dbschema to get SPLs list ..."
dbschema -q -f all -d $DBNAME  > creat_spls.sql

# create an sql file with only the "create procedure" statements
echo "Creating spl scripts ..."
grep -i "^create procedure" creat_spls.sql | \
    tr "[:upper:]" "[:lower:]" > 01
sed 's/create/drop/' 01 > 02
cat 02 | cut -f1 -d"(" > 01
sed 's/$/;/' 01 > drop_spls.sql

# create an sql file with "update statistics for procedure" statements
cat drop_spls.sql | sed "s/drop/update statistics for/" > spl_stats.sql

echo "Removing specific ownership ..."
for FILE in creat_tabls.sql drop_tabls.sql \
            creat_idxs.sql drop_idxs.sql \
            creat_constr.sql drop_constr.sql \
            creat_trgs.sql drop_trgs.sql \
            creat_spls.sql drop_spls.sql \
            tabl_stats.sql spl_stats.sql;do
    # get a list of owners of various stuff so we can strip it out
    grep "\"\." $FILE | cut -f2 -d "\"" | sort -u > 02
    # use the list to strip out ownership
    for owner in `cat 02`;do
        sed "s/\"$owner\"\.//g" $FILE > 03
        mv 03 $FILE
    done
done

rm -f 01 02 03 04

echo "Creating load script ..."
rm -f LOAD.sql
for UNLOAD in *.ul;do
    if [ -s $UNLOAD ];then
        TABLE=`echo $UNLOAD | cut -f1 -d"."`
        echo "LOAD FROM $UNLOAD INSERT INTO $TABLE;" >> LOAD.sql
    else
        rm -f $UNLOAD
    fi
done

echo "DONE!"
ls -la *.sql