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 :)

asked 17 Aug '10, 12:23

Justin%20Willey's gravatar image

Justin Willey
6.7k108141208
accept rate: 20%

3

That raises the question "how often should SQL Anywhere evaluate the WHERE clause?" Previously it only had to watch the event_condition for any change, but now the event could be fired even if the event_condition remained static. Perhaps a SCHEDULE clause could be used to specify "how often?"

(17 Aug '10, 13:16) Breck Carter

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.

permanent link

answered 17 Aug '10, 13:39

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

Thanks Bruce, the dynamic gives me an idea - I put a trigger on the setting that re-creates the events whenever the threshold changes.

(17 Aug '10, 13:59) Justin Willey

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's gravatar image

Martin
8.6k116151237
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×107
×35
×9

question asked: 17 Aug '10, 12:23

question was seen: 1,674 times

last updated: 17 Aug '10, 13:41