Question: How can I be notified when user activity drops to zero?
Answer: It's one thing to measure user activity, quite another to determine when it has actually dropped to zero for any length of time.
One method is to code a SQL Anywhere 16 scheduled EVENT that checks the TimeWithoutClientConnection database property every once in a while, then calls xp_sendmail() when the value gets too large.
You won't find TimeWithoutClientConnection in the GA version of SQL Anywhere 16, but it is available for download as part of the 16.0.0.1512 Update or later:
SQL Anywhere - Server ================(Build #1473 - Engineering Case #734038)================ The database property TimeWithoutClientConnection has been added. The description for this database property is: Returns the elapsed time in seconds since a CmdSeq or TDS client connection to the database existed. If there has not been a CmdSeq or TDS connection since the database started then the time since the database started is returned. If one or more CmdSeq or TDS connections are currently connected, 0 is returned. |
You also won't find TimeWithoutClientConnection in the Help yet, but chances are the above description is all you're ever gonna get anyway.
Plus this, here...
|
- in general here: Task automation using schedules and events
- and more specifically here: CREATE EVENT statement
- in the Help: xp_sendmail system procedure
- in this how-to post: Sending Email From SQL Anywhere 12 Via Google's SMTP Server
- and in section "3. SMTP Email Enhancements" of this article: Top 10 Cool New Features in SAP Sybase SQL Anywhere 16
CREATE EVENT lonely
SCHEDULE
START TIME '00:00:00'
EVERY 5 SECONDS
HANDLER BEGIN
DECLARE @seconds_without_client_connection BIGINT;
DECLARE @email_sent_at TIMESTAMP;
DECLARE @current_timestamp TIMESTAMP DEFAULT CURRENT TIMESTAMP;
DECLARE @loneliness_threshold_in_seconds BIGINT DEFAULT 30;
DECLARE @email_repeat_threshold_in_seconds BIGINT DEFAULT 20;
DECLARE @return_code INTEGER;
SET @seconds_without_client_connection
= CAST ( COALESCE ( DB_PROPERTY ( 'TimeWithoutClientConnection' ), '0' ) AS BIGINT );
IF @seconds_without_client_connection = 0 THEN
UPDATE lonely SET email_sent_at = '1900-01-01 00:00:00';
COMMIT;
ELSE
IF @seconds_without_client_connection >= @loneliness_threshold_in_seconds THEN
SELECT email_sent_at
INTO @email_sent_at
FROM lonely;
IF DATEDIFF ( SECOND, @email_sent_at, @current_timestamp )
>= @email_repeat_threshold_in_seconds THEN
UPDATE lonely SET email_sent_at = @current_timestamp;
COMMIT;
@return_code = CALL xp_startsmtp (
smtp_sender = 'Your.Name@gmail.com',
smtp_server = 'smtp.gmail.com',
smtp_port = 587,
timeout = 60,
smtp_sender_name = 'Your Database',
smtp_auth_username = 'Your.Name@gmail.com',
smtp_auth_password = 'Your Password',
trusted_certificates = 'cert_name=Equifax_Secure_Certificate_Authority' );
IF @return_code = -1 THEN
MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_startsmtp ',
xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
RETURN;
END IF;
@return_code = CALL xp_sendmail (
recipient = 'Breck.Carter@gmail.com',
subject = 'I''m lonely!',
"message" = STRING ( 'Nobody''s connected with me in the past ',
@seconds_without_client_connection,
' seconds.' ) );
IF @return_code = -1 THEN
MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_sendmail: ',
xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
END IF;
@return_code = CALL xp_stopsmtp();
IF @return_code = -1 THEN
MESSAGE STRING ( CURRENT TIMESTAMP, ' ERROR - xp_stopsmtp: ',
xp_get_mail_error_code(), ' - ', xp_get_mail_error_text() ) TO CONSOLE;
END IF;
END IF;
END IF;
END IF;
END;
- The CREATE EVENT statement on lines 1 through 5 schedules the event to start firing right away, and forever after, every 5 seconds.
- The SET statement on line 14 and 15 captures TimeWithoutClientConnection, and the UPDATE on line 19 handles the case where current connections do exist (TimeWithoutClientConnection = 0) by recording in the database that no "I'm lonely!" email has been sent; i.e., '1900-01-01 00:00:00' effectively means 'never'.
- The IF statement starting on line 24 detects loneliness, and the nested IF starting on line 30 determines if it's time to send an email.
- If it's been a while since the previous email was sent, the fact that (another) one is now being sent is recorded by the UPDATE on line 33,
- and the big chunk of code on lines 36 through 69 does the actual work, using the techniques described in Sending Email From SQL Anywhere 12 Via Google's SMTP Server and in section "3. SMTP Email Enhancements" of this article: Top 10 Cool New Features in SAP Sybase SQL Anywhere 16.
- First, the trusted_certificates = 'cert_name=Equifax_Secure_Certificate_Authority' argument makes use of a new feature in SQL Anywhere 16, the CREATE CERTIFICATE statement:
CREATE OR REPLACE CERTIFICATE Equifax_Secure_Certificate_Authority FROM FILE 'C:\\certificates\\Equifax_Secure_Certificate_Authority.cer';
- Second, the lonely table, which follows the example shown in One Row Only:
CREATE TABLE lonely ( one_row_only INTEGER NOT NULL DEFAULT 1 CHECK ( one_row_only = 1 ), email_sent_at TIMESTAMP NOT NULL DEFAULT '1900-01-01 00:00:00', PRIMARY KEY ( one_row_only ) ); CREATE TRIGGER one_row_only BEFORE DELETE ON lonely FOR EACH ROW BEGIN ROLLBACK TRIGGER WITH RAISERROR 99999 'Do not attempt to delete from lonely'; END; INSERT lonely DEFAULT VALUES; COMMIT;
If you run it without first changing any of the placeholders like 'Your.Name@gmail.com', here's what you'll see in the dbsrv16.exe -o file:
I. 07/14 12:59:23. 2013-07-14 12:59:23.217 ERROR - xp_startsmtp 534 - 5.7.9 Please log in with your web browser and then try again. Learn more at 5.7.9 https://support.google.com/mail/bin/answer.py?answer=78754 ri10sm12739973igc.1 - gsmtp |
When events cough up and crash there's no "client" to receive the error message, so it has to go to the server console... but that's ok, every single engine startup command includes the -o filespec.txt option, right? :)
No comments:
Post a Comment