In ICS Definitive Guide #2 we looked at the scheduling of integrations and mentioned the possibility of using iCal. In this tip we shall explore advanced iCal facility for defining schedules within ICS.
The iCal format is probably best known for sharing meeting events in tools such as outlook. The format is actually a standard defined by the IETF originally in 1998 (https://www.ietf.org/rfc/rfc2445.txt) and updated several times the latest being 2015 (https://tools.ietf.org/html/rfc7529). The goal of the definition has been for the purpose of driving standardisation across PIM tools (Personal Information Management) tools which includes Outlook and Thunderbird etc. It is a substantial standard the original document ran to 150 pages, but the section supported by the schweduler’s advanced option purely relates to the reoccurence definition covered by section 4.3.10 of the original RFC, and only takes five pages. If you want to know lots about iCal and the varying revisions then you may find https://icalendar.org/ helpful.
It is worth mentioning that today ICS under the hood exploits Oracle’s Enterprise Scheduler Service (ESS). ESS documentation identifies that it works to the 2445 version of the specification (see here). ESS became a 1st class citizen component with the start of the 12c middleware product stack. As a result, if you see any errors processing an iCal configuration the error codes will reference ESS does.
So why support for this part of the iCal standard. Well unlike the UI provided, the FREQ definition with all the elements of the definition that can be provided mean some very sophisticated schedules can be defined. For example you could create a definition that says the scheduler should only trigger 9.15, 9.30, 14.15, 14.30 on Wednesdays for week numbers 7, 8, 21 and 49.
For ease of reference, the definition of how a FREQ element should be created is, according for RFC2445:
; either UNTIL or COUNT may appear in a ‘recur’,
; but UNTIL and COUNT MUST NOT occur in the same ‘recur’
( “;” “UNTIL” “=” enddate ) /
( “;” “COUNT” “=” 1*DIGIT ) /
; the rest of these keywords are optional,
; but MUST NOT occur more than once
( “;” “INTERVAL” “=” 1*DIGIT ) /
( “;” “BYSECOND” “=” byseclist ) /
( “;” “BYMINUTE” “=” byminlist ) /
( “;” “BYHOUR” “=” byhrlist ) /
( “;” “BYDAY” “=” bywdaylist ) /
( “;” “BYMONTHDAY” “=” bymodaylist ) /
( “;” “BYYEARDAY” “=” byyrdaylist ) /
( “;” “BYWEEKNO” “=” bywknolist ) /
( “;” “BYMONTH” “=” bymolist ) /
( “;” “BYSETPOS” “=” bysplist ) /
( “;” “WKST” “=” weekday ) /
( “;” x-name “=” text )
freq = “SECONDLY” / “MINUTELY” / “HOURLY” / “DAILY”/ “WEEKLY” / “MONTHLY” / “YEARLY”
enddate = date
enddate =/ date-time ;An UTC value
byseclist = seconds / ( seconds *(“,” seconds) )
seconds = 1DIGIT / 2DIGIT ;0 to 59
byminlist = minutes / ( minutes *(“,” minutes) )
minutes = 1DIGIT / 2DIGIT ;0 to 59
byhrlist = hour / ( hour *(“,” hour) )
hour = 1DIGIT / 2DIGIT ;0 to 23
bywdaylist = weekdaynum / ( weekdaynum *(“,” weekdaynum) )
weekdaynum = [([plus] ordwk / minus ordwk)] weekday
plus = “+”
minus = “-”
ordwk = 1DIGIT / 2DIGIT ;1 to 53
weekday = “SU” / “MO” / “TU” / “WE” / “TH” / “FR” / “SA”
;Corresponding to SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY,
;FRIDAY, SATURDAY and SUNDAY days of the week.
bymodaylist = monthdaynum / ( monthdaynum *(“,” monthdaynum) )
monthdaynum = ([plus] ordmoday) / (minus ordmoday)
ordmoday = 1DIGIT / 2DIGIT ;1 to 31
byyrdaylist = yeardaynum / ( yeardaynum *(“,” yeardaynum) )
yeardaynum = ([plus] ordyrday) / (minus ordyrday)
ordyrday = 1DIGIT / 2DIGIT / 3DIGIT ;1 to 366
bywknolist = weeknum / ( weeknum *(“,” weeknum) )
weeknum = ([plus] ordwk) / (minus ordwk)
bymolist = monthnum / ( monthnum *(“,” monthnum) )
monthnum = 1DIGIT / 2DIGIT ;1 to 12
bysplist = setposday / ( setposday *(“,” setposday) )
setposday = yeardaynum
Applying the syntax to get complex schedules
With all of that you have if careful all the information to create the most complex schedules you like. To illustrate here is a validating example:
This translates to a schedule triggering every 3 years on May 2nd and 7th. As you can see trying to express this using calendars and menus would probably prove to be fairly complex. But pretty simple in text once the notation is understood.
We should address a couple of challenges that exist.
Avoiding the traps
Firstly (the current ICS documentation here) shows the example expression as:
However the example omits a key terminating semi colon (essentially each part of the expression needs to be terminated by a semi colon). So, the correct representation is:
Whilst not too serious, an easy mistake to make. Unfortunately today the error messages from ESS back to ICS are rather uninformative with an error message and code of
ESS-09402 Failed to compile recurrence expression
So if you see this error, our advise is to simplify the expression or build it up a piece at a time if you can.
The last issues we have yet to understand relate to the definitions support for extension attributes shown as x-name in the specification. Well to be honest, this is not important as ICS and ESS do not appear to have any extension elements. The slightly more troubling question, which we have yet to get to the bottom of, is the support for COUNT in the expression which does not appear to be accepted.
How to schedule for Business days based on say UK calendar. Is there a away?
Yes it is possible. It is done through setting the time zone checkout https://blogs.oracle.com/integration/timezone-functionality-in-oic-schedules
How can I schedule the integration to ensure it doesnt run on a public holiday? For e.g., an integration scehduled to run on last Fri of the month should instead run on last thursday of the month in case the last friday of the month is a public holiday in say USA.
The key is whether you can express as a formula the public holidays
Can’t think of anyway to define the public holidays in the form of a formula.
In which case you’ll not be able to encode it into a schedule. We’d suggest running a 2nd schedule that triggers for the months which might need an alternate day. Then when the integration runs it looks up to a DB table to work out if needs to run or not
Is it possible to schedule an integration on say 1st workday of a month? Or say last workday of the month? (Irresepective of whether or not the first workday is a public holiday) For e.g., 1st workday of Jul 2021 would be on Thu but 1st workday of June would be Tuesday.
Here is an explanation of how to do it https://medium.com/scrubbi/creating-a-google-calendar-event-that-repeats-on-the-first-weekday-of-every-month-9ca0113eedea
Is it possible to schedule a run on the 6th day after month end close, which will be different for all the months for different years? OIC does not allow more than 5 recurrences in iCal.
This is a common question. As month end varies unless you can express a relatively simple formula then ical can’t help you. We’ll blog about the options soon. But essentially either keep a DB of month end dates, or in your case month end + 6. Then the start of the integration check to see if it is a correct date when the scheduler triggers if it is then continue otherwise stop. If you can derive the date, then use FaaS to determine dates is valid and then calculates and sets a schedule for next month end using a 1 off schedule
Is it possible to create an ICAL schedule like below
Jan – 12th to 31st
Feb- 1st to 6th
April – 12th to 30th
May – 1st to 6th
july – 12th to 31st
Aug – 1st to 6th
Oct – 12th to 31st
Nov – 1st to 6th
That looks like a complex repeating pattern – checkout https://oracle-integration.cloud/2021/08/26/oic-scheduling-for-end-of-month-style-processes-tip-11/
Thank you Phil for the promt response. I was wondering if I can have 2 FREQs defined in the same iCAL expression. 1 for Jan, Apr, Jul, Oct where the Days will be 12 to 31 and the other for rest of the months where Days will be 1 to 6. Is it possible to have two schedules like this?
Is it possible to schedule an integration to run every 30 minutes between first and fourth weekday of every month?
You want something like this – FREQ=MONTHLY;BYSETPOS=1;BYDAY=MO;INTERVAL=1. Have a look at tip 11. There are a couple of tools that could help formulate the expression