Assign SQL result to shell variable

Question

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?

mao · Accepted Answer · 2019-11-13 14:53:56Z

1

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" )

answered Nov 13, 2019 at 14:53

mao

12.4k2 gold badges15 silver badges32 bronze badges

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

Comments

Mark Barinstein · Accepted Answer · 2019-11-13 15:20:10Z

0

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.

answered Nov 13, 2019 at 15:20

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Assign SQL result to shell variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related