Parallel SQL sessions in shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parallel SQL sessions in shell script
# 1  
Old 10-30-2012
Parallel SQL sessions in shell script

i have 3 sqls , sql 1 and sql 2 shuld run in parallel , but sql 3 should run after completion f sql1 nd sql2, my code is as below, please suggest the changes

Code:
 
sqlplus username1/password1@DB1   @sql >> log1 &
sqlplus username2/password2@DB2  @sql2 >> log1 &

how can i execute the sql3 after completion f sql1 & sql2
# 2  
Old 10-30-2012
You can put a wait command after sql 1 & sql 2

Code:
sqlplus username1/password1@DB1  @sql >> log1 &
sqlplus username2/password2@DB2  @sql2 >> log1 &
wait
sqlplus username3/password3@DB3  @sql3 >> log3 &

# 3  
Old 10-30-2012
assuming what you posted works, which I am not all sure is true:
Code:
sqlplus username1/password1@DB1   @sql >> log1 &
sqlplus username2/password2@DB2  @sql2 >> log1 &
wait
sqlplus username1/password1@DB1   @sql2 >> log1 &

PS:
each sqlplus line should look like this
Code:
(
sqlplus username1/password1@DB1 <<EOF  >> log1 
   @sql1
   exit
EOF
) &

It is called a here document.
# 4  
Old 10-31-2012
Hi bipinagith............if sql2 is completed and sql1 is still running, will the wait command still waits for sql1 to complete before it kicks off sql3
# 5  
Old 10-31-2012
Yes, wait command will hold until both sql1 & sql2 are completed, then only sql3 will be called.
# 6  
Old 10-31-2012
thanks, if i want to know the execution time of sql1 and sql2 and it should display at terminal.

Code:
sqlplus username1/password1@DB1   @sql >> log1 &
sqlplus username2/password2@DB2  @sql2 >> log1 &
while ($? -eq 0)
do
let time=0
echo " sql still running  from $time min........"
sleep 60
let time=$time+1
done
wait
sqlplus username1/password1@DB3   @sql3 >> log3 &

please do the corrections
# 7  
Old 10-31-2012
That while loop is not gonna work. You can grab the time before and after calling SQL1 & SQL2:-

Code:
echo "Timestamp: `date`"
sqlplus username1/password1@DB1  @sql1 >> log1 &
sqlplus username2/password2@DB2  @sql2 >> log1 &
wait
echo "Timestamp: `date`"

And then get the difference for knowing execution time.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Executing SQL's in parallel

Hi Folks, I have requirement to pull a bunch of SQL's from a table in DB and execute them in parallel and update the status of each query as and when they complete. Can you please help me with ideas on how this can be achieved? create table list_of_sql ( id number, full_sql... (3 Replies)
Discussion started by: member2014
3 Replies

2. Shell Programming and Scripting

Parallel for in a Script Shell

Hello, This is my Script which configure a list of nodes. ( 40 nodes, and the configuration 10 minutes) #! /bin/sh if then echo "USE: ./start.sh nodes else nb_lignes=`wc -l $1 | cut -d " " -f1` echo "$nb_lignes machines" for i in $(seq $nb_lignes) //instructions done fi My... (2 Replies)
Discussion started by: chercheur111
2 Replies

3. Shell Programming and Scripting

Running 3 shell script parallel in another shell script

Hi, I'm trying to do teh below thing. I have a single script which uses 3 different parameters to do 3 different work like belwo. test1.sh par1 -- it shuts down an instance test1.sh par2 -- it shuts down an instance test1.sh par3 -- it shuts down an instance Now I created a script... (7 Replies)
Discussion started by: bhaski2012
7 Replies

4. Shell Programming and Scripting

Parallel processing of SQL through Shell

Hi Friends, I am trying to write a shell which will invoke 3 CTAS (ORACLE create table XXX as select * from YYYY). The approximate time for one CTAS is around 25 mins. So i want to run the CTAS script parallely. My pseudocode is as below. Main script nohup sh CTAS1.sh &... (3 Replies)
Discussion started by: Showdown
3 Replies

5. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

6. UNIX for Advanced & Expert Users

Call parallel sql scripts from shell and return status when both sql are done

Hi Experts: I have a shell script that's kicked off by cron. Inside this shell script, I need to kick off two or more oracle sql scripts to process different groups of tables. And when both sql scripts are done, I will continue in the shell script to do other things like checking processing... (3 Replies)
Discussion started by: huasheng8
3 Replies

7. Shell Programming and Scripting

Help with executing parallel sessions for same shell script with different but fixed parameters

Hi Experts, There is a shell script that accepts positional parameter between 1-25 to execute case statement of script depending upon the parameter passed. Now I need to run all the 25 sessions parallely. In each option of case statement it is connecting with sqlplus and executing a select... (11 Replies)
Discussion started by: Opamps123
11 Replies

8. Shell Programming and Scripting

Running shell script in parallel

Hi, I am a shell script which takes input parameters and performs various functions. My concern is, if we call the same shell script with different parameter values from different sessions, will the results be inconsistent? Are there any precautions I need to take inorder to avoid conflicts... (1 Reply)
Discussion started by: jamjam10k
1 Replies

9. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

Hi- I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment... I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Discussion started by: rajagavini
5 Replies

10. Shell Programming and Scripting

Problems executing SQL Plus sessions

If I execute this script: $ORACLE_HOME/bin/sqlplus -s <<EOF > oracle.log $DB_id/$DB_pswd@$DB_server start test.sql EOF the sql script executes with no errors. IF I execute the following script:... (12 Replies)
Discussion started by: mh53j_fe
12 Replies
Login or Register to Ask a Question

Featured Tech Videos