Parallel SQL sessions in shell script

Shell Programming and Scripting
1

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

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

how can i execute the sql3 after completion f sql1 & sql2

2

You can put a wait command after sql 1 & sql 2

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

assuming what you posted works, which I am not all sure is true:

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

(
sqlplus username1/password1@DB1 <<EOF  >> log1 
   @sql1
   exit
EOF
) &

It is called a here document.

4

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

Yes, wait command will hold until both sql1 & sql2 are completed, then only sql3 will be called.

6

thanks, if i want to know the execution time of sql1 and sql2 and it should display at terminal.

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

That while loop is not gonna work. You can grab the time before and after calling SQL1 & SQL2:-

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.

8

thanks