Scheduling Rules in IDM v8

Now that SAP has declared that support will be either more expensive and/or harder to get with IDM v7.2 by the end of the year, the community is really ramping up into IDM v8. No doubt those that have upgraded are experiencing the new development environment, Eclipse. I can’t say I like it better or worse than the MMC. It’s just different but there are definitely a couple things I wish would have survived the transition.

For example, I can no longer right click in the destination tab of a job and select attributes that were retrieved from the SQL query in my source tab. When I’m reading from a custom table, that was something I used frequently. However, a big one that I recently discovered is that you can no longer add custom schedule rules via Eclipse. Wow! That’s a huge thing to lose! I mean, can anyone reading this think of a time when you worked in an environment where you DIDN’T write a custom schedule rule for a job? I’m super surprised that this functionality got cut. But, have no fear y’all! Custom schedule rules are still possible; you just have to write them directly into the database and then they’re selectable from jobs in Eclipse. You just have to know which two tables to write into and what the various columns in SQL need.

First table, mc_sched_rule, is where the new schedule rule will live. The RuleNo column is auto populated so don’t worry about that. The rest of the columns will determine how your rule works. You’ll need to write an INSERT INTO SQL query and specify which columns you want to populate. Some columns you’ll want to leave NULL depending on your needs but others you’ll need to fill in. Here’s what the columns mean and then you can write your own INSERT statement:

Name – This is what will show up on the pulldown menu in Eclipse when you select your new schedule rule on a job. Choose something short but descriptive, i.e. “Daily @ 2am”

Rule_Type – There’s only 3 options here. They can be viewed in the mc_sched_rules_names table but they are:
> 0 (zero), Frequency – Used for jobs that occur hourly, every 5 minutes, every 30 minutes, etc. all day, every day.
> 2, On Demand – You’ll probably never use this since you already have an On Demand rule.
> 10, Specific times – Used for once a week, twice a week, once a month, etc. This is probably the one you’ll use most often for custom schedule rules.

Description – The long description of your schedule rule, i.e. “Run every morning at 02:00.”

WeekDays – If you’re creating a rule to run on certain days of the week, this column will come into play. If you’re executing daily, like my 2am example, you’ll leave this NULL but if you want to run on Tuesdays and Thursdays, for example, you’ll actually put in a value of 20. This field is binary based. It uses an 8 bit binary format where the 8th bit is never used. After all, there’s only 7 days in a week. Starting from the right most digit, as is customary in binary, Sunday is the 1 value, Monday is the 2, Tuesday is the 4, Wednesday is the 8, Thursday is the 16, Friday is the 32 and Saturday is the 64. If you want to run on Fridays only, put in 32. Friday, Saturday and Sunday, add all three up and put in 97.

DayOfMonth – This is a bit easier. If you want to run on the 21st, put in 21. It does not appear possible to put in more than one day a month, like the 1st and 15th for example. If you figure out how to do something like this, please feel free to comment on your method.

RuleFlags – 5 appears to be for provisioning jobs (tasks) and jobs appear to use 11. I don’t think there are any other legal values but again, if you find something, please comment.

Next up, once you INSERT INTO the mc_sched_rule table, you need to add a line into the mc_sched_item table.

RuleNo – The number assigned to the custom rule you just created.

Frequency – If you’re doing a daily, at certain times schedule rule, put in the number of seconds between executions, i.e. 60 = every minute, 300 = every 5 minutes, etc. If you’re doing a daily or monthly rule, leave this NULL.

Sched_Time = How many seconds after midnight to execute on your designated day(s) or date, or up to how many seconds in the day to execute your frequency rule. If you have a frequency rule running every 10 minutes, for example, you put in 86399 here, the number of seconds in a day minus 1. I would guess if you put in a lesser number, your scheduled frequency would cut off when you hit that hour in the day and restart at midnight but I haven’t experimented with this. If you’re running a job on a certain day, again at 2am, you put in 7200… the number of seconds after midnight (2 hours) when you want your job to execute.

That should give you a pretty good head start on custom schedule rules in IDM v8. Questions? Additions? Comments? Please share so we all know what’s up! Thanks experts and I hope this saves someone some time someday.  🙂