Solved

unix shell with a parameter

Posted on 2013-05-30
22
496 Views
Last Modified: 2013-06-04
Here is my shell below. I am trying to basically fetch .csv file using ftp to get .csv into a directory. Name of the file could be possibly like
missingtrades_20130530.csv  or
missingtrades_20130531.csv
Can I pass these .csv files as parameters since the file name is varying, how do I run the shell passing the parameters. Please help, this is kind of urgent

#!/bin/ksh -xve


date

HOME_DIR=$tradehome/ods
#DOWNLOAD_FILE_NAME=test_0523.csv
DOWNLOAD_FILE_NAME=$1.csv
DATA_FILE_DIR=$HOME_DIR/data/csv
DATA_FILE=$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
dscfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.dsc
badfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.bad
#ftplog = $HOME_DIR/log/ftp_get_msg_data.log



remote=cnyopdsuat
USER_NAME=opdu
PASSWORD=snp500

export USER_NAME PASSWORD remote ftplog DATA_FILE_NAME

until
[ -f "$DATA_FILE" ]
do
        sleep 2
        date
        echo 'Attempt to get $DATA_FILE from $remote' `date`
        cd $DATA_FILE_DIR
        echo "open $remote
        user $USER_NAME $PASSWORD
        verbose
        asc
        cd pranathi
        get $DOWNLOAD_FILE_NAME
quit"  | ftp -in > $ftplog 2>&1
done



date

Open in new window

0
Comment
Question by:d27m11y
  • 12
  • 9
22 Comments
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 84 total points
ID: 39208009
You already have this:

DOWNLOAD_FILE_NAME=$1.csv

$1 is first parameter to your script, $2, $3 next etc.

You could call your script like this:

scriptname.ksh <first parameter>

or:

scriptname.ksh missingtrades_20130530

What did you try already?
0
 

Author Comment

by:d27m11y
ID: 39208223
Great, thank you for your quick response. My bad, I was running the script as

scriptname.ksh  missingtrades_20130530.csv  as parameter instead of
scriptname.ksh missingtrades_20130530

It works now..

Thank you for your help.

Now, I changed my sh as follows

#!/bin/ksh -xve


date

HOME_DIR=$tradehome/ods
DOWNLOAD_FILE_NAME=$1.csv
DOWNLOAD_FILE_NAME1=sample.csv
DATA_FILE_DIR=$HOME_DIR/data/csv
DATA_FILE=$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
dscfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.dsc
badfname=$HOME_DIR/log/stg_citco_intraday_trade_sqlldr.bad
#ftplog = $HOME_DIR/log/ftp_get_msg_data.log



remote=cnyopdsuat
USER_NAME=opdu
PASSWORD=snp500

export USER_NAME PASSWORD remote ftplog DATA_FILE_NAME

until
[ -f "$DATA_FILE" ]
do
        sleep 2
        date
        echo 'Attempt to get $DATA_FILE from $remote' `date`
        cd $DATA_FILE_DIR
        echo "open $remote
        user $USER_NAME $PASSWORD
        verbose
        asc
        cd pranathi
        get $DOWNLOAD_FILE_NAME
        
quit"  | ftp -in > $ftplog 2>&1
done

echo   'Started sqlloader stg_citco_intraday_trade'


if ( test -e "$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME" )  then
   
   
   dos2unix $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
   mv $DOWNLOAD_FILE_NAME $DOWNLOAD_FILE_NAME1
   echo $odspass | sqlldr $odsuser control=$tradehome/ods/ctl/stg_citco_intraday_trade.ctl  log=$tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log  bad=$badfname  discard=$dscfname
   cat $tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log

fi

echo  'Ended sqlloader stg_citco_intraday_trade'

date

Open in new window


Name of my shell script - stg_citco_intraday_trade_v1.sh
File I wanted to pass on as parameter - Citco_missingtrades_20130523
Am trying to rename the Citco_missingtrades_20130523 as sample.csv

I tried to execute  as

stg_citco_intraday_trade_v1.sh Citco_missingtrades_20130523

and it works.

Now I have another issue, I included sqlloader as part of my shell and it is loading into the table STG_CITCO_INTRADAY_TRADE. The very first column of this table has the sequence number.

Whenever data gets loaded into this table STG_CITCO_INTRADAY_TRADE, sequence number column should be 2013052300001, 2013052300002 and so on..

If the file that is being as passed as parameter is Citco_missingtrades_20130530, then the
sequence number column should be 2013053000001, 2013053000002 and so on..

