| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
This chapter contains the following topics:
Figure 28-1 shows the schedule editor with the Start and End Dates and Times at the top of the editor.
The repeat expression appears in the lower left panel. 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 in Warehouse Builder, see Example Schedules.
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 the Schedule Editor, the start date and time become the defaults for the By Clauses in the Repeat Expression. Notice in Figure 28-1 that the execution time in Schedule Preview corresponds to the Start Time.
The repeat expression determines how often Warehouse Builder executes the schedule. 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 ByClauses are not available. After you complete the wizard, you can open the schedule and set the ByClauses 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 or Immediately.
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, Warehouse Builder evaluates the schedule 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, Warehouse Builder evaluates the schedule 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 such as shown in Figure 28-2. If your goal is to know how to quickly type in values, first use the selector dialogs to learn what values are valid and also refer to Example Schedules.
Figure 28-2 Selector Dialog for Picking Months in a Year
When you use the selector dialogs and select OK, Warehouse Builder displays the results in the schedule editor as shown in Figure 28-3. In this way, you can use the selector dialogs to learn what values are valid.
Figure 28-3 Month Clause for January and June
You can define the following by clauses:
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.
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. 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 as shown in Figure 28-2.
Figure 28-4 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.
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.
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.
This 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.
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.
Use this clause to schedule by the minute. Valid values are 0 to 59. As an example, 45 means 45 minutes past the hour.
Use this clause to schedule by the second. Valid values are 0 to 59. As an example, 30 means 30 seconds past the minute.
If your Oracle Database is version 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 in each frequency. The supported frequencies are MONTHLY and YEARLY.
Use Table 28-1 as a guide for defining schedules.
Table 28-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 |
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
