Hi everyone

Some time ago I have asked about a job to delete some temporary tables. The code are following.

CREATE JOB "TmpTables"
RUN AS "System"
FROM DATE '2018-01-01' TO DATE '2100-12-31'
DAILY ON MON, TUE, WED, THU, FRI, SAT, SUN
BETWEEN TIME '01:00' AND TIME '01:00:00.999'
BEGIN        
   DECLARE xCursor CURSOR FOR xSql;
   DECLARE xTableName VARCHAR DEFAULT '';
   USE "RDB";     
   PREPARE xSql FROM 'select * from information.tables where name like ''tmp_%''';    
   OPEN xCursor;
   FETCH FIRST FROM xCursor('Name') INTO xTableName;
   WHILE NOT EOF(xCursor) DO
      EXECUTE IMMEDIATE 'drop table "' + xTableName + '"';
      REFRESH xCursor;
      FETCH FIRST FROM xCursor('Name') INTO xTableName;
   END WHILE;
   CLOSE xCursor;   
END

I have two questions about it.
1) BETWEEN TIME '01:00' AND TIME '01:00:00.999'  This is a correct way to start the job at 01:00 ? For me does not matter the time to finish. I realize some days the job does not fire but I do not know why.

2) I understand the "xCursor" is sensitive and therefore I need to "FETCH FIRST" instead of "FETCH NEXT". Am I right ?

Thanks for any comment

Eduardo

Jose


It is valid but I'd give it more than a one second space in which to start the job. Unless its critical that it runs as near to 1 as you can get I'd give it 5 minutes to fire.
Roy Lambert

Eduardo,

<< 1) BETWEEN TIME '01:00' AND TIME '01:00:00.999'  This is a correct way to start the job at 01:00 ? For me does not matter the time to finish. I realize some days the job does not fire but I do not know why. >>

Roy is correct - you're going to want to give the EDB Server a larger window of time in which to execute the job.  This will also give it the time it needs to retry the job if it fails for any reason.

<< 2) I understand the "xCursor" is sensitive and therefore I need to "FETCH FIRST" instead of "FETCH NEXT". Am I right ? >>

Yes, that is correct, and you also need the REFRESH call (which you have).

Tim Young
Elevate Software
www.elevatesoft.com

Now I understand better how and when job is fired.

Thank you Roy and Tim