Command substitution in the same shell, db2 reports no connection

Question

I have the following snippet in a bash script:

db2 connect to $DB
var=$(db2 -x "$query" | tr -d " ")

$query holds a select count query, -x just prints out the result of the command with no headers so var would be assigned to a number.

What happens is that $(...) is executed in a subshell and doesn't inherit the connection to DB2 resulting in the "SQL1024N A database connection does not exist. SQLSTATE=08003" message.

I don't know how does the db2 connect command affects the environment.

Answer 1

Without knowing the specifics here: the first command 'db2 connect to $DB' probably finishes, and made a connection. As soon as it finished however it also closed it.

It sounds like you want a construct rather like this:

var=$(db2 -x "connect to $DB;$query" | tr -d " ")

IOW: run db2 command and let him first execute 'connect to $DB' (the shell will have replaced $DB for you) and then '$query' (again the shell did the replacement.)

I'm not sure which separator db2 wants, so ';' was a guess.

Answer 2

If you can do all your variable processing in a subshell you can do something like this:

db2 connect to $DB
db2 -x "$query" | tr -d " " | {
    read var
    do-stuff with var
}

However, you'll have to use temporary files or named pipes to get variable values back to the parent.

Answer 3

One hint: Set the DB2DBDFT environment variable to the name of the database you want to use: it will enable the DB2 CLP to make an implicit connection (if you are running on the database server).

$ export DB2DBDFT=sample
$ db2 "select count(*) from empphoto"  # <-- no "db2 connect to sample" needed.

This also helps to make subshells work.

#!/bin/ksh

export DB2DBDFT=sample
count=$(db2 -x "select count(*) from empphoto" | awk '{print $1}')
print "Count is ${count}"

Good luck!