Re: Best Way to Compare Informix Databases
Here is a shell script using awk and sed:
(Take out the line numbers)
----------------------------------------------
# Print table, column, and index information from a dbschema output,
2 # sorting table names but preserving column order
3 # Use: fmtschema <dbschema-output-file> > newfile
4 # mmf 1/2/1998
5
6 cat $1 |\\
7 grep -v "^[ ]*#" |\\
8 egrep -i "create table| [a-z]|create.*index" $1 |\\
9 sed 's/CREATE TABLE/create table/' |\\
10 sed 's/CREATE INDEX/create index/' |\\
11 sed 's/CREATE UNIQUE INDEX/create unique index/' |\\
12 grep -v " CALL " |\\
13 grep -v " DATABASE " |\\
14 grep -v "primary key.*constraint" |\\
15 sed 's/constraint.*//' |\\
16 sed 's/[ ]*#.*//' |\\
17 sed 's/,$//' |\\
18 sed 's/"[a-z]*"\\.//g' |\\
19 awk '{if ($1=="create" && $2=="table") \\
20 { tableid=$0; print $0; colnum=0} \\
21 else \\
22 if (length($0) > 0) { colnum++;
23 if (colnum > 99) {
24 print tableid colnum "qqq" $0
25 }
26 else if (colnum > 9) {
27 print tableid " " colnum "qqq" $0
28 } else {
29 print tableid " " colnum "qqq" $0
30 }
31 }
32 }' |\\
sort |\\
35 sed 's/^create.*qqq//' |\\
36 sed 's/^CREATE.*qqq//' |\\
37
38 awk '{if ($1=="create" && $2=="table") \\
39 { print "\\n\\n" $3} \\
40 else
41 { print $0}
42 }'
On Wed, 25 Mar 1998, Surfer wrote:
> In article <6f8ded$hr4$1@news.xmission.com>, Tim Owens <towens@bsat.com>
> writes
> >
> >What is the best way to compare two informix databases?
> >
> >My current approach is to run dbschema on both databases, then diff the output.
> >
> >However, since diff only does a line-by-line file comparison, one difference
> >between the databases causes a domino effect in the line-by-line comparison
> >of the schema output.
>
> 'sdiff' is better than 'diff' and will manage to realign itself after
> one different line. I normally use it on source code (sdiff -w80 -s
> file1 file2) but I'm sure it would work great on dbschema outputs.
>
> >
> >Does informix have a built-in tool that would provide a better way?
> >
> >Any help would be appreciated,
> >
> >Tim Owens
> >
>
> --
> Surfer!
> http://www.nevis-vieww.demon.co.uk
> http://www.nevis-vieww.demon.co.uk/flash
> Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.
>
+----------------------------------------------------------------------------+
| Michael M Fox Cardinal Hill Rehabilitation Hospital |
| mmf@cardinalhill.org Phone: 606/254-5701 |
| FAX: 606/253-2906 |
| "..and repentance for forgiveness of sins should be proclaimed in His name"|
| -- Luke 24 |
+----------------------------------------------------------------------------+