capture db2 output in shell script

kristo5747 · #1 (**permalink**) 10-03-11, 19:09

Greetings!

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

Code:

#!/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). I found on this forum a sample of script I used ; my db2 script looks like this

Code:

#!/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??

n_i · #2 (**permalink**) 10-03-11, 20:44

Lose the semicolons, for starters.

przytula_guy · #3 (**permalink**) 10-04-11, 02:10

xx=`db2 -x "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR "`
echo $xx

kristo5747 · #4 (**permalink**) 10-04-11, 11:59

Quote:

Originally Posted by przytula_guy

xx=`db2 -x "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR "`
echo $xx

It does not work. Here's my code

Code:

#!/bin/bash
...............
db2bin="/users/db2inst1/sqllib/bin"
$db2bin/db2 connect to CTSS_VW;
out=`$db2bin/db2 -x "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR "`
echo $out
$db2bin/db2 quit;

and this is the error I got

Code:

$>. test.sh

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.1.4
 SQL authorization ID   = CTSS_VW
 Local database alias   = CTSS_VW

SQL1024N A database connection does not exist. SQLSTATE=08003
DB20000I  The QUIT command completed successfully.

To get around this error, I put all my code in a script like so

Code:

CONNECT TO CTSS_VW;SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR;QUIT;

and executed this

Code:

xx=`$db2bin/db2 -tf test.sql`; echo $xx

It outputs the result

Code:

Database Connection Information Database server = DB2/LINUXX8664 9.1.4 SQL authorization ID = CTSS_VW Local database alias = CTSS_VW ---------- 10/04/2011 1 record(s) selected. DB20000I The QUIT command completed successfully.

but I would to clean it up which defeats the purpose ; is there a better way??

ARWinner · #5 (**permalink**) 10-04-11, 13:12

Your problem is the $ in front of the DB2 commands. Each one is a separate shell so the connect and select statements are executing separately.

Andy

kristo5747 · #6 (**permalink**) 10-04-11, 14:22

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

Code:

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

Hope this helps.

Internet Services

Web Development

Digital Marketing

Consumer Tech