capture db2 output in shell script

I have to convert a shell script from Oracle to db2. I found on this forum a sample of Oracle script I used ; it looks like this

#!/bin/bash
OUT=`$ORACLE_HOME/bin/sqlplus -s user/pass@instance   << EOF
select sysdate from dual;
exit success
EOF`
echo $OUT

This will output "03-OCT-11" (Oracle sysdate). My db2 script looks like this

#!/bin/bash
db2bin="/users/db2inst1/sqllib/bin"
#connect
$db2bin/db2 connect to myschema;
#query
$db2bin/db2 "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH U开发者_JS百科R";
#debug
echo $?
#check
if [ $? = "0" ] then         echo "found-do something"
else        echo "not found-good bye"
fi
#terminate
$db2bin/db2 quit;

It works but does not retrieve the date ; only "0" or "1" (true/false). How can I retrieve the date from my Db2 query result??

Chris, your environment variables and your DB2 command path should be set by sourcing db2profile. The quit command is unnecessary when calling the db2 command with either a SQL file or a single statement specified as part of the command-line.

#!/bin/bash
. ~db2inst1/sqllib/db2profile
OUT=`db2 connect to myschema >/dev/null 2>&1; db2 -x values current date`

Your database connection will remain available until the script ends, so you can run successive statements without reconnecting.

#!/bin/bash
. ~db2inst1/sqllib/db2profile
db2 connect to myschema >/dev/null 
OUT=`db2 -x values current date`
AAA=`db2 -x " select a from sometable where b = 'c' " `

I'm not that familiar with db2, but it sounds like you need to redirect the output from the SELECT statement.

e.g., db2 SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR > /tmp/output

More information here: http://www.ibm.com/developerworks/data/library/techarticle/dm-0503melnyk/

Edit: also, does the db2 select line output to stdout? I don't have a copy readily available to test with :/

I figured it out: the trick is to use /dev/null.

#!/bin/bash
DB2INSTANCE=db2inst1
BIN="/users/db2inst1/sqllib/bin"
OUT=`${BIN}/db2 connect to myschema > /dev/null 
${BIN}/db2 -x "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR"
${BIN}/db2 quit > /dev/null
`
echo $OUT

Hope this helps.