Can you suggest how to do that. I am attaching my control file
stg-citco-intraday-trade-ctl.txt
0
 

Author Comment

by:d27m11y
ID: 39208225
This is kind of urgent issue, kindly respond.
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 416 total points
ID: 39209043
OK, here:
1) Execute these commands:
cd $tradehome/ods/ctl
cp stg_citco_intraday_trade.ctl stg_citco_intraday_trade_ctl.tmp

Open in new window

2) Edit the stg_citco_intraday_trade_ctl.tmp file as follows:
load data
infile '$tradehome/ods/data/csv/sample.csv'
replace into table stg_citco_intraday_trade   
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
   (
  STG_SEQUENCE_NUMBER        SEQUENCE(%SEQNO%),
  ORD_STATUS                 "TRIM (:ORD_STATUS)",   
. . .   E t c   . . .

Open in new window

3) Add these statements to your script:
#!/bin/ksh -xve
date
HOME_DIR=$tradehome/ods
DOWNLOAD_FILE_NAME=$1.csv

STG_SEQUENCE_NUMBER="${1##*_}00001"

DOWNLOAD_FILE_NAME1=sample.csv

. . .   E t c   . . .

echo   'Started sqlloader stg_citco_intraday_trade'

if ( ! test -e "$DATA_FILE_DIR/$DOWNLOAD_FILE_NAME" )  then
   echo  '!Error: Missing download file -- Ended sqlloader stg_citco_intraday_trade'   
   date
   exit 1
fi

sed "s/%SEQNO%/$STG_SEQUENCE_NUMBER/" $tradehome/ods/ctl/stg_citco_intraday_trade_ctl.tmp > $tradehome/ods/ctl/stg_citco_intraday_trade.ctl

dos2unix $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME $DATA_FILE_DIR/$DOWNLOAD_FILE_NAME
mv $DOWNLOAD_FILE_NAME $DOWNLOAD_FILE_NAME1
echo $odspass | sqlldr $odsuser control=$tradehome/ods/ctl/stg_citco_intraday_trade.ctl  log=$tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log  bad=$badfname  discard=$dscfname
cat $tradehome/ods/log/stg_citco_intraday_trade_sqlldr.log

echo  'Ended sqlloader stg_citco_intraday_trade'

date

Open in new window

:p
0
 

Author Comment

by:d27m11y
ID: 39209147
Wonderful, thank you for your help.  It works perfect

I tried to execute  as

stg_citco_intraday_trade_v1.sh Citco_missingtrades_20130530



One last question, what if I wanted to execute as
stg_citco_intraday_trade_v1.sh 20130530, I mean to say if the parameter I wanted to pass is only the date and the file could be

Citco_missingtrades_20130530.csv
Citco_missingtrades_20130523.csv

Will there be change in shell and ctl file. Am attaching the control file and shell with your changes. Can you suggest the changes in these two if only date is passed as parameter.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2sh.txt
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 416 total points
ID: 39209157
Change only the shell script:
. . .   E t c   . . .
DOWNLOAD_FILE_NAME="Citco_missingtrades_$1.csv"
STG_SEQUENCE_NUMBER="${1}00001"
. . .   E t c   . . .

Open in new window

;)
0
 

Author Comment

by:d27m11y
ID: 39209196
Thank you for your help., simply wonderful.

I see that records inserted into table in control file- stg_citco_intraday_trade gets inserted as per sequence of the inputfile like

Eg, if input file is Citco_missingtrades_20130530

2013053000001
2013053000002
2013053000003 etc.,

and if input file is Citco_missingtrades_20130523

2013052300001
2013052300002
2013052300003 etc.,

What if I wanted to be in the continous sequence like

2013053000001
2013053000002
2013053000003
2013052300004
2013052300005
2013052300006

Please suggest.
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 416 total points
ID: 39209289
You need to create database sequence:
CREATE SEQUENCE stg_sequence
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1 NOCYCLE NOCACHE NOORDER;

Open in new window

