capture db2 output in shell script

[Alan Simpson]

Greetings!

I have to convert a shell script from Oracle to db2. My Oracle script looks something like this

#!/bin/bash
OUT=`$ORACLE_HOME/bin/sqlplus -s user/pass@instance   << EOF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
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 UR ";
#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??

[Alan Simpson]

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.

[vijay2082]

Hi Alan,

Just save the coutout of command in a varaible and use it from within the script. In DB2 UDB you are using multiple commands to get the result, like connecting and then selecting.

Redirect to /dev/null just send the undesired output to bin.

Cheers, Vijay