Re: Best Way to Compare Informix Databases
--IMA.Boundary.2665670980
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
Tim
I have attached two shell scripts that I use here. I inherited them
from the DBAs who preceded me, so I dont know where it came from.
Maybe it came from this newsgroup?
The first script compares columns and the next compares indexes. Both
of them read the system catalogs so you wont have the problem that you
have with the Unix diff.
I have used it for the last 2 years now and it runs ok.
HTH
Sujit Pal
______________________________ Reply Separator _________________________________
Subject: Best Way to Compare Informix Databases
Author: "Tim Owens" <towens@bsat.com> at Internet
Date: 3/24/98 9:26 AM
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.
Does informix have a built-in tool that would provide a better way?
Any help would be appreciated,
Tim Owens
--IMA.Boundary.2665670980
Content-Type: application/octet-stream; name="comp_i~1.sh"
Content-Transfer-Encoding: base64
Content-Description: Unknown data type
Content-Disposition: attachment; filename="comp_i~1.sh"
#!/bin/ksh
# PROGRAM NAME: comp_indx
# DESCRIPTION : Compares indexes of 2 databases and produces column-by-column
# report.
#
# DATE INT DESCRIPTION
# ======== === ===============================================================
# 03/03/93 mhl ORIGINAL CODE, V1.0.0
# 03/12/93 mhl Added opts for remote databases
# ======== === ===============================================================
#
###-----------------------------------------------------------------------------
### Set up variables, flags for each input parameter:
### If a dash in front, signifies a remote database
###-----------------------------------------------------------------------------
##
##dash="-"
##dots=":"
##
##d1=`echo ${1#$dash}`
##tst1=$dash$d1
##
##if [ "$tst1" = "$1" ]
##then
## r1_flg=1
## TMPDB1=$d1
## DB1=`echo ${TMPDB1##*$dots}`
## PTH1=`echo ${TMPDB1%%$dots*}`
##else
## r1_flg=0
## TMPDB1=$1
## DB1=$1
## PTH1=`uname -n`
##fi
##
##d2=`echo ${2#$dash}`
##tst2=$dash$d2
##
##if [ "$tst2" = "$2" ]
##then
## r2_flg=1
## TMPDB2=$d2
## DB2=`echo ${TMPDB2##*$dots}`
## PTH2=`echo ${TMPDB2%%$dots*}`
##else
## r2_flg=0
## TMPDB2=$2
## DB2=$2
## PTH2=`uname -n`
##fi
if [[ "$#" -lt 1 || "$1" = "-" ]]
then
echo "\\n Usage: $0 [ -i INFORMIXDIR1 ] [ -h HOSTNAME1 ] [ -o ONCONFIG1 ] -d DBNAME1"
echo "[ -I INFORMIXDIR2 ] [ -H HOSTNAME2 ] [ -O ONCONFIG2 ] -D DBNAME2"
exit 1
fi
#------------------------------------------------------------------------------
# Get the table name (if entered) and database
#------------------------------------------------------------------------------
DB_NAME1=""
DB_NAME2=""
ONCONF1=`echo $ONCONFIG`
ONCONF2=`echo $ONCONFIG`
#HOST1=`hostname`
#HOST2=`hostname`
r1_flg=0
r2_flg=0
INFXDIR1=`echo $INFORMIXDIR`
INFXDIR2=`echo $INFORMIXDIR`
findsoc1 ()
{
ONCONF=$1
HOST=$2
echo "HOST = $HOST"
remsh $HOST -n grep DBSERVERALIASES $INFXDIR1/etc/$ONCONF|awk '{print $2}' \\
> soctmp1
}
findsoc2 ()
{
ONCONF=$1
HOST=$2
echo "HOST = $HOST"
remsh $HOST -n grep DBSERVERALIASES $INFXDIR2/etc/$ONCONF|awk '{print $2}' \\
> soctmp2
}
while getopts i:h:o:d:I:H:O:D: option
do
case "$option"
in
i) INFXDIR1=$OPTARG
;;
h) HOST1=$OPTARG
;;
o) ONCONF1=$OPTARG
findsoc1 $ONCONF1 $HOST1
SOC1=`cat soctmp1`
rm -f soctmp1
;;
d) DB_NAME1=$OPTARG
;;
I) INFXDIR2=$OPTARG
;;
H) HOST2=$OPTARG
;;
O) ONCONF2=$OPTARG
findsoc2 $ONCONF2 $HOST2
SOC2=`cat soctmp2`
rm -f soctmp2
;;
D) DB_NAME2=$OPTARG
;;
\\?) echo "\\n Usage: $0 [ -i INFORMIXDIR1 ] [ -h HOSTNAME1 ] [ -o ONCONFIG1 ] -d DBNAME1"
echo " [ -I INFORMIXDIR2 ] [ -H HOSTNAME2 ] [ -O ONCONFIG2 ] -D DBNAME2"
;;
esac
done
#------------------------------------------------------------------------------
# Check for 2 database names
#------------------------------------------------------------------------------
if [[ "$DB_NAME1" = "" || "$DB_NAME2" = "" ]]
then
echo "\\n usage: $0 [ -o ONCONFIG1 ] -d DBNAME1 [ -O ONCONFIG2 ] -D DBNAME2"
exit 1
fi
DB1=$DB_NAME1
wrk=`echo $ONCONF1 | awk -F\\. '{ print $2 }'`
if [[ $wrk != "" ]]
then
DB1=$DB_NAME1":"$wrk
fi
DB2=$DB_NAME2
wrk=`echo $ONCONF2 | awk -F\\. '{ print $2 }'`
if [[ $wrk != "" ]]
then
DB2=$DB_NAME2":"$wrk
fi
DBDELIMITER=","; export DBDELIMITER
OUT_FIL="indx."$DB_NAME1"."$DB_NAME2
echo "\\t\\tINDEX COMPARISON OF $DB1 TO $DB2\\n" > $OUT_FIL
date >> $OUT_FIL
#-----------------------------------------------------------------------------
# Get the indexes from database1
#-----------------------------------------------------------------------------
#DBPATH=//$PTH1; export DBPATH
echo "Reading $DB1 database index info..."
echo "Reading $DB1 database index info" >> $OUT_