From: dennisp@informix.com (Dennis Pimple) Subject: Re: upgrading a database whilst moving it Date: 22 Nov 1995 17:13:00 GMT Richard (R.) Bown (rbown@bnr.co.uk) 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, .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 dennisp@informix.com 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