If you have ever had to work with the scheduler in the FDMEE UI then you will understand that it is far from perfect.
The main issues I find are that the UI doesn’t t have all the scheduling options that are available through the ODI studio, if you were not aware the scheduling defined in FDMEE is handled by ODI.
It is not possible to view the active schedules through the FDMEE UI and there is no option to delete individual schedules as it is an all or nothing situation.
I am sure these irritations will improve over time and yes there are workarounds like using the Studio but that is not a viable solution for everyone.
So on to the reason for this post, I was challenged to come up with a scheduling solution in on-premise FDMEE that met the following criteria:
It is possible to schedule metadata and data load rules, batches and custom scripts.
On to the next requirement and this is where the UI just doesn’t cut it, if you select schedule you are presented with the following options:
You can certainly set a start date and time but not an end date, also there are no repetition options.
If you compare the options to the ones available through the ODI Studio you will see FDMEE is lacking.
ODI has several repetition options which are not available in FDMEE.
This means the FDMEE UI does not meet the scheduler requirements and the use of the ODI Studio is not an option so how about a custom solution.
Well, ODI has a Java API which provides the ability to replicate pretty much anything you can do in the Studio, FDMEE has jython scripting so combine the two and things are looking up.
The good news is FDMEE has direct access to the ODI Java API so there is no need to mess around with jar files, just write some code and off you go.
I think it is probably best to run through the end solution and then break it down so show how it was done.
Three jython scripts were created and registered as part of the custom scheduler, these create, update and delete batch schedules.
If I execute the “Create Batch Schedule” script a popup window is displayed where the schedule can be defined.
The “Batch to schedule” parameter has a list box that returns the available batches.
The parameters that appear in the execute script window are defined in Custom Script Registration.
So when the custom script is run, the popup windows is displayed with the parameters that have been defined above.
You will see that “Batch to schedule” has been set to query type which means it will run a SQL query, the SQL query is created in Query Definition
The SQL query returns batches from the FDMEE database repository table AIF_BATCHES, this query is run when the lookup icon is selected in the execute script window.
After running the script and populating the parameter values it would look something like:
In the above example, the batch “OpenBatchDemo” will be scheduled to run every 30 minutes starting on the 1st August at 9am until the 5th August at 11pm.
The values are passed into the jython script and If the script runs successfully a custom message is displayed.
The process logs should also display the status of executing the custom script.
The FDMEE log associated with the process contains the schedule definition, this is handled by the jython script.
I will use the Studio to demonstrate what has happened on the ODI side, the scenario “COMM_EXECUTE_BATCH” which is the one FDMEE uses to run batches has had a new schedule created.
The schedule matches to the parameter values defined in the FDMEE UI though it does need an explanation of why it is created in this way.
Looking at the schedule you would think that it should have been created using “Active for the period” and a starting and ending date set, unfortunately I found out the hard way and that is not the way ODI operates when repetitions are involved.
When using repetitions the status in the definition tab should be set as “Active” and then the start date/time set as part of Execution like above.
The remaining settings are defined in the “Execution Cycle” tab.
Instead of setting an end date/time a calculation needs to be performed to work out the difference between the start and end dates, this value is used in “Maximum Cycle Duration”.
The time between each repetition is set as “Interval between Repetitions”, it is worth mentioning that ODI only considers the interval after a session completes to stop overlapping of sessions.
Say a scheduled batch starts at 9:00am and has a repetition interval of 30 minutes, if the batch takes 10 minutes to complete the next scheduled run will not take place until 9:40am and not what you might expect of 9:30.
If there is a strict time of when the repetitions need to start, then this option is not for you and alternatives need to be looked at.
In the variables tab of the schedule the required values including the batch name will have been set.
The creation of the schedule is all handled by the custom jython script and the ODI Java API.
I am not going to go through the jython script line by line as it would probably bore you to death and it is much better to write your own scripts in your style instead of copying them as you learn much more that way.
I do recommend looking through the ODI Java API documentation as that holds the key to working with the scheduler.
Just to recap when the custom script is run in FDMEE a window is displayed where parameter values are entered, these parameters match to those defined in the custom script registration.
The first part of the jython script retrieves and stores the values passed in from the FDMEE when the custom script is executed, this is done using the API method
getCustomScriptParameterValue(<parameter_name>)
The start date values are converted into a Java calendar which is a requirement for the ODI API.
The time in minutes between the start and end date is calculated which will be used to define how long the schedule will be active for.
The next section of the script deals with the connection to the ODI database repository, this information is retrieved using the FDMEE API method
getProfileOptionValue(<option_name>,<application_id>,<username>)
If you didn’t know the ODI profile option values are located within system settings.
The values are stored in an FDMEE database table called AIF_PROFILE_OPTION_VALUES and by using the getProfileOptionValue method these values can be accessed.
Next the name of the ODI scenario which will be scheduled is defined, there are three variables that are added to the scheduled scenario.
As explained earlier FDMEE uses the “COMM_EXECUTE_BATCH” scenario to run batches, the version is “001”
The variables that are required for the scheduling of the batch scenario are the user that will run the batch, the name of the batch and the URL for the FDMEE web application, for example
The remaining part of the script deals with the creation of the schedule and to summarise:
So now we have a custom schedule that has been simply created from the FDMEE UI, what about updating, deleting and viewing active schedules or carrying out these activities from outside of the UI? Well I think I have covered enough for now so look out for a follow-up post.
The main issues I find are that the UI doesn’t t have all the scheduling options that are available through the ODI studio, if you were not aware the scheduling defined in FDMEE is handled by ODI.
It is not possible to view the active schedules through the FDMEE UI and there is no option to delete individual schedules as it is an all or nothing situation.
I am sure these irritations will improve over time and yes there are workarounds like using the Studio but that is not a viable solution for everyone.
So on to the reason for this post, I was challenged to come up with a scheduling solution in on-premise FDMEE that met the following criteria:
- Ability to schedule batches.
- Define a start and end date and repeat every x minutes/hours.
- Update existing scheduled batches.
- Delete individual batches.
- Run from the UI or from a batch script which can be run anywhere.
- No access to ODI Studio (ODI Console does not have scheduling functionality)
It is possible to schedule metadata and data load rules, batches and custom scripts.
On to the next requirement and this is where the UI just doesn’t cut it, if you select schedule you are presented with the following options:
You can certainly set a start date and time but not an end date, also there are no repetition options.
If you compare the options to the ones available through the ODI Studio you will see FDMEE is lacking.
ODI has several repetition options which are not available in FDMEE.
This means the FDMEE UI does not meet the scheduler requirements and the use of the ODI Studio is not an option so how about a custom solution.
Well, ODI has a Java API which provides the ability to replicate pretty much anything you can do in the Studio, FDMEE has jython scripting so combine the two and things are looking up.
The good news is FDMEE has direct access to the ODI Java API so there is no need to mess around with jar files, just write some code and off you go.
I think it is probably best to run through the end solution and then break it down so show how it was done.
Three jython scripts were created and registered as part of the custom scheduler, these create, update and delete batch schedules.
If I execute the “Create Batch Schedule” script a popup window is displayed where the schedule can be defined.
The “Batch to schedule” parameter has a list box that returns the available batches.
The parameters that appear in the execute script window are defined in Custom Script Registration.
So when the custom script is run, the popup windows is displayed with the parameters that have been defined above.
You will see that “Batch to schedule” has been set to query type which means it will run a SQL query, the SQL query is created in Query Definition
The SQL query returns batches from the FDMEE database repository table AIF_BATCHES, this query is run when the lookup icon is selected in the execute script window.
After running the script and populating the parameter values it would look something like:
In the above example, the batch “OpenBatchDemo” will be scheduled to run every 30 minutes starting on the 1st August at 9am until the 5th August at 11pm.
The values are passed into the jython script and If the script runs successfully a custom message is displayed.
The process logs should also display the status of executing the custom script.
The FDMEE log associated with the process contains the schedule definition, this is handled by the jython script.
I will use the Studio to demonstrate what has happened on the ODI side, the scenario “COMM_EXECUTE_BATCH” which is the one FDMEE uses to run batches has had a new schedule created.
The schedule matches to the parameter values defined in the FDMEE UI though it does need an explanation of why it is created in this way.
Looking at the schedule you would think that it should have been created using “Active for the period” and a starting and ending date set, unfortunately I found out the hard way and that is not the way ODI operates when repetitions are involved.
When using repetitions the status in the definition tab should be set as “Active” and then the start date/time set as part of Execution like above.
The remaining settings are defined in the “Execution Cycle” tab.
Instead of setting an end date/time a calculation needs to be performed to work out the difference between the start and end dates, this value is used in “Maximum Cycle Duration”.
The time between each repetition is set as “Interval between Repetitions”, it is worth mentioning that ODI only considers the interval after a session completes to stop overlapping of sessions.
Say a scheduled batch starts at 9:00am and has a repetition interval of 30 minutes, if the batch takes 10 minutes to complete the next scheduled run will not take place until 9:40am and not what you might expect of 9:30.
If there is a strict time of when the repetitions need to start, then this option is not for you and alternatives need to be looked at.
In the variables tab of the schedule the required values including the batch name will have been set.
The creation of the schedule is all handled by the custom jython script and the ODI Java API.
I am not going to go through the jython script line by line as it would probably bore you to death and it is much better to write your own scripts in your style instead of copying them as you learn much more that way.
I do recommend looking through the ODI Java API documentation as that holds the key to working with the scheduler.
Just to recap when the custom script is run in FDMEE a window is displayed where parameter values are entered, these parameters match to those defined in the custom script registration.
The first part of the jython script retrieves and stores the values passed in from the FDMEE when the custom script is executed, this is done using the API method
getCustomScriptParameterValue(<parameter_name>)
The start date values are converted into a Java calendar which is a requirement for the ODI API.
The time in minutes between the start and end date is calculated which will be used to define how long the schedule will be active for.
The next section of the script deals with the connection to the ODI database repository, this information is retrieved using the FDMEE API method
getProfileOptionValue(<option_name>,<application_id>,<username>)
If you didn’t know the ODI profile option values are located within system settings.
The values are stored in an FDMEE database table called AIF_PROFILE_OPTION_VALUES and by using the getProfileOptionValue method these values can be accessed.
Next the name of the ODI scenario which will be scheduled is defined, there are three variables that are added to the scheduled scenario.
As explained earlier FDMEE uses the “COMM_EXECUTE_BATCH” scenario to run batches, the version is “001”
The variables that are required for the scheduling of the batch scenario are the user that will run the batch, the name of the batch and the URL for the FDMEE web application, for example
The remaining part of the script deals with the creation of the schedule and to summarise:
- The “COMM_EXECUTE_BATCH” scenario is located and stored.
- A new schedule is created against the scenario.
- The status of the schedule is set as active.
- The start date, repetition interval and the duration of the schedule are set.
- The scenario variables values are defined.
- The information is then committed and the ODI agent schedule updated.
So now we have a custom schedule that has been simply created from the FDMEE UI, what about updating, deleting and viewing active schedules or carrying out these activities from outside of the UI? Well I think I have covered enough for now so look out for a follow-up post.
