26 Scheduling ETL Objects

This chapter contains the following topics:

About Schedules

Use schedules to plan when and how often to execute operations that you designed within Warehouse Builder. You can apply schedules to mappings and process flows that you want to execute in an Oracle Database, version 10g or higher.

When you are in the development phase of using Warehouse Builder, you may not want to schedule mappings and process flows but rather start and stop them immediately from a Control Center as described in "Deploying Objects".

You can define schedules to execute once or to execute repeatedly based on an interval you define in the user interface. For each schedule you define, Warehouse Builder generates codes that follows the iCal calendaring standards, which can be deployed to a scheduler such as Oracle 10g Scheduler or Applications Concurrent Manager.

Schedules are defined in the context of projects and contained in schedule modules under the Schedules node on the Project Explorer.

Figure 26-1 displays schedules on the Project Explorer.

Figure 26-1 Schedules on the Project Explorer

This illustration is described in the surrounding text.
Description of "Figure 26-1 Schedules on the Project Explorer"

For every new project you create, Warehouse Builder creates a default schedule module, MY_CAL_MODULE. Create schedules under the default module or create a new module by right- clicking the Schedules node and selecting New.

Deploying Warehouse Builder Schedules to Oracle Workflow

To successfully deploy Warehouse Builder schedules to Oracle Workflow, ensure access to the correct version of Oracle Workflow as described in the Oracle Warehouse Builder Installation and Administration Guide. Scheduled jobs should be deployed to a standard database location, not to a Workflow Location. Only Process Flow packages should be deployed to Oracle Workflow.

Scheduled jobs may reference an executable object such as a process flow or a mapping. If a job references a process flow, then you must deploy the process flow to Oracle Workflow and deploy the scheduled job to either a database location or a Concurrent Manager location.

For remote Oracle Workflow locations and remote Warehouse Builder 10g locations to which schedules are deployed, ensure that the target location has the CREATE SYNONYM system privilege. If the Evaluation Location is specified or the deployment location references a different database instance from Control Center schema, then the deployment location must have the CREATE DATABASE LINK system privilege.

Process for Defining and Using Schedules

  1. To create a module to contain schedules, right-click the Schedules node and select New.

  2. To create a schedule, right-click a schedule module and select New.

    Warehouse Builder displays the Schedule Wizard.

  3. On the Name and Description page, type a name for the schedule that is 24 characters or less.

    The rules for most Warehouse Builder objects is that physical names can be 1 to 30 alphanumeric characters and business names can be 1 to 2000 alphanumeric characters.

  4. Follow the instructions in the Schedule Wizard.

    Use the wizard to specify values for Start and End Dates and Times, Frequency Unit, and Repeat Every. When you complete the wizard, Warehouse Builder saves the schedule under the schedule module you selected.

    See Also:

    "Example Schedules" for examples of schedules
  5. On the Project Explorer, right-click the schedule you created with the wizard and select Open Editor.

    Warehouse Builder displays the schedule editor. Review your selections and view the list of calculated execution times. For complex schedules, you can now enter values for the By Clauses.

  6. To apply a schedule to a mapping or process flow, right-click the object in the Project Explorer and select Configure. In the Referred Calendar field, click the Ellipsis button to view a list of existing schedules.

    For any mapping or process flow you want to schedule, the physical name must be 25 characters or less and the business name must be 1995 characters or less. This restriction enables Warehouse Builder to append to the mapping name the suffix _job and other internal characters required for deployment and execution.

  7. Deploy the schedule.

    Recall that when you deploy a mapping, for example, you also need to deploy any associated mappings and process flows and any new target data objects. Likewise, you should also deploy any associated schedules.

    When properly deployed with its associated objects, the target schema executes the mapping or process flow based on the schedule you created.

Editing a Schedule

Use the schedule editor to edit a schedule.

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.

For examples of schedules you can define, see Example Schedules.

Figure 26-2 The Schedule Editor

This illustration is described in the surrounding text.
Description of "Figure 26-2 The Schedule Editor"

Start and End Dates and Times

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.

When working in the wizard, click Next to view the next page.

When working in the schedule editor, the start date and time become the defaults for the By Clauses in the Repeat Expression. The execution time in Schedule Preview corresponds to the Start Time.

Defining Schedules To Repeat

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.

Frequency Unit

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.

Repeat Every

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

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.

Figure 26-3 Selector dialog box for Picking Months in a Year

This illustration is described in the surrounding text.
Description of "Figure 26-3 Selector dialog box for Picking Months in a Year"

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.

Figure 26-4 Month Clause for January and June

This illustration is described in the surrounding text.
Description of "Figure 26-4 Month Clause for January and June"

You can define the following by clauses:

By Month

This specifies in which month or months the schedule is valid. If you type in values, use numbers such as 1 for January and 3 for March, or use three-letter abbreviations such as FEB for February and JUL for July.

