Welcome to All Things Warehouse Builder

  • ETL
    November 20, 2008

Using 3rd party schedulers with OWB

Jean-Pierre Dijcks
Master Product Manager

We get this question a lot, so it warrants a post on the blog I think. Most people are asking how to schedule OWB jobs with a 3rd party scheduler like Control M, or Autosys or any other enterprise wide scheduler.

The other question; should I now ignore workflow and if I do what does that mean? is also quite interesting and related of course.

Now, lets look at workflow and schedulers first. If you use a process flow in OWB to create a chain of jobs with email notifications etc, you will deploy this process flow into the Oracle Workflow engine. That engine lives within the database and you can call the actual job (so the entire process flow) using the regular OWB execution templates. In other words you don't have to make an either/or choice here. You can deploy to Workflow and still use Control M for example to schedule the job.

You can also choose to not use the process flows and therefore not use Workflow at all. You will then need to build your dependencies in the external scheduling/flow engine. In that case you will refer to the mappings as SQL or PL/SQL jobs (more about that later). Now you have full control over the flow in the external scheduler. You will also use its notifications etc.

The interesting information is now how to call either the process flow, or how to call an actual mapping. If we look at the PL/SQL mappings they are quite simple to call. You can call the actual main of the mapping. A simple way would be to do something like this:

-- Run in the schema where the map is deployed


status varchar2(4000);





This little block you can now plug into the scheduler and it is now a job that will run the specific mapping.

Since OWB mappings can be non-PL/SQL objects (e.g. a SQL Loader call or an ABAP mapping for SAP) you cannot call all mappings like the above example.

Therefore OWB ships a script called the sqlplus_exec_template.sql, which is located in the /owb/rtp/sql. The exec template allows you to run any mapping from SQL. In that case from an external scheduler you would call SQL Plus and then run the above SQL script with the appropriate parameter values (open up the script for examples and for the actual parameters required).

The sqlplus_exec_template.sql script also allows you to run a process flow. So if you are using the process flows and have deployed them to workflow, use the sqlplus_exec_template.sql script to run the process flow from the external scheduler.

All in all, there are many ways to run jobs. The above is a brief summary on what options you have when running OWB jobs of all sorts. While you are reading about schedulers, make sure to also look at the Oracle Database Scheduler in 11g. It allows you to run jobs on non-Oracle systems via a nifty small agent. David wrote about this in some earlier posts on this blog.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.