CODEWITHSUNDEEP
bashdb2
Chris
Chris

Reputation: 1697

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 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??

Upvotes: 1

Views: 11461

Answers (3)

Fred Sobotka
Fred Sobotka

Reputation: 5332

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' " `

Upvotes: 2

Chris
Chris

Reputation: 1697

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.

Upvotes: 2

Mel Boyce
Mel Boyce

Reputation: 269

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 :/

Upvotes: 2

Related Questions