By Week Number

Only when you select Yearly for the Frequency Unit can you schedule by the week number in the year.

You can either type in values or click the Ellipsis button to view the selector dialog box. If you type in values, valid values include positive and negative integers from 1 to 52 or 53, depending on the year. For example, to set a schedule to run on the second to last week of the year, you can either type -2 or fill in the selector dialog box.

Figure 26-5 By Week Number Clause Set to Second To Last Week of the Year

This illustration is described in the surrounding text.
Description of "Figure 26-5 By Week Number Clause Set to Second To Last Week of the Year"

The By Week Number clause follows the ISO-8601 standard, which defines the week as starting with Monday and ending with Sunday. Also, the first week of a year is defined as the week containing the first Thursday of the Gregorian year and containing January 4th.

Using this standard, a calendar year can have 52 or 53 weeks. Part of week 1 may be in the previous calendar year. Part of week 52 may be in the following calendar year. If a year has a week 53, part of it must be in the following calendar year.

As an example, in the year 1998, week 1 began on Monday December 29th, 1997. The last, week 53, ended on Sunday January 3rd, 1999. Therefore, December 29th, 1997, is in the first week of 1998 and January 1st, 1999, is in the 53rd week of 1998.

By Year Day

Use this clause to specify the day of the year as a number. A value of 1 equates to January 1st and 35 is February 4th. Valid values are and 1 to 366 and -366 to -1.

The negative values are useful for identifying the same dates year after year despite the occurrence of leap years. For example, the 60th day of the year is March 1st except for leap years when it is February 29th. To calculate the appropriate negative value, count backwards from the last day of the year. Therefore, the By Year Day for December 31st is -1. December 30th is -2. To define a schedule for every March 1st, despite leap years, set By Year Day to -306.

By Month Day

This clause specifies the day of the month as a number. Valid values are 1 to 31 and -1 to -31. An example is 10, which means the 10th day of the selected month. Use the minus sign (-) to count backward from the last day. For example, if you set the By Month Day clause to -1, the schedule runs on the last day of every month. A value of -2 runs the schedule on the next to last day of every month.

By Day

This clause specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on.

You can prefix the By Day values with positive and negative numbers. The numerical prefix you can use depends on the value you select for the Frequency Unit.

If you select Yearly as the frequency, you can prefix By Day with values that represent the weeks in a year, 1 to 53 and -53 to -1. Therefore, By Day set to 26Fri equates to the 26th Friday of the year. An entry of -1Mon when the frequency equals Yearly, equates to the last Monday of the year.

If you select Monthly as the frequency, you can prefix By Day with values that represent the weeks in a month, 1 to 5 and -5 to -1. In this case, an entry of -1Mon with frequency set to Monthly results in the last Monday of every month.

By Hour

This clause enables you to schedule by the hour. Valid values are 0 to 23 where 0 is midnight, 5 is 5 am, 13 is 1 pm, and 23 is 11 pm.

By Minute

Use this clause to schedule by the minute. Valid values are 0 to 59. As an example, 45 means 45 minutes past the hour.

By Second

Use this clause to schedule by the second. Valid values are 0 to 59. As an example, 30 means 30 seconds past the minute.

By Set Position

If your Oracle Database version is 10g Release 2 or higher, you can use this clause to schedule based on the position of items in a previously evaluated list of timestamps. Use other By clauses to return a list of timestamps. Then add the By Set Position clause to select one or more items from that list. It is useful for requirements such as running a job on the last workday of the month. Valid values are 1 through 9999. A negative number selects an item from the end of the list (-1 is the last item, -2 is the next to last item, and so on) and a positive number selects from the front of the list. This clause is always evaluated last and only once per frequency. The supported frequencies are MONTHLY and YEARLY.

Example Schedules

Use Table 26-1 as a guide for defining schedules.

Table 26-1 Example Repeat Expressions for Schedules

Schedule Description Frequency Units Repeat Every By Clause

Every Friday.

weekly

1 week

By Day = FRI

Every other Friday.

weekly

2 weeks

By Day = FRI

Last day of every month.

monthly

1 month

By Month Day = -1

Second-to-last day of every month.

monthly

1 month

By Month Day = -2

First Friday of any month containing 5 weeks.

monthly

1 month

By Day = -5FRI

Last workday of every month.

monthly

1 month

By Day = MON,TUE,WED,THU,FRI;

By Set Pos = -1

On March 10th.

yearly

1 year

By Month = MAR

By Month Day = 10

Every 12 days.

daily

12 days

n/a

Every day at 8 am and 5 pm.

daily

1 day

By Hour = 8,17

On the second Wednesday of every month.

monthly

1 month

By Day = 2 WED

Every hour for the first three days of every month.

hourly

1 hour

By Month Day = 1,2,3


Editing a Schedule

