How to create parallel connections and queries to db in bash script

Question

I have an oracle db on my Linux machine. A single sql query (1 connection) via bash is as follows:

su - oracle
sqlplus <dbuser>/<dbpass>
select * from cat;
exit

I'm trying to run parallel queries via bash, the following script is for running 10000 connections in parallel (Correct me if i'm wrong):

for i in $(seq 1 10000); do echo "select * from <tableName>;" | sqlplus <dbuser>/<dbpass>&done

I would like to make this code more robust and flexible, for the sake of example i want to add a sleep between each of the following command:

Create a connection
Create a table (Unique to this connection, i as index for example)
Select data from the table
Close the connection

The following code is my attempt of doing so: (Not working)

for i in $(seq 1 10000); 
do 
   echo "CREATE TABLE test+i (id NUMBER NOT NULL);" 
   sleep 2
   echo "select * from test+i"
   sleep 2 
   echo "DROP TABLE test+i" | sqlplus <dbuser>/<dbpass>& 
done

1) Syntactically, how should i write it?

2) How can i know how many queries/connections succeeded and how many failed?

3) How can i know how many connections actually ran in parallel

torbatamas · Accepted Answer · 2019-11-19 07:42:17Z

1

1) you can use ( and ) to group command into subshells, and send them background:

for i in $(seq 1 10000); 
do
   echo "CREATE TABLE test_$i (id NUMBER NOT NULL);
   !sleep 2
   select * from test_$i;
   !sleep 2
   DROP TABLE test_$i;" | sqlplus <dbuser>/<dbpass> &
done

2) you can set up error handling after each sqlplus call (examine output or exit value)

     echo "CREATE TABLE test_$i (id NUMBER NOT NULL);"  | sqlplus <dbuser>/<dbpass> 2>&1 | grep -i error

3) you can use the jobs command to examine how many job is running in the background:

> sleep 100 &
[1] 31642
> jobs
[1]+  Running                 sleep 100 &

edited Nov 19, 2019 at 7:42

answered Nov 19, 2019 at 7:21

torbatamas

1,3461 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

totothegreat Over a year ago

Those commands you wrote in 1, is doing the following: do action and disconnect, do another action and disconnect, do another and disconnect, i want the 3 actions to happen via 1 connection, connect -> do 3 queries -> disconnect

torbatamas Over a year ago

I modified the post, you can call out to shell from sqlplus using !

Ole Tange · Accepted Answer · 2019-12-06 23:02:44Z

1

10000 jobs in parallel will often cause overflow. By setting 'WHENEVER SQLERROR EXIT SQL.SQLCODE' sqlplus will return an error, if the SQL fails. GNU Parallel can then re-run the query.

my.log will show if the query failed after rerunning 3 times.

doit() {
   i=$1
   (echo "WHENEVER SQLERROR EXIT SQL.SQLCODE CREATE TABLE test$i (id NUMBER NOT NULL);"
    sleep 2
    echo "WHENEVER SQLERROR EXIT SQL.SQLCODE select * from test$i;"
    sleep 2
    echo "WHENEVER SQLERROR EXIT SQL.SQLCODE DROP TABLE test$i;") |
     sqlplus <dbuser>/<dbpass>
}
export -f doit

seq 1 10000 | parallel --joblog my.log -j0 --retries 3 doit

edited Dec 6, 2019 at 23:02

answered Dec 6, 2019 at 22:54

Ole Tange

34.1k9 gold badges93 silver badges111 bronze badges

Collectives™ on Stack Overflow

How to create parallel connections and queries to db in bash script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related