How to process a scheduled database task n times per day

Hi @Link 

You can choose a custom schedule in the Schedule database task and give the DBMS Scheduler expression for your requirement.

I think this expression would work for you :)

FREQ=DAILY;BYHOUR=6,12,17;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI

Check this topic for some good discussion on custom expressions in scheduled tasks

Scheduled Database Task between certain times | IFS Community

Hope it helps!

Damith

Hi @dsj 

thank you a lot.

What to do if I have times like 6:30, 12:00 and 17:15?

Do I need to create 3 database tasks?

For this kind of scheduling where the timing is not equally spread out in the day (e.g. at specific times and not every x hours), I would schedule them as 3 different tasks.  This is simple to do and will give you the control you need, as well as making it easiest to adjust just one of the jobs in the future.

Nick

Hi @NickPorter,

yes, I agree. But you will have too many jobs to manage. 

Well, that really depends on how many of these you will have that need to run at irregular times.  In your example your asked about there would be only 3 jobs (rather than 1) so that is still the approach I would recommend. 

In reality I would be really surprised if you truly have such a large number of jobs that MUST run at irregular times during a day that requires this kind of split into different jobs.  If so, I would really try to align the execution times to specific minutes and implement similar to what @dsj suggested.  But again, I really can’t foresee why you would get to ‘too many jobs to manage’ using this suggested approach if you used it when you needed to, and generally you wouldn't likely need to manage them often after creation anyway.

HTH,

Nick

Hi @NickPorter 

we have about 7 companies worldwide and if everybody needs 3 iterations at different times, we will have 21 jobs. 😀

Then I will try to standardise the scheduled times.

Thank you a lot. 👍🏼

If you need to validate the scheduler formula, you can use below simple script which will output next 10 run date/times

DECLARE
  start_date        TIMESTAMP;
  return_date_after TIMESTAMP;
  next_run          TIMESTAMP;
BEGIN
  start_date        := SYSTIMESTAMP;
  return_date_after := start_date;
  FOR i IN 1 .. 10 LOOP
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=DAILY; BYTIME=063000,120000,171500; BYDAY=MON,TUE,WED,THU,FRI',
                                            start_date,
                                            return_date_after,
                                            next_run);
    DBMS_OUTPUT.PUT_LINE('Next Run: ' || next_run);
    return_date_after := next_run;
  END LOOP;
END;

Hi @dsj 

a very good idea. It is cool. 😄

Thank you again.