change the control file:
. . .   E t c   . . .
trailing nullcols
   (
  STG_SEQUENCE_NUMBER        EXPRESSION "%SEQNO% + stg_sequence.nextval",
  ORD_STATUS                 "TRIM (:ORD_STATUS)",   
. . .   E t c   . . .

Open in new window

and the script (You may need more "zeroes" if you want continuous sequence):
STG_SEQUENCE_NUMBER="${1}0000000000"

Open in new window

0
 

Author Comment

by:d27m11y
ID: 39210802
I tried to make changes as suggested. Attached are the control, shell files but I get strange results. I also attached the .csv file which I used for execution.

here is my execution script
stg_citco_intraday_trade_v2.sh 20130523

Open in new window


Here is the sequence number I get for all the records

2.0130523E17

I did attach the result in the table and you can see the sequence number. I defined the datatype for stg_sequence number in the table initially as number, now I changed it to Number(38).. still, it gives me the same result.

Please suggest.
stg-citco-intraday-trade-ctl.txt
stg-citco-intraday-trade-v2-sh.txt
CITCO-MISSING-TRADES-20130523.csv
Resultset-001.xls
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39211293
Unnecessary to change to Number(38)...execute this in sql*plus:
SET NUM 23

Open in new window


In Excel select the column, then Format -> Cells -> Number tab -> Select number -> Set decimal places to 0 (zero) -> Click OK.

SET NUM 23
It seems that you loose precision with excel.
0
 

Author Comment

by:d27m11y
ID: 39211534
since I am executing thru shell script, I am unable to determine where to set NUM 23.

In excel, I tried setting the datatype to number and precision to '0' and it did not work.

Kindly suggest.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39216822
You set the number to  23 digits in the utility you use to generate/display your report ( SQL*Plus?). The values are correct in the database.

And as also I mentioned, excel seems to only handle 18 significant digits.

Why not add a "load" date column to the table and thus you need 8 characters less for the "STG_SEQUENCE_NUMBER"?
0
 

Author Comment

by:d27m11y
ID: 39217065
Thank you for your reply. I am still struggling to get the right sequence number. As I said before, I am loading the table stg_citco_intraday_trade thru sqlloader. Can I set number to 23 digits in my control file. Attached is my control file.

or is there anyway that I can change my shell to set the number to 23.

Please suggest.
stg-citco-intraday-trade-ctl.txt
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 416 total points
ID: 39217155
Not in control file, the seq is loading fine into the db.
The issue is with the program you use to display/report that number.
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 416 total points
ID: 39217164
Or as I said, reduce the number of digits here:
STG_SEQUENCE_NUMBER="${1}0000000000"
#------------------------^^^^^^^^^^

Open in new window

0
 

Author Comment

by:d27m11y
ID: 39217179
when I change the number in my shell script, it works fine.

STG_SEQUENCE_NUMBER="${1}00000"
0
 

Author Comment

by:d27m11y
ID: 39217181
However, thank you so much for your wonderful help!
0
 

Author Closing Comment

by:d27m11y
ID: 39217189
Excellent and very beneficial, useful for unix beginners who use sqlloader as part of their shell script.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39217273
Be aware that "${1}00000" will only give you 99,999 sequences before it will add +1 to the date.
;)
0
 

Author Comment

by:d27m11y
ID: 39219052
that is my only concern. If the sequence exceeds "${1}00000" i.e 201305300000 --2013053099999.. what could be my next step to prevent this ?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39219373
Add a "load" date column to the table and thus you remove 8 characters from the "STG_SEQUENCE_NUMBER".
 ALTER TABLE stg_citco_intraday_trade ADD (load_date DATE);

Open in new window

Change the shell script:
. . .   E t c   . . .
DOWNLOAD_FILE_NAME="Citco_missingtrades_$1.csv"
LOAD_DATE="${1}"
. . .   E t c   . . .
sed "s/%LOAD_DATE%/$LOAD_DATE/" $tradehome/ods/ctl/stg_citco_intraday_trade_ctl.tmp > $tradehome/ods/ctl/stg_citco_intraday_trade.ctl
. . .   E t c   . . .

Open in new window

change the control file:
. . .   E t c   . . .
trailing nullcols
   (
  STG_SEQUENCE_NUMBER        EXPRESSION "stg_sequence.nextval",
  ORD_STATUS                 "TRIM (:ORD_STATUS)",   
. . .   E t c   . . .
  MX_ORIGINAL_INB            "TRIM (:MX_ORIGINAL_INB)",
  TAKE_EXP_FLAG              "TRIM (:TAKE_EXP_FLAG)",
  TRADER_NOTES               "TRIM (:TRADER_NOTES)",
  LOAD_DATE                  EXPRESSION "TO_DATE('%LOAD_DATE%', 'YYYYMMDD')",
)

Open in new window

0
 

Author Comment

by:d27m11y
ID: 39220334
Got it, perfect.. Initially I had a question to add the new column LOAD_DATE in stg_citco_intraday_trade

but now, when I load the records from stg_citco_intraday_trade  to base table, I donot need to select this column LOAD_DATE, that makes things clear.

Thank you so much for your patience and help !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now