Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: capture db2 output in shell script

    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??
    Last edited by kristo5747; 10-03-11 at 20:24.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Lose the semicolons, for starters.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    xx=`db2 -x "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 WITH UR "`
    echo $xx
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18
    Quote Originally Posted by przytula_guy View Post
    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??
    Last edited by kristo5747; 10-04-11 at 13:14.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •