SAP SQL Anywhere

Question

For example, I would like to be able to do something like

CREATE EVENT LogNotifier
TYPE LogDiskSpace
WHERE event_condition( 'LogFreePercent' ) < (select DiskThreshold from Settings)
HANDLER
BEGIN
   MESSAGE 'LogNotifier message'
END;

I appreciate there are potential performance implications if one did something stupid - but that pretty well applies to anything :)

Accepted Answer

The conditions allowed in an event WHERE clause are limited for exactly that reason: performance. The WHERE clause applies only to system events, and these can potentially be triggered very frequently. By limiting what is allowed, the server can evaluate whether or not to fire the event without the overhead incurred by permitting arbitrarily complex expressions, and can do so without creating a temporary connection for the event. If you need to add more complex logic, add an IF statement at the start of the handler with a RETURN statement as its body.

Another alternative (which would be more appropriate for your example above) is to create the event dynamically using EXECUTE IMMEDIATE to incorporate the value from the subquery, and recreate it when the Settings table is changed. This would avoid creating a temporary connection and evaluating the query every time the log file grows.

Answer 2

4

You could take a differen approach by using the event GrowLog which is fired, if the log file shall be grown. At that point you are able to check the disk space based on a value from a select statement. Or if you have the feeling that this situation might be to late to check, you could create an event which runs every 10 minutes checking the diskspace in question.

permanent link

answered 17 Aug '10, 13:41

Martin
9.0k●127●165●257
accept rate: 14%

Thanks Martin - I see there are GrowDB and GrowTemp events as well which would cover the other disk monitoring events I have. However I think the easiest answer is using a trigger as described in my comments on Bruce's answer.

(17 Aug '10, 14:03) Justin Willey

flat view

SAP SQL Anywhere

Please add a way of using variables or database values in event trigger conditions

Follow this question

Related questions