Shell Programming and Scripting

I need to process 50 sqlplus scripts which are listed in a text file. I need to develop a shell script that'll read this file and run these sqlplus scripts. At any point of time, the number of sqlplus scripts running shouldn't exceed 6. If any of the sqlplus scripts completes successfully then another one needs to kick off, till all scripts are processed.

How would I go about doing this please?

My idea is to have 2 loops. One looping through the sql list and another one checking the number sql sessions opened at any time.

Say, the first loop kicks off the first 6 sqls. Now the sqls read and running are the same : 6. After some time, 2 sqls get done. Now the sqls read is 6 but the sqls running is 4. So the first loop should read the next two sqls, 7 & 8, and execute them.

However, I am not able to put together a shell script for this. Any help would be much appreciated!

What's your system? What's your shell?

If you have bash or ksh, you can do something like:

Code:

#!/bin/bash

PROCS=5       ;       WAIT=0  ;       END=0

# Wait for next thread.
function waitnext
{       # Needs BASH/KSH
        wait "${PIDS[$(( (WAIT++) % PROCS))]}"
}

while read LINE
do
        [ "$((END-WAIT))" -ge "$PROCS" ] && waitnext
        stuff "$LINE" &
        PIDS[$(( (END++) % PROCS ))]=$!
done < textfile
# wait for ALL remaining processes
wait

I don't know of a way to make a shell wait for any particular single process though. Either you give it the PID and it waits for that exact process, or you do a general 'wait' and wait for ALL of them. So this has to wait for them in order.

Meaning if one program takes 10x longer than the rest it might end up waiting for that long one before it launches another 5 jobs.

Further to Corona688's last word. See the Shell "jobs" command. You can use this in most Bourne-type Shells to determine how many background jobs are running.

Please state what Shell you are using.

Wow, that was a super fast response! thank you ...

System is SunOS 5.10 Generic_144488-06, shell is ksh.

Yeah, I tried with "wait" before but I found that since "wait" waits for the successful completion of jobs, if a script takes too long, then it will block the next batch of scripts from getting executed. That's not what I am looking for.

Btw, what does "stuff "$LINE" &" mean?

It is pseudocode to describe your process which runs a sqlplus program in your environment. We cannot guess what it is called or what it contains.
The "&" of course backgrounds the command.


My turn for a Btw.
A sequence of commands written in sqlplus is called a program.
A sequence of commands written in unix Shell is called a script.
There is no such thing as a sqlplus script.

Using jobs to count background jobs running:

Code:

#!/bin/ksh
PROCS=6
SLEEP=20
while read LINE
do
     while true
     do
         NUM=$(jobs | wc -l)
         echo NUM=$NUM
         if [ $NUM -lt $PROCS ]
         then
             stuff "$LINE" &
             break
         else
             sleep $SLEEP
         fi
    done
done < textfile
# wait for ALL remaining processes
wait

methyl, you are absolutely wrong about this: "A sequence of commands written in sqlplus is called a program."

There were always sql scripts and always will be. A set of sql commands run through sql*plus is called by us Oracle DBAs as sqlplus script (or sql script for short).

But that's besides the point. Do you have any sample code to post like Corona688 did?