CODEWITHSUNDEEP
sqllinuxshelldb2sh
macgyverx86
macgyverx86

Reputation: 51

DB2 Server connection lost when running a select inside a shell script

I'm trying to automate some constraint checks after a huge data import and I encountered the flowing problem. I found a workaround that I will also describe, but if there is someone that has better Linux knowledge than me and can explain why this happens I would really appreciate it.

So if I am running the following commands from DB2 CLI after being logged in as instance owner i get the output value printed, without any errors.

bla:~> VAL=$(db2 -x 'select count(*) from SIM.SUPPLIER')
bla:~> echo value = $VAL
value = 621684

If I save the commands in a select.sh file and call the script in the same process using . select.sh after being logged in as instance owner I get a error message that there is no connection to the database. I think that somehow the command substitution is running in e new thread where the connection to the server is not forwarded.

select.sh content:

VAL=$(db2 -x 'select count(*) from SCHEMA.TABLE')

echo value = $VAL

How I'm running the script:

bla:~> db2 connect to DB

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.0
 SQL authorization ID   = DB2INS10
 Local database alias   = DB

bla:~> . select.sh
value = SQL1024N A database connection does not exist. SQLSTATE=08003
bla:~>

If select.sh contains only the db2 command, without the assignment to VAL the connection isn't lost:

select.sh content:

db2 -x 'select count(*) from SIM.SUPPLIER'

bla:~> . select.sh
     621684
bla:~>

And now the workaround: Writing the SQL select in a file and calling running the file inside select.sh does the trick and connection isn't lost.

select.sh content

echo 'select count(*) from SIM.SUPPLIER;' > sql

db2 -txf sql

bla:~> . select.sh
     621684
bla:~>

But this doesn't work, and I don't understand why:

echo 'select count(*) from SIM.SUPPLIER;' > sql

echo $(db2 -txf sql)

bla:~/> . select.sh
SQL1024N A database connection does not exist. SQLSTATE=08003
bla:~/>

So can someone please explain to me why command substitution is losing the server connection and how can I still use it but keep the server connection.

PS: I am not allowed to connect to the server inside any scripts. No credentials should be written inside files because of security reasons. Server connection must be established before calling other scripts and only once.

Thanks

Upvotes: 1

Views: 1429

Answers (3)

mao
mao

Reputation: 12267

The reason is that both the VAL=$(....) and the echo $(db2 -txf sql) each runs a subshell, and in that subshell there is no database connection. Your workaround does not involve a subshell, so it works.

For bash, If you are prevented from having a connect inside your scripts then you must avoid sub shelling for the Db2 CLP , as you do with your workaround.

You can use temporary files to avoid subshells, at the cost of more parsing etc. For example instead of using VAL=$(db2 ...) use db2 ... > $tmpfile followed by VAL=$(cat $tmpfile) or similar technique.

You cannot 'forward the connection' as such.

If you are able to use ksh93 with coprocesses then you can communicate between processes and ensure all db2 CLP actions happen in one task that then pipes results to another task. But such complexity is rarely worthwhile and it may be preferable to use a different non-shell scripting language.

Upvotes: 1

Mark Barinstein
Mark Barinstein

Reputation: 12314

This depends on shell.
bash opens a sub-shell, which doesn't have a connection to your db2 background process (db2bp), which holds the database connection.

Try ksh. It shouldn't open a sub-shell. If you use a dot space file notation, ksh must be set for your parent session:

$ ksh
$ db2 connect to mydb ...
$ . ./select.sh

Upvotes: 0

data_henrik
data_henrik

Reputation: 17118

See the information about front-end and back-end processes and the examples for the Db2 command. Essentially, the db2 command has the UI (front-end) and a related back-end process (database connection, context, etc.). When you invoke the script and directly execute db2, it is able to connect to the back-end process which holds the database connection.

When you execute the command in parentheses, (db2 ...), a new subprocess (subshell) is spawn. It is a different environment and does not have the information about the back-end process, and hence the Db2 database connection.

Upvotes: 0

Related Questions