Using DBMS Scheduler with OWB Mappings

Starting in OWB 10g and higher, you can apply schedules to mappings and process flows that you want to execute in an Oracle Database using the dbms_scheduler package.  This blog entry will walk through the steps in creating the necessary objects inside of the OWB design client, deploying them to a target location database, and then enabling/disabling the scheduled job.

For a more in-depth description of the capabilities of the scheduling system, the chapter "Scheduling ETL Jobs" at http://download.oracle.com/docs/cd/E11882_01/owb.112/e10935/scheduling_etl.htm#CHDGEGJF

All screen shots are from the OWB 11gR2 release.

CREATE A CALENDAR MODULE

First we need to create a new Calendar Module inside the OWB design client.

02_New_Calendar_Module

Using the Create Module Wizard, name the Calendar Module and follow the steps in the wizard to create the module.

05_Calendar_Wizard_2

Assign (or create a new) location to the module.  This location is the Oracle Database where the dbms_scheduler is running.  The schema isn't important, so I just chose my map deployment location since the map is going to run in the same Database instance as the scheduler.

06_Calendar_Location

On the next screen of the wizard, hit 'Finish'.  And then hit 'ctrl-S' to save the new module into the OWB repository.

CREATE A SCHEDULE

Once the calendar module is created, it is now time to create a schedule to control the running of jobs that will be assigned to this schedule.

To start, right click on the Calendar Module and select 'New Calendar'.

07_NewCalendarTask

Name the schedule and continue on to the next step of the wizard.

08_Create_Schedule_Step1

The start and end dates and times define the duration for which the schedule is valid.  Begin by specifying the time zone. You can accept the default start date or specify a time in the future. Be sure to change the default end date which is the same as the default start date. 

09_ChooseStartEndTime

The repeat expression determines how often the schedule is executed. Define the repeat expression by specifying the Frequency Unit, the Repeat Every value, and one or more By Clauses values.

When working in the wizard, note that By Clauses are not available. After you complete the wizard, you can open the schedule and set the By Clauses using the schedule editor.

The Frequency Unit determines the type of recurrence. The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.

Also, you can define schedules to run One Time.

The Repeat Every value specifies how often the recurrence repeats. The default value is 1 and the maximum value is 999. If you select YEARLY for the Frequency Unit and leave the Repeat Every value at 1, the schedule is evaluated for every year included in the date range you specify in Start and End Dates and Times. For the same schedule, if you change Repeat Every to 2, the schedule is evaluated only every other year within the specified date range.

By Clauses enable you to define repeat expressions for complex schedules such as a schedule to run the first Friday of any month that contains 5 weeks. For each clause you can either type in values or click the Ellipsis button to view a selector dialog box. If your goal is to know how to quickly type in values, first use the selector dialog box to learn what values are valid and also refer to Example Schedules.

When you use the selector dialog box and select OK, the results are displayed in the schedule editor. In this way, you can use the selector dialog box to learn what values are valid.

10_ChooseFrequency2

Select 'Next' to review the calendar, or 'Finish' to create it.  Save the calendar using 'ctrl-S'.

MODIFYING AN EXISTING SCHEDULE

Use the Schedule Editor to edit schedules. Double click on the schedule to open up the Edit Schedule dialogue.

The repeat expression appears in the lower left panel of the editor. Use the repeat expression to specify the Frequency Unit, Repeat Every, and one or more By Clauses.

The schedule preview appears in the lower right panel. The preview refreshes each time you press the Enter key or navigate to a new cell on the schedule editor. If you specify an invalid schedule, the preview displays an error message.

10_5_Schedule_Edit

CONFIGURE A MAP TO RUN ON A SCHEDULE

Now it is time to configure a pre-existing map to run on this newly created schedule.  Right click the map and select 'Configuration' - and then click on the '...' on the 'Referred Calendar' property.  The Referred Calendar dialog will come up, expand the tree and select the desired calendar - 'EVERY_FIVE_MINUTES' in this case.

11_ConfigureMap

Hit 'OK' to assign a calendar, and then save by using 'ctrl-S'.

DEPLOYING THE JOB VIA THE CONTROL CENTER MANAGER

In the control center, expand the target location/Scheduled Jobs selection.  Here you will find a new entry corresponding to <MAP_NAME>_JOB - right click, Set Action => Create.  The target location is the location assigned to the calendar module when it was created.

Then select Deploy and make sure it is successful in the Control Center Jobs window.

ENABLE THE EXECUTION OF THE CALENDAR VIA CONTROL CENTER

In the control center, after the Scheduled Job has been created, right click the calendar and select 'Start'.  At this point the job will execute at the next scheduled interval.

The Control Center 'Execution' tab will show the audit information for every time this job executes.

ENABLE THE EXECUTION OF THE CALENDAR VIA SQL*PLUS

In order to enable the job in the dbms_scheduler via SQL*Plus, connect as the sys user and execute the following code:

BEGIN
sys.dbms_scheduler.set_attribute( name => '"TAHOE_11201_U"."TEST_MAP_JOB"', attribute => 'job_action', value => '"TAHOE_11201_U"."PTEST_MAP_JOB"');
sys.dbms_scheduler.set_attribute( name => '"TAHOE_11201_U"."TEST_MAP_JOB"', attribute => 'repeat_interval', value => 'FREQ=MINUTELY;INTERVAL=5');
sys.dbms_scheduler.enable( '"TAHOE_11201_U"."TEST_MAP_JOB"' );
END;

DISABLE THE EXECUTION OF THE CALENDAR VIA SQL*PLUS

If you want to disable the job from running, connect to SQL*Plus as the sys user and execute the following code:

BEGIN
sys.dbms_scheduler.disable( '"TAHOE_11201_U"."TEST_MAP_JOB"' );
END;

ENABLE THE EXECUTION OF THE CALENDAR VIA ENTERPRISE MANAGER

It is also possible to control the job through the Oracle Enterprise Manager.  Navigate to the Server Tab, and then under the 'Oracle Scheduler' selection - select the 'Jobs' link.

By selecting 'Jobs' - all jobs in the dbms_scheduler will be displayed.  Select the 'TEST_MAP_JOB' and choose 'Edit Job Definition'. 

To Enable the job, select 'Yes' and then apply the change to the database. 

Once the job has been enabled, it will start running at the next scheduled interval.

DISABLE THE EXECUTION OF THE CALENDAR VIA ENTERPRISE MANAGER

It is also possible to control the job through the Oracle Enterprise Manager.  Navigate to the Server Tab, and then under the 'Oracle Scheduler' selection - select the 'Jobs' link.

By selecting 'Jobs' - all jobs in the dbms_scheduler will be displayed.  Select the 'TEST_MAP_JOB' and choose 'Edit Job Definition'. 

To disable, select 'No' and then apply changes to the database.

Once the job has been disabled, it will stop running and ignore all future intervals.

Comments:

This option is only available in enterprise version. What is solution for standard edition one?

Posted by Mindaugas on January 30, 2012 at 03:14 AM PST #

You must manually schedule your jobs if running without Enterprise ETL.

Cheers
David

Posted by David on February 01, 2012 at 08:39 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today