Unix Script exiting when select query returns no rows

Question

Below is the part of my script. The "set -e" will make the script exit whenever a negative return code comes from any of the commands.

But, when the below select statement returns no rows from the table, the script exits there itself (echo "Get Eg names end" is not executed). Which means the below command is giving negative return code.

db2 -x "SELECT EG_NAME FROM MS.CFG_CACHE_REFRESH WHERE 
EG_RELOAD_UPD_BK2_TS < M_TABLE_UPD_TS AND CURRENT_TIME >= EG_RELOAD_START_TIME AND
CURRENT_TIME <= EG_RELOAD_END_TIME" 
> /home/DummyUser/gm4/logs/MP_CACHE_REFRESH_TEMP_BK2.txt

If the select statement returns some rows, it works fine. The script doesn't exit and runs till the end.

My requirement is to exit if a genuine error occurs, like unable to connect database, invalid syntax etc. If no rows are returned from the table, it should not be considered as an error.

Why am I getting a -ve return code for the select query not returning rows and how can I handle this?

Below is the part of the script:

#!/bin/ksh
set -e
brokername=$1

if [ "$#" -ne  "1" ]
  then
echo "Invalid arugments supplied"
echo "call the script using command:"
echo "MP_CACHE_REFRESH_BRK2.ksh <BrokerName>"

exit -1
 fi

touch /home/DummyUser/gm4/logs/MP_CACHE_REFRESH_TEMP_BK2.txt
chmod 777 /home/DummyUser/gm4/logs/MP_CACHE_REFRESH_TEMP_BK2.txt
db2 CONNECT TO MSAPP USER DummyUser using paasss 
db2 -x "SELECT EG_NAME FROM MS.CFG_CACHE_REFRESH WHERE EG_RELOAD_UPD_BK2_TS < M_TABLE_UPD_TS AND CURRENT_TIME >= EG_RELOAD_START_TIME AND CURRENT_TIME <= EG_RELOAD_END_TIME" > /home/DummyUser/gm4/logs/MP_CACHE_REFRESH_TEMP_BK2.txt
echo "Get Eg names end"

I think it's best not to use `set -e` but to explicitely check for the exit code ($?) of the commands you're interested in — Ingo Leonhardt, Sep 04 '14 at 16:44
@IngoLeonhardt Thank you for the response. Actually above is very small part of the complete script and checking for return codes after each of the commands we think can throw error will actually be more time consuming. I was looking for a way to do it with set -e. — nitgeek, Sep 04 '14 at 17:03
I really can't imagine that (usually built-in) test command can have any important perfomance issues compared to database access. Furthermore, explicitely checking for exit codes leaves you the freedom to print error messages etc. — Ingo Leonhardt, Sep 04 '14 at 17:15
By "will be time consuming" I was talking about development time not about script's performance. I just wanted to achieve without many changes in the script. — nitgeek, Sep 04 '14 at 17:41

Lennart - Slava Ukraini · Accepted Answer · 2014-09-05T17:14:37.160

The problem is not negative return codes, it is any return code that is != 0. DB2 exits with:

- 0, success
- 1, no row found
- 2, warning (for example using existing index instead of creating a new one)
- 4, error (for example object not found)
- 8, system error (os related)

Unless you wrap db2 and return 0 for $? -lt 4 I don't see how you are going to success. Example on how to deal with db2 exit codes (-e removed)

db2 -x "SELECT EG_NAME FROM MS.CFG_CACHE_REF ..."
rc=$?
if [ $rc -ge 4 ]; then
    ...

EDIT: Alternative with sql stmts in separate file

Keeping all the sql in a separate file - say myfile.sql - you can do something like from your sh:

db2 -l myfile.log +c -s -tf myfile.sql
rc=$?
if [ $rc -ge 4 ]; then
    echo "Error"
    db2 rollback
    exit 1
elif [ $rc -ge 1 ]; then
    echo "Warning"
fi

db2 commit
exit 0

-s terminates execution on error ( -ge 4 ). You can find out what caused the problem by tailing the log file, tail -10 myfile.log. Beware that certain operations such as reorg will commit the ongoing transaction.

Thanks. So, "set -e" makes the script exit in case of any non zero RC. Are you aware if there is any alternative that works for -ve RC only? If not then probably I have no other option but to handle RC for 100s of select statements in my script :-( — nitgeek, Sep 05 '14 at 15:29

Unix Script exiting when select query returns no rows

1 Answers1