db2 query in shell script doesn't run with empty results in shell

Question

I have below script in shell. test.sh

#! /bin/bash

connect_stat=$(db2 -x "connect to $DB_NAME USER $DB_USER using $DB_PASSWORD" )
db2 "SET SCHEMA=SCHEMA1"

while read line; 
do
a=$(db2 -x "SELECT C.id FROM table C WHERE C.col1  IN ('$line)') with ur")
echo $a
done<inputs.txt 

I get empty results when I run "sh test.sh"

Where as when I run the same above query in db2 through putty, I get results.

I have below dbcon file. Will trigger the dbcon file in putty and results are available

/admin/.profile

db2 connect to DB_NAME USER DB_USER using DB_PASSWORD

db2 set SCHEMA=SCHEMA1

db2


db2 => SELECT C.id FROM table C WHERE C.col1  IN ('xyz-asd-asd') with ur

ID
----------------------------------------------------------------
123

  1 record(s) selected.

Please help me what I am doing wrong in shell script.

Thanks in advance.

Answer 1

$(db2 -x "connect to $DB_NAME...) executes in a subshell, which terminates the connection when the subshell exits, so by the time you get to db2 "SELECT C.PARTNUMBER..." the connection does not exist.