CODEWITHSUNDEEP
linuxbashshelldb2
Abhiram Varma
Abhiram Varma

Reputation: 105

Assign SQL result to shell variable

I am trying to assign the result of the db2 command to a variable inside a shell script:

backup_end_time="$(db2 select END_TIME FROM SYSIBMADM.DB_HISTORY where operation = "'B'" AND OBJECTTYPE = "'D'" ORDER BY START_TIME DESC FETCH FIRST 1 ROWS ONLY | awk 'NR==4')"

echo $backup_end_time

When I echo I am getting a blank value. I am expecting value 20191113030053.

If I try the same command outside the script without assigning to variable

db2 select END_TIME FROM SYSIBMADM.DB_HISTORY where operation = "'B'" AND OBJECTTYPE = "'D'" ORDER BY START_TIME DESC FETCH FIRST 1 ROWS ONLY | awk 'NR==4'

I get the expected output.

How can I assign the output to a variable?

Upvotes: 0

Views: 230

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12299

Bash opens sub-shell too often.
When you call v=$(db2 ...), then a new sub-shell is opened, and db2 front-end process doesn't have a connection to a db2 background process (db2bp) opened for your current shell. 

#db2 "select 1 from sysibm.sysdummy1" | awk 'NR==4' | read v

tmpf=f.txt
db2 "select 1 from sysibm.sysdummy1" | awk 'NR==4' > ${tmpf}
read -r v < ${tmpf}
rm -f ${tmpf}
echo "Value: ${v}"

Commented out line works with ksh, but not with bash.

Upvotes: 0

mao
mao

Reputation: 12267

You need to get the quotes correct and also use the -x option to the CLP to avoid needing to awk the output.

Example:

backup_end_time=$(db2 -x "select END_TIME FROM SYSIBMADM.DB_HISTORY where operation = 'B' AND OBJECTTYPE = 'D' ORDER BY START_TIME DESC FETCH FIRST 1 ROWS ONLY" )

Upvotes: 1

Related Questions