awk script help for seaching reserved words 3

[AlStl]

We are using Sun server and teradata database. There is a need to upgrade teradata version, but lot of data load is done via ksh scripts, which has embedded SQL in it. Well, Teradata has some words that are reserved words in this new version.

Is there a way to write a script (using awk or something like that) that will look for all these reserved words used in embeded SQL in ksh scripts. All these scripts are located at following location /nisc/tto/scripts I would like to pass above location as parameter to the script via command line. For example reserved words are CONNECT or day_of_year and I want to see all the .ksh scripts at above location that have these word in it and at what line.

Any suggestion will be highly appreciated. Here is a sample script that was used to find a wild card in a select statement:

It is execute as belowsamplescript.awk /nisc/tto/scripts

#!/bin/nawk -f

BEGIN {
}

/[S,s]elect|Sel|sel\ / {
FromCnt = 0
WildCnt = 0
LineNum = 1
FirstLine = NR
}

/[S,s]elect|Sel|sel\ /,/;/ {
Line[LineNum++] = $0
if ($0 ~ /[F,f]rom/ && $0 !~/[T,t]rim/) FromCnt++
if ($0 ~ /\.\*/) WildCnt++
}

/;/ {
if (WildCnt > 1) {
LastLine = NR
print "------> WARNING <------"
for(i=1;i<LineNum;i++)
print Line
printf ("%d Wildcards in above Select\n", WildCnt)
printf ("From count = %d\n", FromCnt)
printf ("In file %s between lines %d and %d\n\n", FILENAME, FirstLine, LastLine)
WildCnt = 0
}
}









Al

 

[SamBones]

It's not AWK, but what about this?

find /nisc/tto/scripts -name '*.ksh' -print | xargs grep -n CONNECT

That gives, filename, the line number it's on, and the line, separated by colons.

If you want to search for more than one key word at the same time, this would do.

find /nisc/tto/scripts -name '*.ksh' -print | xargs egrep -n 'CONNECT|day_of_year'

 

[SamBones]

If you're worried about missing one because of upper case vs lower case...

find /nisc/tto/scripts -name '*.ksh' -print | xargs egrep -i -n 'CONNECT|day_of_year|Select'

 

[LKBrwnDBA]

If you need to scan for a lot of reserved words, try this:
a) Create file with the reserved words:

cat - <<! >>~/scripts/reserved_words.txt
ABORT
ABORTSESSION
ABS
ACCESS_LOCK
ACCOUNT
ACOS
ACOSH
ADD
ADD_MONTHS
. . .   E t c   . . .
WIDTH_BUCKET
WITH
WITHOUT
WORK
XMLPLAN
YEAR
ZEROIFNULL
ZONE
!

b) Use awk to scan the scripts:

#!/bin/ksh
# scan_words.sh
# Parameter: 1- Scripts directory

SCRIPTDIR=$1
# Location of the reserved words file:
RSVWDS=~/scripts/reserved_words.txt

cd $SCRIPTDIR

awk -v words=$RSVWDS '
BEGIN{n=0; while ((getline line < words ) > 0){ n=n+1; rw[n]=line;} close(words);}
{l0=toupper($0); w=0;
 for(i in rw){if(index(l0,rw[i])>0) w=w+1;}
 if(w>0){print FILENAME": "FNR" : "$0}
}' *.*sh

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[AlStl]

Thanks all for your input. I appreciate it. Question for LKBrwnDBA.

I created scan_words.txt and added some words in it and here is my code, but i am getting an error

#!/bin/ksh
# scan_words.ksh
# Parameter: 1- Scripts directory

SCRIPTDIR=$1
# Location of the reserved words file:
RSVWDS=/nto/scripts/reserved_words.txt

cd $SCRIPTDIR

awk -v words=$RSVWDS
'BEGIN{n=0; while ((getline line < words ) > 0){ n=n+1; rw[n]=line;} close(words);}
{l0=toupper($0); w=0;
for(i in rw){if(index(l0,rw)>0) w=w+1;}
if(w>0){print FILENAME": "FNR" : "$0}
}' *.*ksh


scan_words.ksh /nto/scripts/

Error:

awk: no program given
scan.ksh[17]: BEGIN{n=0; while ((getline line < words ) > 0){ n=n+1; rw[n]=line;} close(words);}^J{l0=toupper($0); w=0;^J for(i in rw){if(index(l0,rw)>0) w=w+1;}^J if(w>0){print FILENAME": "FNR" : "$0}^J}: not found


Here is the location of awk and nawk
which awk
/bin/awk
which nawk
/bin/nawk

What am i doing wrong here?

 

[PHV]

What am i doing wrong here?
You really don't see that the starting single quote isn't in the right line ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlStl]

oh crap. Thanks PHV.

 

[AlStl]

I am curious...can I restrict searches to exact word for example right now its picking up DIRECTCONNECT due to CONNECT being in reserved_words.txt

So, if I put CONNECT then I only want results with exact word CONNECT and nothing else.

Thx

 

[AlStl]

Also, for some reason its just searching the first word in reserved_words.txt and stop after that. So, right now my reserved_words.txt looks like this:

CONNECT
day_of_week
WARNING

result only show CONNECT or DIRECTCONNECT etc. I know for sure rest of the words are used in .ksh file under that directory. What is missing?

Al

 

[LKBrwnDBA]

Try this:

. . . E t c . . .
awk -v words=$RSVWDS '
BEGIN{n=0; while ((getline line < words ) > 0){ n=n+1; rw[n]=line;} close(words);}
{for(k=1;k<=NF;k++){
   l0=toupper($k); w=0;
   for(i in rw){if(l0==rw[i]) w=w+1;}
   }
 if(w>0){print FILENAME": "FNR" : "$0}
}' *.*sh

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[AlStl]

LKBrwnDBA,

Your previous code was returning results for first word in reserved_words.txt. I replaced it with new code you recommended and it does not yield anything...something is not right here? I am 100% sure some scripts has words that I put in reserved_words.txt file.

Al

 

[LKBrwnDBA]

1) The reserved words in your file must be UPPER case.
2) This works for me:

#!/bin/ksh
# scan_words.sh
#
SCRIPTDIR=/opt/oracle/scripts/$1
RSVWDS=/opt/oracle/scripts/reserved_words.txt

cd $SCRIPTDIR
awk -F'[ ()+-;]' -v words=$RSVWDS '
BEGIN{n=0; while ((getline line < words ) > 0){ n=n+1; rw[n]=line;} close(words);}
{for(k=1;k<=NF;k++){
   l0=toupper($k); w=0;
   for(i in rw){if(length(l0)>0&&l0==rw[i]) w=w+1;}}
 if(w>0){print FILENAME": "FNR" : "$0}
}' *.*sh
exit

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[AlStl]

LKBrwnDBA,

thanks a lot. Its very much appreciated.

Is there a way to handle upper case and lower case words in reserved_words.txt?

Al

 

[LKBrwnDBA]

Is there a way to handle upper case and lower case words in reserved_words.txt?

Yes, but only if you are sure the word you are trying to scan is written the same always.
If this is the case, just remove the "toupper()" in the "l0=toupper($k); w=0;" statement and that will force case equality comparison (words must be exactly the same case combination).
Or add "toupper()" in this statement: "{ n=n+1; rw[n]=toupper(line);}" (but not both changes).



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb