Defines an event and its associated handler for automating
predefined actions. Also defines scheduled actions.
Syntax
CREATE EVENT <event-name>
[ TYPE event-type
[ WHERE trigger-condition [ AND trigger-condition ], ...]
| SCHEDULE schedule-spec, … ]
…[ ENABLE | DISABLE ]
…[ AT { CONSOLIDATED | REMOTE | ALL } ]
…[ HANDLER
BEGIN
…
END ]
event-type - (back to Syntax)
BackupEnd
| “Connect”
| ConnectFailed
| DatabaseStart
| DBDiskSpace
| “Disconnect”
| GlobalAutoincrement
| GrowDB
| GrowLog
| GrowTemp
| IQMainDBSpaceFree
| IQTempDBSpaceFree
| LogDiskSpace
| “RAISERROR”
| ServerIdle
| TempDiskSpace
trigger-condition - (back to Syntax)
event_condition( <condition-name> )
{ =
| <
| >
| !=
| <=
| >= } <value>
schedule-spec - (back to Syntax)
[ <schedule-name> ]
{ START TIME <start-time> | BETWEEN <start-time> AND <end-time> }
[ EVERY <period> { HOURS | MINUTES | SECONDS } ]
[ ON { ( <day-of-week>, … ) | ( <day-of-month>, … ) } ]
[ START DATE <start-date> ]
Parameters
(back to top)
- event-name an event has a creator, which is the user creating the event, and the
event handler executes with the permissions of that creator. This is the same as stored
procedure execution. You cannot create events owned by other users. You can list event
names by querying the system table SYSEVENT. For
example:
SELECT event_id, event_name FROM SYS.SYSEVENT
- event-type one of a set of system-defined event types. The event types are
case-insensitive. To specify the conditions under which this
<event-type> triggers the event, use the WHERE clause.
- DiskSpace – if the database contains an event handler for one
of the DiskSpace types, the database server checks the available space on each
device associated with the relevant file every 30 seconds.
In the event the
database has more than one dbspace, on separate drives, DBDiskSpace checks each
drive and acts depending on the lowest available space.
- LogDiskSpace – checks the location of the transaction log and
any mirrored transaction log, and reports based on the least available space.
- Globalautoincrement – fires when the GLOBAL AUTOINCREMENT
default value for a table is within one percent of the end of its range. A typical
action for the handler could be to request a new value for the GLOBAL_DATABASE_ID
clause.
You can use the EVENT_CONDITION function with
RemainingValues as an argument for this event type.
- ServerIdle – if the database contains an event handler for
the ServerIdle type, the server checks for server activity every 30 seconds.
- WHERE clause the trigger condition determines the condition under which an event is
fired. For example, to take an action when the disk containing the transaction log
becomes more than 80% full, use this triggering
condition:
...
WHERE event_condition( 'LogDiskSpacePercentFree' ) < 20
...
The
argument to the EVENT_CONDITION function must be valid for the event type. You can use
multiple AND conditions to make up the WHERE clause, but you cannot use OR conditions or
other conditions.
- SCHEDULE specifies when scheduled actions are to take place. The sequence of times
acts as a set of triggering conditions for the associated actions defined in the event
handler.You can create more than one schedule for a given event and its associated
handler. This permits complex schedules to be implemented. While it is compulsory to
provide a schedule name when there is more than one schedule, it is optional if you
provide only a single schedule.
You can list schedule names by querying the system
table SYSSCHEDULE. For example:
SELECT event_id, sched_name FROM SYS.SYSSCHEDULE
Each event has a unique event ID. Use the event_id columns of
SYSEVENT and SYSSCHEDULE to match the event to the
associated schedule.
When a nonrecurring scheduled event has passed, its
schedule is deleted, but the event handler is not deleted.
Scheduled event times
are calculated when the schedules are created, and again when the event handler
completes execution. The next event time is computed by inspecting the schedule or
schedules for the event, and finding the next schedule time that is in the future. If
an event handler is instructed to run every hour between 9:00 and 5:00, and it takes
65 minutes to execute, it runs at 9:00, 11:00, 1:00, 3:00, and 5:00. If you want
execution to overlap, you must create more than one event.
The subclauses of a
schedule definition are as follows:
- START DATE – the date on which scheduled events are to
start occurring. The default is the current date.
- START TIME – the first scheduled time for each day on which
the event is scheduled. If a START DATE is specified, the START TIME refers to
that date. If no START DATE is specified, the START TIME is on the current day
(unless the time has passed) and each subsequent day.
- BETWEEN … AND – a range of times during the day outside of
which no scheduled times occur. If a START DATE is specified, the scheduled times
do not occur until that date.
- EVERY – an interval between successive scheduled events.
Scheduled events occur only after the START TIME for the day, or in the range
specified by BETWEEN …AND.
- ON – a list of days on which the scheduled events occur.
The default is every day. These can be specified as days of the week or days of
the month.
Days of the week are Monday, Tuesday, and so on. The abbreviated
forms of the day, such as Mon, Tue, and so on, may also be used. The database
server recognizes both full-length and abbreviated day names in any of the
languages supported by SAP IQ.
Days of the
month are integers from 0 to 31. A value of 0 represents the last day of any
month.
Each time a scheduled event handler is completed, the next scheduled time
and date is calculated.
- If the EVERY clause is used, find whether the next scheduled time falls on the
current day, and is before the end of the BETWEEN …AND range. If so, that is the
next scheduled time.
- If the next scheduled time does not fall on the current day, find the next date
on which the event is to be executed.
- Find the START TIME for that date, or the beginning of the BETWEEN … AND
range.
- ENABLE | DISABLE by default, event handlers are enabled. When DISABLE is specified, the
event handler does not execute even when the scheduled time or triggering condition
occurs. A TRIGGER EVENT statement does not cause a disabled event handler to be
executed
- AT to execute events at remote or consolidated databases in a SQL Remote
setup, use this clause to restrict the databases at which the event is handled. By
default, all databases execute the event.
- HANDLER each event has one handler. Like the body of a stored procedure, the
handler is a compound statement. There are some differences, though: you can use an
EXCEPTION clause within the compound statement to handle errors, but not the ON
EXCEPTION RESUME clause provided within stored procedures.
Examples
(back to top)
- Example 1 instructs the database server to carry out an automatic incremental
backup daily at 1 a.m.:
CREATE EVENT IncrementalBackup
SCHEDULE
START TIME '1:00AM' EVERY 24 HOURS
HANDLER
BEGIN
BACKUP DATABASE INCREMENTAL
TO 'backups/daily.incr'
END
- Example 2 instructs the database server to call the system stored procedure
sp_iqspaceused every 10 minutes, then store in a
table the returned current date and time, the current number of connections to the
database, and current information about the use of main and temporary IQ store:
CREATE TABLE mysummary(dt DATETIME,
users INT, mainKB UNSIGNED BIGINT,
mainPC UNSIGNED INT,
tempKB UNSIGNED BIGINT,
tempPC UNSIGNED INT) ;
CREATE EVENT mysummary
SCHEDULE sched_mysummary
START TIME '00:01 AM' EVERY 10 MINUTES
HANDLER
BEGIN
DECLARE mt UNSIGNED BIGINT;
DECLARE mu UNSIGNED BIGINT;
DECLARE tt UNSIGNED BIGINT;
DECLARE tu UNSIGNED BIGINT;
DECLARE conncount UNSIGNED INT;
SET conncount = DB_PROPERTY('ConnCount');
CALL SP_IQSPACEUSED(mt,mu,tt,tu);
INSERT INTO mysummary VALUES( NOW(),
conncount, mu, (mu*100)/mt, tu,
(tu*100)/tt );
END;
- Example 3 posts a message to the server log when free disk space on the device
containing the transaction log file falls below 30 percent, but execute the handler no
more than once every 300 seconds.
CREATE EVENT LowTxnLogDiskSpace
TYPE DBDiskSpace
WHERE event_condition( 'DBFreePercent' ) < 30
AND event_condition( 'Interval' ) >= 300
HANDLER
BEGIN
message 'Disk space for Transaction Log is low.';
END;
Usage
(back to top)
An event definition includes two distinct pieces. The trigger condition can be an
occurrence, such as a disk filling up beyond a defined threshold. A schedule is a set of
times, each of which acts as a trigger condition. When a trigger condition is satisfied, the
event handler executes. The event handler includes one or more actions specified inside a
compound statement (BEGIN... END).
If no trigger condition or schedule specification is supplied, only an explicit
TRIGGER EVENT statement can trigger the event. During development, you
might want to develop and test event handlers using TRIGGER EVENT and add
the schedule or WHERE clause once testing is complete.
Event errors are logged to the database server console.
When event handlers are triggered, the server makes context information, such as the
connection ID that caused the event to be triggered, available to the event handler using
the EVENT_PARAMETER function.
Note
Although statements that return result sets are disallowed in events, you can allow an
event to call a stored procedure and insert the procedure results into a temporary
table.
Side Effects:
- Automatic commit.
- The actions of an event handler are committed if no error is detected during
execution, and rolled back if errors are detected.
Standards
(back to top)
- SQL—Vendor extension to ISO/ANSI SQL grammar.
- SAP Database products—Not supported by SAP ASE.
Permissions
(back to top)
Requires
one of:
- MANAGE ANY EVENT system privilege.
- CREATE ANY OBJECT system privilege.
Event handlers execute on a separate connection, with the privileges of
the event owner. To execute with privileges other than
MANAGE
ANY EVENT system privilege, you can call a procedure from within the event handler: the
procedure executes with the permissions of its owner.