FDMEE - Custom Scheduler - Part 2

In the last part I went through an example solution to build a custom scheduler which could be accessed through the FDMEE UI, it was created using a combination of jython and the ODI Java API, the post covered in detail the process to create a new batch schedule but could easily be adapted to schedule data and metadata load rules or custom scripts.

In this post I want to continue where I left off and move on to updating, deleting and viewing active schedules, also look at the possibilities of running these tasks outside of the FDMEE UI.

Let us start off with updating an existing schedule, a custom script was registered which has parameters defined to select the schedule, set the start and end dates and repetition in minutes or hours. These parameters will be displayed when the custom script is executed and then passed into the jython script which will then update the schedule.

The parameter “SCHEDULE_ID” has been defined to call a query called “Batch Info”, this SQL query is against the ODI repository which hold the scheduling information, the query has been defined to return an understandable description and the ODI internal ID for the schedule, this ID is important as it will be used with the ODI Java API to access the selected schedule.

You will see the query in action when I execute the custom script which now appears in the UI under the “Custom Batch Scheduler” group it has been assigned to.

Executing the “Update Batch Schedule” script displays a window with the parameters that were defined in the script registration.

Now if select the schedule to update the SQL query comes into action and the script name, start to end date, repetition and schedule ID are displayed ready for selection.

It would be nice if there was the functionality to base a SQL query on the results of a SQL query but unfortunately that is not yet available in FDMEE so the remaining input fields need to input manually, as the schedule is being updated this is not much of an issue because you would need to enter new values anyway.

Now the new schedule information has been entered the script can be run and a custom message is displayed if the update was successful.

The process logs also display the status of executing the custom script.

The FDMEE log associated with the process contains the updated schedule information.

To prove the schedule has been updated I can switch over to the ODI console where it should have a new start date of 20th of August at 8am.

As I explained in the last part the end date has to be calculated and set as a maximum cycle duration, the execution cycle includes the interval between repetitions which correctly matches the value entered when running the custom script.

To prove the scheduling information had been update correctly I could have also just run the update schedule custom script again and selected the option to return the active schedules, this would have returned the updated values from the ODI repository.

What you do need to watch out for with populating parameter value fields is there is currently a 50-character limit, the SQL will return no problem, for example

As the above text is are over 50 characters then after selecting you would be hit with an unfriendly ADF error.

The magic to update the schedule is all handled in the jython script, the script follows the same concept as what I went through in the last part so there is no need for me to show all the script again.

The parameters values are stored, the schedule duration is calculated by finding the difference in minutes between the start and end date.

A connection is made to the ODI repository and then the schedule is returned by searching based on the ID that was passed into the script.

The values of the schedule are then updated to the ones defined when running the script, these values are then committed and the agent schedule updated.

So that is creating and updating schedules covered off, on to deleting a schedule.

Once again a custom script is registered, this time there is only the requirement for one parameter and that is to select the schedule to delete, the query is exactly the same as the one used in the update script.

After the script has been registered, it is available in script execution under the group which it was registered to.

When the script is executed there is one parameter available to select the schedule to delete.

Selecting the magnifying glass will run the SQL query to run all the active batch schedules.

I selected the first schedule in the list to be deleted.

A custom message is displayed if the delete was successful.

If I run the script again and view the schedules it confirms the schedule has definitely been deleted.

The jython script is much simpler this time as all that is required is to retrieve the ID of the schedule from the custom script parameter values, connect to the ODI repository, find the schedule by the ID, delete the schedule, commit and then update the ODI agent scheduler.

Just to recap that means with the custom scheduler solution we have the ability to create, update and delete schedules.

So how about viewing the active schedules, well you can certainly run the custom scripts like I have already shown to display the schedules but personally I don’t feel like is the nicest solution, what I did to overcome this was to create a report in FDMEE.

Before creating a report a SQL query was created and registered, the query was similar to one used in the update/delete scripts.

The query produces the following output that be used in the FDMEE report.

Once the query validated the generate XML button was selected, this produces an XML file which can be loaded into word using BI Publisher desktop to generate a template file.

Now that everything is in place a new report definition was created, the query, template and group were assigned to the definition, there were no need for any parameters for this report.

The report is available against the report group it has been assigned to.

When executing the report you have the option to generate in either PDF, HTML or Excel (XLSX)

I selected XLSX and depending on browser settings it will either open directly in excel or you will have the option to download and then open in excel.

So this can report can be generated at any time to provide up-to-date batch schedule information.

Finally on to running the activities such as creating a new schedule outside of the FDMEE UI, if you are not aware from 11.1.2.4.210 it is possible to use a REST resource to run tasks such as data load rules, batches, custom scripts and reports, I have covered the functionality in previous blogs if you would like to understand it in more detail.

To run a custom script you can make a post to the following URL format:

http(s)://<webserver>:<port>/aif/rest/V1/jobs/

The body of the post will need to include in JSON format the job type which will be REPORT, the report format type which will be SCRIPT, the job name which will be the name of the custom script to run and the parameter display names and values.

An example to run the script to create a new batch schedule using a REST client would be:

After the post has been made a response will be returned with FDMEE process information such as ID and status.

If the status is in a running state a GET request can be made to keep checking the status.

You could then convert this into a script which could be called by an automated process and parameters passed in or say run from a user’s client machine instead of having to log into workspace.

I created the following example script using PowerShell which uses the REST resource to run the custom script to create a new schedule, I know it requires error trapping but it just to give an idea.

When the script is run, it takes user input to define the parameters of the schedule which it then converts into JSON, makes a post to the REST resource and then keeps checking the status until the process has successfully completed.

Running the report again to view the scheduled batches confirm that the new schedule has been created.

Job done, a full solution to create, update, delete and view schedules.

FDMEE – Custom Scheduler

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:

• 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)

Let us go through the criteria to see if it is currently possible in FDMEE, well there is no problem to schedule batches through the UI.

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.