Use the schedule editor to edit a schedule.

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.

For examples of schedules you can define, see Example Schedules.

Figure 26-6 The Schedule Editor

This illustration is described in the surrounding text.
Description of "Figure 26-6 The Schedule Editor"

Start and End Dates and Times

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.

When working in the wizard, click Next to view the next page.

When working in the schedule editor, the start date and time become the defaults for the By Clauses in the Repeat Expression. The execution time in Schedule Preview corresponds to the Start Time.

Defining Schedules To Repeat

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.

Frequency Unit

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.

Repeat Every

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

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.

Figure 26-7 Selector dialog box for Picking Months in a Year

This illustration is described in the surrounding text.
Description of "Figure 26-7 Selector dialog box for Picking Months in a Year"

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.

Figure 26-8 Month Clause for January and June

This illustration is described in the surrounding text.
Description of "Figure 26-8 Month Clause for January and June"

You can define the following by clauses:

By Month

This specifies in which month or months the schedule is valid. If you type in values, use numbers such as 1 for January and 3 for March, or use three-letter abbreviations such as FEB for February and JUL for July.

By Week Number

Only when you select Yearly for the Frequency Unit can you schedule by the week number in the year.

You can either type in values or click the Ellipsis button to view the selector dialog box. If you type in values, valid values include positive and negative integers from 1 to 52 or 53, depending on the year. For example, to set a schedule to run on the second to last week of the year, you can either type -2 or fill in the selector dialog box.

Figure 26-9 By Week Number Clause Set to Second To Last Week of the Year

This illustration is described in the surrounding text.
Description of "Figure 26-9 By Week Number Clause Set to Second To Last Week of the Year"

The By Week Number clause follows the ISO-8601 standard, which defines the week as starting with Monday and ending with Sunday. Also, the first week of a year is defined as the week containing the first Thursday of the Gregorian year and containing January 4th.

Using this standard, a calendar year can have 52 or 53 weeks. Part of week 1 may be in the previous calendar year. Part of week 52 may be in the following calendar year. If a year has a week 53, part of it must be in the following calendar year.

As an example, in the year 1998, week 1 began on Monday December 29th, 1997. The last, week 53, ended on Sunday January 3rd, 1999. Therefore, December 29th, 1997, is in the first week of 1998 and January 1st, 1999, is in the 53rd week of 1998.

By Year Day

Use this clause to specify the day of the year as a number. A value of 1 equates to January 1st and 35 is February 4th. Valid values are and 1 to 366 and -366 to -1.

The negative values are useful for identifying the same dates year after year despite the occurrence of leap years. For example, the 60th day of the year is March 1st except for leap years when it is February 29th. To calculate the appropriate negative value, count backwards from the last day of the year. Therefore, the By Year Day for December 31st is -1. December 30th is -2. To define a schedule for every March 1st, despite leap years, set By Year Day to -306.

By Month Day

This clause specifies the day of the month as a number. Valid values are 1 to 31 and -1 to -31. An example is 10, which means the 10th day of the selected month. Use the minus sign (-) to count backward from the last day. For example, if you set the By Month Day clause to -1, the schedule runs on the last day of every month. A value of -2 runs the schedule on the next to last day of every month.

By Day

This clause specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on.

You can prefix the By Day values with positive and negative numbers. The numerical prefix you can use depends on the value you select for the Frequency Unit.

If you select Yearly as the frequency, you can prefix By Day with values that represent the weeks in a year, 1 to 53 and -53 to -1. Therefore, By Day set to 26Fri equates to the 26th Friday of the year. An entry of -1Mon when the frequency equals Yearly, equates to the last Monday of the year.

If you select Monthly as the frequency, you can prefix By Day with values that represent the weeks in a month, 1 to 5 and -5 to -1. In this case, an entry of -1Mon with frequency set to Monthly results in the last Monday of every month.

By Hour

This clause enables you to schedule by the hour. Valid values are 0 to 23 where 0 is midnight, 5 is 5 am, 13 is 1 pm, and 23 is 11 pm.

By Minute

Use this clause to schedule by the minute. Valid values are 0 to 59. As an example, 45 means 45 minutes past the hour.

By Second

Use this clause to schedule by the second. Valid values are 0 to 59. As an example, 30 means 30 seconds past the minute.

By Set Position

If your Oracle Database version is 10g Release 2 or higher, you can use this clause to schedule based on the position of items in a previously evaluated list of timestamps. Use other By clauses to return a list of timestamps. Then add the By Set Position clause to select one or more items from that list. It is useful for requirements such as running a job on the last workday of the month. Valid values are 1 through 9999. A negative number selects an item from the end of the list (-1 is the last item, -2 is the next to last item, and so on) and a positive number selects from the front of the list. This clause is always evaluated last and only once per frequency. The supported frequencies are MONTHLY and YEARLY.