Run sql from a shell script

[Chushia]

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

[Jonathan Fife]

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.

[Steven E. Protter]

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

[Chushia]

/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]

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