Sometimes I need to have more control and flexibility over a job than just scheduling it at a given time every day.
The WhereScape RED scheduler job frequency of "custom" provides you additional options to schedule your job:
i) Only on certain days of the week which is handy if you don't want a daily job to run over the weekend
ii) Only for a specific time interval which is often required to check data warehouse source systems for new data every five minutes or every hour.
Tip: If you are scheduling a custom job to run frequently and you don’t need to keep its history, you can specify a “Logs retained” value that will stop job output being cluttered with hundreds of repeated entries.
There is also the very useful functionality within the WhereScape RED scheduler to add single or multiple parent jobs to a job. This is great for scenarios such as only running the daily sales job once the build reference data job has completed successfully, or only running the building consolidated fact table job once all of the different sales by region jobs have completed successfully. You can even specify a maximum time interval to wait for the parent job to complete.
All very useful stuff indeed, but occasionally, even using all the WhereScape RED scheduler options above still does not give you the scheduling options you need.
But don't worry, there are still a couple more clever procedures that will help you, Ws_Job_Release and Ws_Job_Create.
Ws_Job_Release allows the automated release of an on hold or waiting job. When would I use this you may ask ? As an example I have used this before in a daily job that checks a manually maintained WhereScape RED parameter (using WsParameterRead) just before it finishes to see if I want to automatically catch up with the source systems I am extracting data from. If the parameter value is yes, then I release another daily job. This enables multiple daily jobs to run one after the other in the minimum time without having to schedule additional job runs and guessing how long each would take.
Ws_Job_Create allows the automated creating of a new job from an existing job that is in either an on hold or waiting state. When would I use this as well you may ask ? As an example I have used this before when I have a polling job that has a procedure to release the daily job (using Ws_Job_Release) once a trigger row of data is found. I only want to start polling at midnight though. Within the polling job procedure if my trigger data row is not present I release another copy of the polling job (using Ws_Job_Release). If my trigger row is present I release the daily job (using Ws_Job_Release) AND create a new version of the polling job using Ws_Job_Create to run the following evening.
Ws_Job_Release and Ws_Job_Create are just two of many job related procedures that are available within WhereScape RED.
Combining WhereScape RED scheduler functionally, additional job related procedures with the use of WhereScape RED parameters will always enble you to get your data warehouse running exactly as you need it to.
Thanks to Aaron Parvin of WhereScape for historic job scheduling help.