topic Re: Run sql from a shell script in Operating System - Linux

Run sql from a shell script

Chushia — Tue, 22 Aug 2006 09:52:39 GMT

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

Re: Run sql from a shell script

Jonathan Fife — Tue, 22 Aug 2006 09:59:40 GMT

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.

Re: Run sql from a shell script

Steven E. Protter — Tue, 22 Aug 2006 09:59:48 GMT

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

Re: Run sql from a shell script

Chushia — Tue, 22 Aug 2006 10:31:23 GMT

/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

Re: Run sql from a shell script

Jonathan Fife — Tue, 22 Aug 2006 12:49:22 GMT

Perhaps you have a cleanup script running from your cron.daily or root's crontab that is clearing out the /var/tmp directory indiscriminately.