Search code examples
sqlunixscheduled-taskssap-aseisql

ASE ISQL output to file, occassionally is empty or blank

Give this unix script, which is scheduled batch run:

isql -U$USR -S$SRVR -P$PWD -w2000 < $SCRIPTS/sample_report.sql > $TEMP_DIR/sample_report.tmp_1
sed 's/-\{3,\}//g' $TEMP_DIR/sample_report.tmp_1 > $TEMP_DIR/sample_report.htm_1
uuencode $TEMP_DIR/sample_report.htm_1 sample_report.xls > $TEMP_DIR/sample_report.mail_1

mailx -s "Daily Sample Report" email@example.com < $TEMP_DIR/sample_report.mail_1

There are occasionally cases where the sample_report.xls attached in the mail, is empty, zero lines.

I have ruled out the following:

  1. not command processing timeout - by adding the -t30 to isql, I get the xls and it contains the error, not empty
  2. not sql error - by forcing an error in the sql, I get the xls and it contains the error, not empty
  3. not sure of login timeout - by adding -l1, it does not timeout, but I can't specify a number lower than 1 second, so I can't say

I cannot reproduce this, as I do not know the cause. Has anyone else experienced this or have way to address this? Any suggestions how to find the cause? Is it the unix or the Sybase isql?

Solution

  • I found the cause. Since this is scheduled, and this particular report takes a long time to generate. Other scheduled scripts, I found have this line of code:

    rm -f $TEMP_DIR/*

    If the this long running report, overlaps with one of the scheduled scripts with the line above, the .tmp_1 can possibly be deleted, hence blank by the time it is mailed. I replicated this by manually deleting the .tmp_1 while the report was still writing the sql in there.