Hi guys ..since u all are reeally trying to help me sort out the errors...i think i should better explain you the requirements and what I have done.i think this would make all of us think on similar grounds.
so,there are 4 parts of this script.
1.i have to read a logfile which looks like:
Quote:
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SA
About to export SA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SA's tables via Conventional Path ...
. . exporting table ACCOUNT_MISSING_FRM_RCIS_LINK 4 rows exported
. . exporting table ADP_COMMENT 2311 rows exported
. . exporting table ADP_CONFIG 11 rows exported
. . exporting table ADP_FIELD 36323 rows exported
. . exporting table ADP_HEADER 1 rows exported
|
Now the first script reads this logfile and gives output file which has only the table name and the row count.The script looks like:
Quote:
logcountfunction()
{
awk ' {
# when executing the script pass the logfile as parameter on the command prompt with the name of the file
#export file_name= "&1"
if (index ( $0, ". . exporting table") >0 || index ($0, ". . exporting partition") >0)
#searching for the pattern in string
{
if ($4 != "partition"){
#$4 is either table or a partition
i=$6;
#$6 is the number of rows stored in variable i here
if (table_flag ==0 && table_name ==temp_table_name){
# checking the flag
printf table_name > "logcountOP";
printf "-" > "logcountOP";
print j > "logcountOP";
}
table_name = $5;
#$5 has the name of the table from which the rows have been imported
temp_table_name = table_name;
table_flag =1;
#setting the flag to 1
}
if ($4 == "partition") {
i=i+$6;
#summing up the rows in partitioned tables
printf("value of i in first: %d\n",i);
j=i;
table_flag=0;
#setting flag to 0
}
if (table_flag !=0 && $6 !=""){
printf table_name >> "logcountOP";
printf "-" >> "logcountOP";
print i >> "logcountOP";
}
}
} ' < $1
}
|
ignore the partition part please
NOW THE SECOND PART:
I TAKE THE LOG OUTPUT FILE AND CUT THE TABLENAME INTO A VARIABLE.I QUERY THE DATABASE FOR THE ROW COUNT OF EACH TABLE.THE SCRIPT FOR THIS LOOKS LIKE:
Quote:
DBcounttry_finalfunction()
{
#!/bin/ksh
cat logcountOP | while read LINE
TBLName=`echo $LINE|cut -d "-" -f1`
do
if [ $LINE != "" ]
then
printf "${TBLName}-" $TBLName
return_count=$(sqlplus -s ab/ab@avfd5 <<EOF
set heading off feedback off pagesize 0 linesize 30000 trimout on;
whenever sqlerror exit 1;
whenever oserror exit 1;
select count (*) from ${TBLName};
exit 0;
EOF)
if [ $return_count -ge 0 ]
then
print "${TBLName}-${return_count}" >> DBcountOP4
else
echo "$TBLName- is missing" >> DBcountOP4
fi
else
#exit
break
fi
done > DBcountOP3
}
|
NOW THE THIRD PART.
I AM READY WITH 2 OUTPUT FILES-
LOG OUTPUT FILE AND DATABASE OUTPUT FILE.NOW I HAVE TO COMPARE THESE TWO OUTPUT FILES TO FIND IF:
1.THE COUNT MATCHES OR
2.THE COUNT DOES NOT MATCH OR
3.THE TABLE IN THE LOGFILE DOES NOT EXIST IN THE DATABASE
THE SCRIPT FOR ABOVE COMPARISON IS:
Quote:
#!/bin/ksh
compareLOGandDBtry1function()
{
export file1="DBcountOP4"
export file2="logcountOP"
#match=0
while read FILE1_LINE ; do
#reLOGandDBtry1reading the output file from database and the first string is stored in the variable LINE
file1_tablename="$(echo $FILE1_LINE | cut -d '-' -f1)"
#the 1st field is stored i.e,tablename
file1_count="$(echo $FILE1_LINE | cut -d '-' -f2)"
#the 2nd filed is stored i.e,the count of rows for the table
if [ $file1_count != "is" ]
then
#echo "File count is greater than 0"
while read FILE2_LINE ; do
#reading the OP file from log and stores the 1st string
file2_tablename="$(echo $FILE2_LINE | cut -d '-' -f1)"
#the 1st field is stored i.e,tablename
file2_count="$(echo $FILE2_LINE | cut -d '- ' -f2)"
#the 2nd fild stored-the count of rows for the table
if [ "$file1_tablename" = "$file2_tablename" ] && [ "$file1_count" -eq "$file2_count" ]
#start of 1st if block
#if the tablename and the rowcount from the 2 files match then print the following to OP file
then
echo "Table name $file1_tablename $file2_tablename and $file2_count matched"
echo "Both table and Count has matched $file1_tablename $file2_count" >> compareLOGandDBtry1OP
break
fi #end of 1st if block
if [ "$file1_tablename" = "$file2_tablename" ] && [ "$file1_count" -ne "$file2_count" ]
#start of 2nd if
#checking for match b/w tables but a mismatch b/w the rowcount from logfile and the DB output file
then
echo "$file2_tablename table Match count mismatch" "$file1_count" "$file2_count"
echo "table has matched but count does not match $file1_tablename $file1_count $file2_count" >>compareLOGandDBtry1OP
break
fi
# end of 2nd if block
done < $file2
else
echo "$file1_tablename does not exist" >> compareLOGandDBtry1OP
echo "$file1_tablename does not exist"
fi
done < $file1
#
}
|
AS U CAN SEE ALL THE ABOVE ARE FUNCTIONS.SO,THE MAIN SCRIPT IS AS BELOW WHICH CALLS THE ABOVE FUNCTIONS:
Quote:
#MAIN SCRIPT STARTS HERE
#!/bin/sh
echo "please exit and execute the script again alongwith the logfile name"
. ./logcount
logcountfunction "$1"
. ./DBcounttry_final
DBcounttry_finalfunction
. ./compareLOGandDBtry1
compareLOGandDBtry1function
|
THIS SCRIPT IS WORKING FINE.THE FIRST 2 FUNCTIONS EXECUTE PROPERLY BUT THE MOMENT 3RD FUNCTION,i.e,COMPARING FUNCTION,IT IS TAKING ALMOST AN HOUR TO GIVE THE OUTPUT!!!THAT IS WHERE THE PROBLEM IS.
SO TO EXPEDITE THE SCRIPT I USED COMM COMMAND FOR A COMPARE.
Quote:
#!/bin/sh
comm -3 logcountOP DBcountOP4 >c.txt
#shows diff
comm -1 logcountOP DBcountOP4 >d.txt
#shows common
~
|
and then i wrote a script that gives the following output:
Quote:
ACCOUNT_MISSING_FRM_RCIS_LINK- is #means missing
ACCOUNT_MISSING_FRM_RCIS_LINK-4
ADP_COMMENT- 2384
ADP_COMMENT-2311
ADP_CONFIG- 11
ADP_CONFIG-11
ADP_FIELD- 36333
ADP_FIELD-36323
|
i wrote another script that outputs the below:
ACCOUNT_MISSING_FRM_RCIS_LINK- is ACCOUNT_MISSING_FRM_RCIS_LINK-4
#is means the table is missing in database
ADP_COMMENT- 2384 ADP_COMMENT-2311
ADP_CONFIG- 11 ADP_CONFIG-11
ADP_FIELD- 36333 ADP_FIELD-36323
ADP_HEADER- 1 ADP_HEADER-1
here the problem is that some tables get repeated n number of times in the output file.
now the last script is:
Quote:
#!/bin/sh
export file="newcOP2.txt"
while read FILE_LINE ; do
tablename1="$(echo $FILE_LINE | cut -d '-' -f1)"
echo tablename1 is $tablename1
count1="$(echo $FILE_LINE | cut -d ' ' -f2)"
echo count1 is $count1
LINE2="$(echo $FILE_LINE | cut -d ' ' -f3)"
tablename2="$(echo $LINE2 | cut -d '-' -f1)"
count2="$(echo $LINE2 | cut -d '-' -f2)"
echo tablename2 is $tablename2
echo count2 is $count2
if [ $count1 = "is" ]
then
echo missing
echo status for $tablename1- does not exist in DB >> statusOP
elif [ $count1 -eq $count2 ]
then
echo match
echo status for $tablename2- match >> statusOP
elif [ $count1 -ne $count2 ]
then
echo mismatch
echo status for $tablename2- mismatch >> statusOP
fi
done < $file
|
finally i am getting the output but the problem is that in the course of reaching the output i have made a mess!!!please help me simplify the script.If you could help me with a script that simply compares the log output file and the database output file.thankyou!!!
