DB2 + shell script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bikkaran@in.ibm.com - 14 Mar 2006 10:46 GMT

Hi .

I have to write a Shell script like ...
IF "select value from tablnema where condition;" = some_value
then
depending upon the this value I will call some other script.sql

This query will always return 1 value.

Can some one help me how to implement this in Shell script.

regards
bikash

Reply to this Message

Michel Esber - 14 Mar 2006 14:29 GMT

Try something like this ...

#!/bin/bash
. /home/db2inst1/sqllib/db2profile

db2 connect to <DB>

var=`db2 -x "select value from tablnema where condition"`
if [ $var -eq 0 ]
then
something
else
something
fi

> Hi .
>
[quoted text clipped - 9 lines]
> regards
> bikash

Reply to this Message

Eugene F - 14 Mar 2006 14:59 GMT

A regular OS shell has no facility to communicate with database except
for the return code from DB2 CLP that only flags success of its
execution (0) or failure (not 0). For your task perl would be a better
choice.

-Eugene

Reply to this Message

Dave Hughes - 14 Mar 2006 16:05 GMT

> A regular OS shell has no facility to communicate with database except
> for the return code from DB2 CLP that only flags success of its
> execution (0) or failure (not 0). For your task perl would be a better
> choice.
>
> -Eugene

Or Python or Ruby :-)

(though, as I understand it, Perl is the only one with an "official"
feature-replete driver from IBM; Ruby and Python basically have "bare
essentials" community efforts which, frankly, need quite a bit more
work)

One other quick point: if you do go the shell script route, the failure
exit codes from the CLP aren't quite standard:

0=success
1=a fetch returned no rows (not an error)
2=statement warning (not an error)
4=statement error
8=CLP error

Check the Reference / Commands / DB2 Universal Database / Command Line
Processor (CLP) / CLP return codes section in the Info Center for more
information. So, you'll want to do something like:

#!/bin/bash

db2 -x CONNECT TO $db >/dev/null
if [[ $? -ge 4 ]]; then
echo "Failed to connect to $db"
exit 1
fi
value=$(db2 -x SELECT somefield FROM sometable)
if [[ $? -ge 4 ]]; then
echo "Failed to run query"
exit 1
fi

HTH,

Dave.

Reply to this Message

bikkaran@in.ibm.com - 20 Mar 2006 13:04 GMT

hi ,

I want to use the value i.e. output as give by the SQL query "select
value from tablnema where condition" .

The value as above "var" will be either 0 for successful execution of
the query .......... It wont have the value returned by the query . I
want to use the out put of the query ..the result the database gave.

Thanks :)
bikash

Reply to this Message

Brian Tkatch - 20 Mar 2006 15:11 GMT

Name the file to be executed values.sql.

Then use 'eval' to execute the output of a db2 -x (clean output).

For example, i have a script that SELECTs other CALLs from TABLE.

#!/bin/bash

db2 +o "CONNECT TO [alias] USER [user] USING [password]"
db2 +o "SET SCHEMA [schema]"
db2 +o "SET PATH [schema], CURRENT PATH"
eval "$(db2 -x "SELECT 'db2 +o \"CALL Test_Timings_Call(' || CHAR(Id)

|| ')\" && db2 +o \"UPDATE Timings_Log SET Stop = CURRENT TIMESTAMP

WHERE Stop IS NULL\"' FROM Timings_Call")"
db2 +o "CONNECT RESET"
db2 +o "TERMINATE"

It may be confusing because i am using db2 to call db2. In your case,
perhaps it may be:

#!/bin/bash

db2 +o "CONNECT TO [alias] USER [user] USING [password]"
db2 +o "SET SCHEMA [schema]"
db2 +o "SET PATH [schema], CURRENT PATH"
eval "$(db2 -x "SELECT './' || value || '.sql' WHERE condition")"
db2 +o "CONNECT RESET"
db2 +o "TERMINATE"

B.

Reply to this Message

Anurag - 03 Apr 2006 11:11 GMT

Hey Bikash,
This one is very simple.

Sample script (and very basic) goes like this:
set stmt="select value from tablnema where condition"
db2 -x "$stmt" | read output_result

if [ $output_result -eq 1254 ]
then
..........
..........
end

This method would be troublesome if db2 -x "$stmt" returns an error.
How I handle the error situation depends on whether the expected result
is numeric or not.
If I am expecting a numeric output I just check if "$output_result" is
numeric.
If expected result is VARCHAR, I redirect the output of db2 $stmt (note
the missing "-x") to a file and check for success execution of the db2
statement and then parse the output result.

If your problem still persists, post your actual code here and I can
finetune it.

HTH..........Anurag

Reply to this Message