Contents Index Introduction Defining schedules

ASA Database Administration Guide
  Automating Tasks Using Schedules and Events

Understanding schedules


By scheduling activities you can ensure that a set of actions is executed at a set of preset times. The scheduling information and the event handler are both stored in the database itself.

Although this is not usually necessary, you can define complex schedules by associating more than one schedule with a named event. For example, a retail outlet might want an event to occur once per hour during hours of operation, where the hours of operation vary based on the day of the week. You can achieve the same effect by defining multiple events, each with its own schedule, and by calling a common stored procedure.

The following examples give some ideas for scheduled actions that may be useful.

When scheduling events, you can use either full-length English day names (Monday, Tuesday, and so on) or the abbreviated forms of the day (Mon, Tue, and so on). Note that you must use the full-length English day names if you want the day names to be recognized by a server running in a language other than English.

For more information, see CREATE EVENT statement.

Examples 

Carry out an incremental backup daily at 1:00 am:

create event IncrementalBackup
schedule
   start time '1:00 AM' every 24 hours
handler
begin
   backup database directory 'c:\\backup'
   transaction log only
   transaction log rename match
end

Summarize orders at the end of each business day:

create event Summarize
schedule
   start time '6:00 pm'
   on ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday',
      'Friday' )
handler
begin
   insert into DBA.OrderSummary
      select max( date_ordered ),
             count( * ),
             sum( amount )
      from DBA.Orders
      where date_ordered = current date
end

Defining schedules

Contents Index Introduction Defining schedules