Capture db2 sql results in shell script

Question

I have a shell script which will connect to database and get the results. My script is like

#!/bin/bash
getResults()
{
    db2 "connect to ${1} user ${2} using ${3}"
    db2 "set schema ${4}"
    status=`db2 -x "select status from results where id=1"`
    echo $status
}
#MAIN STARS HERE
getResults dbname foo bar test

Now I wanted to get multiple columns from results tables using

select status,timestamp from results where id=1

How can I run the above query and capture both status and timestamp into two different shell variables using single query instead of running 2 differnt queries like

#!/bin/bash
getResults()
{
    db2 "connect to ${1} user ${2} using ${3}"
    db2 "set schema ${4}"
    status=`db2 -x "select status from results where id=1"`
    echo $status
     timestamp=`db2 -x "select timestamp from results where id=1"`
    echo $timestamp

}
#MAIN STARS HERE
getResults dbname foo bar test

My results table is like:

create table (id number, status char(1), timestamp datetime);

And data is like

1 P <some ts>
2 F <some ts>

Thanks in advance!

Where is the table? If it's in the database, you should be able to run it as a regular insert/create statement... Can't help with the shell script, sorry. — Clockwork-Muse
– Clockwork-Muse, Commented Jan 17, 2012 at 22:07
my table is in database and I wanted to retrive data from that table using above query from my shell script. I know how run sql's from db2 cnsole. — springpress
– springpress, Commented Jan 17, 2012 at 23:06

Ian Bjorhovde · Accepted Answer · 2012-01-18 02:31:33Z

2

The problem is that the database connection you make in the getResults function is not visible to the subshell (i.e. when you call db2 -x). Using backticks invokes a new shell.

To make this work you need to keep your query in the same shell:

db2 "connect to ${1} user ${2} using ${3}"
db2 "set schema ${4}"

db2 -x "select status,timestamp from results where id = 1" | while read status timestamp ; do
    echo $status
    echo $timestamp
done

Note that with the while loop here you will output multiple lines if your query returns more than 1 row. It's easy to modify the SQL to return only 1 row.

answered Jan 18, 2012 at 2:31

Ian Bjorhovde

11.1k1 gold badge30 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

springpress Over a year ago

Looks like its gonna solve my problem. Let me test this tomorrow and get back to you. Thanks for your answer.

Collectives™ on Stack Overflow

Capture db2 sql results in shell script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related