cancel
Showing results for 
Search instead for 
Did you mean: 

Run sql from a shell script

Chushia
Frequent Advisor

Run sql from a shell script

I have a working script (kitpoll.sh) that runs sql to prepare for a list of recipients for an email as following:
...
...
echo "From: SDC" > /eis/data/poll.msg
echo "Subject: POLLING RESULTS " >>/eis/data/poll.msg

emailaddr=`isql -s sdc -< set lock mode to wait;
Select Unique Lower(recipient), "RCP"
From email_ctrl
Where Upper(rptType) = "POLL"
And Upper(hold) <> "Y"
EOF`

people_cnt=0
for everyone in `echo $emailaddr`
do
echo "To: $everyone" >> /eis/data/poll.msg
people_cnt=`expr $people_cnt + 1`
done

if [ $people_cnt -gt 1 ]
then
cat /eis/data/poll.msg /eis/data/sitestat.txt | /usr/sbin/sendmail -t
fi

...
...

Randomly it failed in getting any recipients (if re-ran, it worked again!). I put the output of shell script to a log and noticed, when it was working, in the log:

Lockmode set.
34 row(s) retrieved.

When it failed, in the log:
kitpoll.sh[48]: /var/tmp/sh2806.1: Cannot find or open the file.

----------------------------------------------
The Database engine is Informix runing on HP-UX 11.11. What can I do to make sure it works perfect?

Thanks,
Chushia
4 REPLIES
Jonathan Fife
Honored Contributor

Re: Run sql from a shell script

I'd check /var/tmp to make sure there is plenty of room and there are inodes free. Your script is trying to create a temp file and failing.
Decay is inherent in all compounded things. Strive on with diligence
Steven E. Protter
Exalted Contributor

Re: Run sql from a shell script

Shalom,

emailaddr=`isql -s sdc -<set lock mode to wait;
Select Unique Lower(recipient), "RCP"
From email_ctrl
Where Upper(rptType) = "POLL"
And Upper(hold) <> "Y"
EOF`

Seems that your sql database connection is not working. You need to check return codes and stop this script when this happens.

The return code from processes is kept in the $? variable.

echo "steve"
echo $?
If I get a zero that means the echo steve statement was successful.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Chushia
Frequent Advisor

Re: Run sql from a shell script

/var is 2G and only 27% used. And, if re-run it worked. In syslog.log there is no error about i-node.

As for connection, I tested that if there is sql error it'd still result in a return value of 0. Plus, the fail reason would be written in log like : Database not found or no system permission;
or "The specified table (email_ctrl) is not in the database."

Thanks,
Chushia
Jonathan Fife
Honored Contributor

Re: Run sql from a shell script

Perhaps you have a cleanup script running from your cron.daily or root's crontab that is clearing out the /var/tmp directory indiscriminately.
Decay is inherent in all compounded things. Strive on with diligence