Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Coding for GMT, sqlplus and related parameters

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Coding for GMT, sqlplus and related parameters
# 1  
Old 06-10-2011
Tools Coding for GMT, sqlplus and related parameters
Unix gurus,

I'm brand new here so go easy on me.

I'm trying to automate the following script to be added to crontab which will automate the deployment of five sql scripts based on day of week. I've got three questions pertaining to the following:

1. Our server date+time is running on GMT; how do I get perl to reflect day of week for GMT-4?
2. I'm not sure if my sqlplus parameters are correct. The hardcoded values passed DO need to have a single quotation mark included when being passed. Is the way I've coded it, correct?
3. Lastly, I'm calling sqlplus each time and passing user/passwd/sid for each script being called. Since each script should be running sequentially after each other, should I remove sqlplus for all subsequent calls? What is the best way to code this?




Code:
 
#!/bin/ksh
export USAGE="USAGE: `basename $0` -e <DBUSER> <DBPASSWD> <TNSNAME>"
if [ $# -lt 3 ]; then
  echo ${USAGE}
  exit 1;
fi
SCRIPTHOME=`home/users/pzx8pak`
TMS_USER=$1
TMS_PWD=$2
TMS_DATABASE=$3
LL="${TMS_USER}/${TMS_PWD}@${TMS_DATABASE}"
dow()  
{
 perl -e '
  use POSIX qw(strftime);
  $fmt = "%A";  # %a = abbreviated weekday %u = weekday number 
  $mday = substr("$ARGV[0]", 8, 2);
  $mon =  substr("$ARGV[0]", 5 ,2);
  $year = substr("$ARGV[0]", 0 ,4);
  $weekday =
    strftime($fmt, 0, 0, 0, $mday , $mon - 1, $year - 1900, -1, -1, -1);
  print "$weekday";
  ' "$1"
}
day = "$(dow `date "+%Y-%m-%d"` )"
 
if [[ ${day} = "Monday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
elif [[ ${day} = "Tuesday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
elif [[ ${day} = "Wednesday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2007'" "'01-JAN'" "'31-MAR'" 
elif [[ ${day} = "Thursday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"   
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2008'" "'01-JAN'" "'31-MAR'"  
 
elif [[ ${day} = "Friday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"   
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2009'" "'01-JAN'" "'31-MAR'"   
elif [[ ${day} = "Saturday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"   
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"  
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2010'" "'01-JAN'" "'31-MAR'"  
 
fi 
 
echo ""
echo ""
echo ""
echo "Deployments Complete"

# 2  
Old 06-10-2011
Use TZ variable to set the time zone. You did not specify your OS, so a solaris example
Code:
TZ=US/Atlantic

Lose the perl, use
Code:
dow=`date +%A`

BE CAREFUL when you use relative paths (./) in cron.
crontab example
Code:
#monday
0 1 * * 1  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2005  > /path/to/logfile 2&>1 
#tuesday                                              
0 1 * * 2  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2006  > /path/to/logfile 2&>1 
                                                      
#wednesday                                            
0 1 * * 3  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2007  > /path/to/logfile 2&>1 
                                                      
#thursday                                             
0 1 * * 4  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2008  > /path/to/logfile 2&>1 
                                                      
#friday                                               
0 1 * * 5  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2009  > /path/to/logfile 2&>1 
                                                      
#saturday                                             
0 1 * * 6  . path/to/.profile && /path/to/myscript.sh -e user pwd db 2010  > /path/to/logfile 2&>1

Then shorten your code sorta like this:
Code:
#!/bin/ksh

year()
{
   printf "'%s'" $1
}
export USAGE="USAGE: `basename $0` -e <DBUSER> <DBPASSWD> <TNSNAME>"
if [ $# -lt 3 ]; then
  echo ${USAGE}
  exit 1;
fi
SCRIPTHOME=`home/users/pzx8pak`
TMS_USER=$1
TMS_PWD=$2
TMS_DATABASE=$3
LL="${TMS_USER}/${TMS_PWD}@${TMS_DATABASE}"
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./SCR_MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "$(year $4)" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql    "$(year $4)" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql             "$(year $4)" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql     "$(year $4)" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql        "$(year $4)" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql        "$(year $4)" "'01-JAN'" "'31-MAR'"

This User Gave Thanks to jim mcnamara For This Post:
WhoDatWhoDer
# 3  
Old 06-10-2011
Sometimes, you can impose a different time zone on the session, else subtract 4 hours from time using oracle time arithmetic.

Quoted quotes seems overkill, but if it works, great! I prefer to generate the input stream to one sqlplus command, with all the commands, "echo '... everything' | sqlplus ...." to save on connect fork exec overhead, and if there is any transactioning, you need to stay in one session. DB2 has a shadow process holding one session, so you can use this ugly, counterintuitive pragma, but as I recall every sqlplus is a new session. If you echo double quoted big string, you can use single on strings, just watch out for shell meta. I prefer to echo in single quotes, where little happens, and use '"'"' for each literal single quote. I am not sure if it is cheaper than <<, but << is very open to meta-shell expansion. Throw in lots of line feeds for beautification, and indentation if necessary. Do 'select scriptname from dual' or something before each script.

Use a wrapper script name in cron. Immediately wrap everything in "() >>log_file 2>&1" so everything is logged outside cron, and use date to put out time stamped run header+trailer messages.
Code:
date "+
========================================
%Y-%m-%d %H:%M:%S Start and End $$ ${0##*/} $* Be nice enough to say Fatal Error or Successful Finish
========================================"

Last edited by DGPickett; 06-10-2011 at 11:59 AM..
This User Gave Thanks to DGPickett For This Post:
WhoDatWhoDer
# 4  
Old 06-10-2011
Thanks for the quick response
My OS is HP UX.

---------- Post updated at 11:21 AM ---------- Previous update was at 11:09 AM ----------

Gurus,

I sincerly apologize. I think I did not explain myself properly. Your recommendations are top notch.

I will be creating four of these scripts: dates for Q1, Q2, Q3, and Q4. Each script will be called once per day to convert data for the specified year based on which day of week it is. Monday we'll be converting for year 2005, Tuesday for 2006, etc. The year will not be passed in from cron. Actually no parameters will be passed from cron.
You have answered most of my question based on your responses anyway. I'm still a little confused as to how to code DOW to consider EDT offset.
Jim said to lose the Perl, ok. Then how does that get applied with the offset.

Thanks in advance for your help.
# 5  
Old 06-10-2011
Quote:
Originally Posted by WhoDatWhoDer
My OS is HP UX.

---------- Post updated at 11:21 AM ---------- Previous update was at 11:09 AM ----------

Gurus,

I sincerly apologize. I think I did not explain myself properly. Your recommendations are top notch.

I will be creating four of these scripts: dates for Q1, Q2, Q3, and Q4. Each script will be called once per day to convert data for the specified year based on which day of week it is. Monday we'll be converting for year 2005, Tuesday for 2006, etc. The year will not be passed in from cron. Actually no parameters will be passed from cron.
You have answered most of my question based on your responses anyway. I'm still a little confused as to how to code DOW to consider EDT offset.
Jim said to lose the Perl, ok. Then how does that get applied with the offset.

Thanks in advance for your help.
Why do you need 4 scripts? Create just one script and have it figure out what day of the it's running on and make determination based on that what year to run the 'conversion' for - should be pretty easy/basic to implement.
This User Gave Thanks to vgersh99 For This Post:
WhoDatWhoDer
# 6  
Old 06-10-2011
VGersh,

Thanks for replying. The whole story is that we'll be executing each of the four scripts at different points during the day. We'll include each quarterly script as an entry in the cron.

The reason we're doing this is because our replication environment is spewing a boatload of alerts due to the volume of updates, not to mention, the load backing up.

Any additional help with the GMT-4 issue would be great.

Thanks.
# 7  
Old 06-10-2011
Big operations especially transactions are a drag on any system. They lack locality of reference, run at disk speed not RAM and CACHE speeds. Do an hour, day, week at a time and wait between batches as long as the batch took for the interactive users, and let it chug. You could even collect all the rowids first, and then do 1024 rowids at a time. Think about it: you get 99+% of economy of scale doing 101 rows! Everything past that may be tiny or even a slowdown. If you make it a stored procedure or anonymous block, and let it run inside the engine, compile/plan time is eliminated. Then, there is locking . . . !
Last edited by DGPickett; 06-10-2011 at 03:44 PM..
This User Gave Thanks to DGPickett For This Post:
WhoDatWhoDer
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

2. Shell Programming and Scripting

Shell Script passing parameters to sqlplus code

Hello All, I am interested in finding out a way to pass parameters that are entered at the prompt from HP unix and passed to SQLPlus code with a Shell Script. Is this possible? Thanks (4 Replies)
Discussion started by: compprog11
4 Replies

3. Shell Programming and Scripting

need downloading related help...but its not related to unix

Hi All, I am trying to dowmload the zip file "zkManageCustomers.zip " but i dont have access. Can anyone help me to download this file See the below link- http://www.ibm.com/developerworks/opensource/library/wa-aj-open/index.html?ca=drs- Please help me as early as... (1 Reply)
Discussion started by: aish11
1 Replies

4. Shell Programming and Scripting

Can't get shell parameters to pass properly to sqlplus

Gurus, The issue I'm having is that my Shell won't accept SQL parameters properly...... Here's they way I'm running it.... applmgr@ga006hds => sh CW_MigrationDeployScript.sh apps <appspwd> <SID> '01-JAN' '31-MAR' The process just hangs not submitting the SQL job... ... (3 Replies)
Discussion started by: WhoDatWhoDer
3 Replies

5. Shell Programming and Scripting

GMT to PST

I have a file which is tab delimited, it contains the GMT date. Please tell me how to convert from GMT to PST time and if the date and time is of 2 days ago from the current date store the lines in a file or else remove the line. 10.1.10.178 - - - 10.1.10.178 - - ... (2 Replies)
Discussion started by: sandy1028
2 Replies

6. AIX

tuning network parameters : parameters not persist after reboot

Hello, On Aix 5.2, we changed the parameters tcp_keepinit, tcp_keepintvl and tcp_keepidle with the no command. tunrestore -R is present in inittab in the directory /etc/tunables we can clearly see the inclusion of parameters during reboot, including the file lastboot.log ... (0 Replies)
Discussion started by: dantares
0 Replies

7. Shell Programming and Scripting

Help me about gmt scripts

does somebody know GMT mapping? please reply (2 Replies)
Discussion started by: SANGUINE
2 Replies

8. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies

9. Shell Programming and Scripting

How to pass Shell variables to sqlplus use them as parameters

Hi, I am trying to pass some of the variables in my shell scripts to the sqlplus call and use them as parameters. For example, I would like to replace the 'SAS', and '20050612' with $var1 and $var2, respectively, how can I do that? --------------------------------------------------------... (1 Reply)
Discussion started by: Jtrinh
1 Replies

10. Shell Programming and Scripting

passing parameters from a shell script to sqlplus

Hi , I want to pass parameters from a shell script to a sql script and use the parameter in the sql query ..and then I want to spool a particular select query on to my unix box... for 4 different locations by writing only one sql script Right now no file is generated on the unix box...it is a... (2 Replies)
Discussion started by: phani
2 Replies
Login or Register to Ask a Question