Script help

Shell Programming and Scripting
1

Hi
I want compare my prodution tables with dev tables and list tables if any data length or data type mismatches.I put all ddl statements with 2 different files (one from prod and one from dev).Below script is not working.Any help should be appereciated

tab1=$1
tab2=$1
 
if [ ! -f $tab1 ] ; then
echo "file1 not exist:$tab1"
fi

if [ ! -f $tab2 ] ; then
echo "file2 not exist:$tab2"
fi

col1=`awk -F" " '/SET TABLE/{print substr($4,(index($4,".")+1))}1'  $tab1 
|egrep -iv 'create|journal|default|index'|awk -F " " '{print $1,$2 }'|awk NF`
 
col2=`awk -F" " '/SET TABLE/{print substr($4,(index($4,".")+1))}1'  $tab2 
|egrep -iv 'create|journal|default|index'|awk -F " " '{print $1,$2 }'|awk NF`
 
tabname=`awk -F" " '/SET TABLE/{print substr($4,(index($4,".")+1))}'`

if [ "$col1 == "$col2" ] ; then 
echo "Tablename "$tabname  is ok
else 
echo "Tablename "$tabname columns  not matches
fi

###

file1.txt

CREATE  TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER NOT NULL,
Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);



file2.txt

CREATE  TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id char(10) NOT NULL,
Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);

Thanks
MR

2

Is this a typo?

tab1=$1
tab2=$1

Filename is missing here:

tabname=`awk -F" " '/SET TABLE/{print substr($4,(index($4,".")+1))}' filename`

Regards

3

Hi
sorry its typo error and missed the filename .


tab1=$1
tab2=$2

tabname=`awk -F" " '/SET TABLE/{print substr($4,(index($4,".")+1))}'  $tab1`

Thanks,
MR

4

Hi
Its working but its not giving the status of first table even if difference .

a.sh file1.txt file2.txt

xx1
xx2 is ok

example


data length of Cd its different from xx1 in both databases ,its not 

file1.txt

CREATE  TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER NOT NULL,
Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);
-----------------------------

CREATE  TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER NOT NULL,
Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);


file2.txt

CREATE  TABLE XX1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER NOT NULL,
Cd CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);
-----------------------------

CREATE  TABLE XX2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Id INTEGER NOT NULL,
Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Category_Cd VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
XX_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
XX_Dt1 DATE FORMAT 'YYYY-MM-DD'
);

Thanks,
MR

5

ummmm did not read all this so i could be off,,, but to save much work just do this.

sdiff file1.txt file2.txt

OR
diff file1.txt file2.txt

OR

sdiff -s -w 150 file1.txt file2.txt

then

man sdiff

did a light go on? or should i have raed this thread to see what it was really about

6

Your files don't have any line with "SET TABLE" so your awk command doesn't give any result.
In your script you're grepping for words in lowercase, but remind that grep is case sensitive.

Regards