Wednesday, December 20, 2017

Sending Emails Via SendGrid SMTP Relay

Once upon a time, it was possible to send emails from inside SQL Anywhere by using xp_sendmail with Google's free smtp.gmail.com service.

It may still be possible, but apparently not for me... all I get is return code 6 (Secure connection error) after clicking on Send Test Alert Email in Foxhound:

Email test result: SMTP start test AutoDrop email failed with return code 6 when sending 
  "Foxhound Test AutoDrop Email (2017-12-01 09:58:38)". Secure connection error
There are a number of alternatives to Google's free SMTP email server; SendGrid is one of them, here's how to sign up:
  • Go to https://sendgrid.com/.

  • Click on Try it for free.

  • Provide a user name and password, a real email address and some other information.

  • Choose SMTP Relay (you're going to use SendGrid's API via SQL Anywhere's xp_sendmail() procedure).

  • Specify a value for API key and receive a big-long-ugly-generated-password in return.

  • Note the following values (you're going to use port 25):
    Server   smtp.sendgrid.net
    Ports 
    25, 587  (for unencrypted/TLS connections)
    465      (for SSL connections)
    Username apikey
    Password big-long-ugly-generated-password
    
Here's how to set up Foxhound's Alert Email feature to use the SendGrid SMTP Relay:
  • Foxhound Options page - 2. Global Email Settings
    Check Use SMTP to send emails 
    SMTP Sender:  your.email@whatever.com
    SMTP Server:  smtp.sendgrid.net
    SMTP Port:    25
    SMTP Timeout: 60
    SMTP Authorization User Name: apikey
    SMTP Authorization Password:  big-long-ugly-generated-password 
    SMTP Certificate Filespec:    [leave empty] 
    

  • Monitor Options page - 7. Email Setup for [Default Settings]
    Use HTML in emails:             [checked] 
    Host[:port] for URLs in emails: localhost
    Send Alert emails:              [unchecked]
    Email address(es) for Alerts:   your.email@whatever.com
    Click on Send Test Alert Email  Test result: OK
    
Here's how to use the SendGrid SMTP Relay in your own SQL:
BEGIN
DECLARE @return_code           INTEGER;
DECLARE @smtp_sender           LONG VARCHAR;
DECLARE @smtp_server           LONG VARCHAR; 
DECLARE @smtp_port             INTEGER; 
DECLARE @timeout               INTEGER;
DECLARE @smtp_sender_name      LONG VARCHAR;
DECLARE @smtp_auth_username    LONG VARCHAR; 
DECLARE @smtp_auth_password    LONG VARCHAR;
DECLARE @trusted_certificates  LONG VARCHAR;
DECLARE @recipient             LONG VARCHAR;
DECLARE @subject               LONG VARCHAR;
DECLARE @message               LONG VARCHAR;

SET @smtp_sender          = 'your.email@whatever.com';
SET @smtp_server          = 'smtp.sendgrid.net'; 
SET @smtp_port            = 25;   -- 25 for standard SMTP, 465 for SSL, or 587 for TLS
SET @timeout              = 60;   -- default is 60 seconds
SET @smtp_sender_name     = 'Foxhound';
SET @smtp_auth_username   = 'apikey';
SET @smtp_auth_password   = 'big-long-ugly-generated-password';
SET @trusted_certificates = NULL;
SET @recipient            = 'your.email@whatever.com';
SET @subject              = STRING ( 'sendgrid test subject at ', CURRENT TIMESTAMP );
SET @message              = STRING ( 'sendgrid test message at ', CURRENT TIMESTAMP );

@return_code = CALL xp_startsmtp ( 
   smtp_sender          = @smtp_sender,  
   smtp_server          = @smtp_server,  
   smtp_port            = @smtp_port,  
   timeout              = @timeout,
   smtp_sender_name     = @smtp_sender_name,
   smtp_auth_username   = @smtp_auth_username, 
   smtp_auth_password   = @smtp_auth_password,
   trusted_certificates = @trusted_certificates );

MESSAGE STRING ( 'xp_startsmtp @return_code = ', @return_code ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_code() = ',  xp_get_mail_error_code()      ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT;

@return_code = CALL xp_sendmail ( 
   recipient     = @recipient,  
   subject       = @subject,  
   "message"     = @message );

MESSAGE STRING ( 'xp_sendmail @return_code = ', @return_code ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_code() = ',  xp_get_mail_error_code()      ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT;

@return_code = CALL xp_stopsmtp();

MESSAGE STRING ( 'xp_stopsmtp @return_code = ', @return_code ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_code() = ',  xp_get_mail_error_code()      ) TO CLIENT;
MESSAGE STRING ( 'xp_get_mail_error_text() = "', xp_get_mail_error_text(), '"' ) TO CLIENT;

EXCEPTION WHEN OTHERS THEN
   CALL xp_stopsmtp();

END;
Here's the MESSAGE ... TO CLIENT output:
xp_startsmtp @return_code = 0
xp_get_mail_error_code() = 235
xp_get_mail_error_text() = "Authentication successful "
xp_sendmail @return_code = 0
xp_get_mail_error_code() = 250
xp_get_mail_error_text() = "Mail queued for delivery. "
xp_stopsmtp @return_code = 0
xp_get_mail_error_code() = 250
xp_get_mail_error_text() = "Mail queued for delivery. "
Here's what the email looks like:




Wednesday, November 15, 2017

Top 10 Cool New Features In Foxhound 4

Version 4 of the Foxhound Database Monitor has been around for a while, long enough for five patches and a new build... long enough for a Top 10 listicle about the best new features:

1. Foxhound Is The Book


Once upon a time (2004) there was a book about SQL Anywhere 9.

Then the book got old and the questions started:
Q: When are you going to write a book about SQL Anywhere Version 11? I really like the Version 9 book.
Eventually (2009) the questions were answered:
A: This blog is that book.
Now, the answer has changed: Foxhound is that book... Foxhound is where the effort's going, especially in the Help.

For example,
  • dozens of new "Performance Tips" have been added,

  • links between related Monitor and Monitor Options topics have been added; for example, between the Cache statistic and Alert #19 Cache size, and

  • the source of each performance statistic is documented; e.g., "The Cache and % of Max fields are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level CurrentCacheSize and MaxCacheSize properties."

2. It's Easy To Open And Close The Book.


In Foxhound 3 the Help was always on display.

The bad news was, the Help was always on display, always taking up room on the screen.

Sure, you could turn off the Help, but that was hard, and once you turned it off it stayed invisible until you turned it on again.

In Foxhound 4 it's easy to close the Help by clicking X, and easy to open again by clicking any of the context-sensitive ? icons... no more checking/unchecking Show Help over on the Foxhound Options page and then having to click refresh.
The Help content is a big part of Foxhound (see Foxhound Is The Book above), now the Help interface is a bit easier to use.

3. Banner Warnings About Unintended Consequences


Every target database has its own full set of Monitor Options in Foxhound, making "options management" the hardest part of using Foxhound.

Couple that with the unintended consequences of "getting it wrong" when you set up conflicts between different options, and "hardest" becomes "really hard". For example, there are no less than five different ways you can accidentally disable the Alert Email Schedule feature.

To combat this problem Foxhound 4 now displays white-on-black banner warnings to immediately flag potential conflicts:
 This Alert Email Schedule has no effect when the Enable Emails checkbox is unchecked (see 1. Global Overrides). 

 This Alert Email Schedule has no effect when the Enable Schedules checkbox is unchecked (see 1. Global Overrides). 

 This Alert Email Schedule has no effect when the Send Alert Emails checkbox is unchecked (see 7. Email Setup).  

 This Alert Email Schedule has no effect when the Alert Email Address(es) field is empty (see 7. Email Setup). 

 Only Alert #1 emails are sent when Ping-Only Sampling is performed (see 16. Ping Settings). 

4. Drop-Down List Boxes Everywhere


It used to be a real chore to switch from one target database to another on the Monitor Options page. Foxhound 4 fixed that with drop-down list boxes spread all over the Monitor Options page that let you switch from target to target.

...but that's not the only place these drop-down list boxes appear.
They let you quickly change from one target to another on the Monitor page itself, and on the Sample History page:
That's what makes this a Cool New Feature: Those list boxes were originally designed just for the Monitor Options page, but it made perfect sense to add them to other pages as well.

5. Disable All Alerts


Sometimes it makes sense to enable only one single Alert when looking for a problem. The new Disable All Alerts buttons on the Monitor Option page make that easy: Turn off all 34 alerts, all at once, and then turn one back on.

Two other buttons were added as well: Enable All Alerts to turn them all back on, and Undo All Alerts to recover from an "Oops!"

6. Support Continues For Adhoc Queries


Unlike the builtin SQL Anywhere Monitor, Foxhound lets you run ISQL queries against all the historical data pertaining to your databases: "It's your data, you own it" is one of the Hallmarks of Foxhound.

Foxhound 4 introduced a whole new Adhoc Queries chapter in the Help, and has added the ID column to the Monitor tab on the Foxhound Menu page so you can see which sampling_id value to use in your queries.

7. Faster Patches


Patches are easier to build for Foxhound 4, and that has made it possible to publish five patches since February.
Patches are popular because (a) you can pick and choose which patch(es) to apply depending of your needs, and (b) patches are fast because you don't have to reinstall Foxhound and upgrade the database.

8. Disk-Versus-Cache Usage


Disk/Cache statistics are displayed for both table and index data, with anything over 1% for extended periods of time indicating a possible performance bottleneck.
These percentages are new to Foxhound 4, and are calculated from the DiskReadTable, CacheReadTable and four other SQL Anywhere statistics that are [cough] less-than-humanly readable.

9. Monitor The Ability To Accept New Connections


Sometimes a SQL Anywhere database loses the ability to accept new connections. Foxhound 3 didn't detect that problem because once it was connected to the target database, it stayed connected.

Foxhound 4 introduced a separate "ping" process that tests the target database's ability to accept new connections.

Also, the time required to make each new ping connection has been added as a third measurement of response time, along with Heartbeat and Sample times.

10. Monitor The Arbiter Server


Foxhound's ability to monitor the Arbiter server in a SQL Anywhere High Availability setup was an unexpected new feature of version 4. It was made possible by the new Ping-Only Sampling feature, and is documented in this blog post.



Sunday, October 29, 2017

Monitor The Arbiter Server With Foxhound

Question: How do I monitor a SQL Anywhere High Availability Arbiter server with Foxhound 4?

Answer: Gosh, how hard can that be?

After all, dbping has no problem connecting to an arbiter server:

"%SQLANY17%\bin64\dbping.exe" -c "SERVER=ARBITER_PROD; HOST=localhost:55501;"

SQL Anywhere Server Ping Utility Version 17.0.7.3399
Ping server successful.
Foxhound 4 uses ODBC to connect, so all we have to do is add a DRIVER= to the connection string, right?
SERVER=ARBITER_PROD; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:10:36 PM  2m 21s  -- Invalid user ID or password -- 
2:09:11 PM          Alert #1: Database unresponsive.  Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:08:15 PM    5.2s  -- Invalid user ID or password at -- 
OK, so Foxhound can't just ping a server, it needs a database, so let's specify DBN=utility_db in the connection string.
Tip: You have to specify the -su option when starting the arbiter server if you want to connection via the utility database: e.g., dbsrv16 -su sql or dbsrv17 -su sqlsql. Why sqlsql? Because passwords have to be 6 characters long in SQL Anywhere 17.
But! ... it still doesn't work!
SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:19:13 PM  1m .2s  -- Permission denied: you do not have permission to execute a statement of this type -- 
2:19:09 PM          Alert #1: Database unresponsive.  Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:18:13 PM    5.1s  -- Permission denied: you do not have permission to execute a statement of this type at -- 
One last change is needed: Use the Monitor Options page to tell Foxhound to use the new Ping-Only Sampling feature:
Finally! Foxhound connects to the arbiter server and displays "Ping OK":
SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native; 

2:32:53 PM  20.2s  -- Ping-only sampling -- Ping OK 
2:32:33 PM            All Clear  - Alert #1: Database unresponsive. Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled. 
2:32:33 PM    10s  -- Ping-only sampling at -- 
When you directly monitor the arbiter server with a separate Foxhound sampling session, you can get Alert #1 messages sent to you when the arbiter goes down:
The other High Availability sampling sessions (primary, secondary, partner) also tell you about troubles with the arbiter, but that's not the same as Alert #1:

Overview - How To Monitor The Arbiter Server With Foxhound

  • Start the arbiter server with the -su option to enable connections via the utility database; e.g., dbsrv16 -su sql or dbsrv17 -su sqlsql

  • Specify the utility database in the Foxhound connection string; e.g., SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native;

  • Check Perform Ping-Only Sampling on the Foxhound Monitor Options page for the arbiter server.
Tip: If you are using the original Foxhound 4 build 4729, consider upgrading to build 4740, or at least applying Patch 8 to build 4728, in order to fix problems affecting Ping-Only Sampling.


Monday, October 9, 2017

How To Shrink Foxhound

Question: How do I shrink and reorganize the Foxhound 4 database?

Answer: Foxhound uses SQL Anywhere for its database, and the only way to shrink the size of a SQL Anywhere database file is to unload, recreate and reload the file from scratch. The good news is, that whole process is automatic when you reinstall Foxhound on an existing installation:

  • First, Foxhound creates a new, empty copy of the database file.

  • Second, Foxhound finds the old database file,

  • then it asks you how much of the old data you want to copy to the new database: some, all, none, just the options.

  • Finally, Foxhound copies and loads the data, using just enough space to hold it all.
In this context, "shrink" means "don't copy the empty space, and don't allocate space for data that isn't copied", and "reorganize" means the copy process implicitly organizes the data as it is loaded.

Here's the step-by-step process:

Step 1: Start Foxhound 4

...if it isn't running already, that is.

In this example, the Foxhound 4 database has grown quite large:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/02/2017  07:31 PM    37,521,506,304 foxhound4.db

Step 2: Decide How Much Data To Copy

Let's say you want to save half the data, thus shrinking the file by 50%.

Foxhound doesn't understand "half", but it does understand "only copy samples recorded after yyyymmdd".

To convert "half" into "yyyymmdd", start ISQL and run this query:
All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL

SELECT CAST ( SYSTAB.count * 0.50 AS INTEGER ) AS half
  FROM SYSTAB
 WHERE SYSTAB.table_name = 'rroad_sample_set';

       half 
----------- 
     867871 

-- The space used by the foxhound4.db file is more-or-less determined by the number 
-- of rows in the rroad_sample_set table, which contains one row for each sample
-- sample recorded by the Foxhound Monitor.

-- In other words, the "half" calculated here is "half the samples", not "half the bytes".
Now use "half" as the START AT value in this query:
SELECT TOP 1 START AT 867871
       DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd
  FROM sample_header
 ORDER BY sample_header.sample_set_number;

yyyymmdd 
-------- 
20170808 

-- The sample_finished_at column is used to turn the row number 867871 into a yyyymmdd date.
-- The sample_header view is used because Foxhound only allows SELECT statements on the 
-- adhoc query views (sample_header), not the underlying tables (rroad_sample_set).

Step 3: Run Foxhound's "Unsetup"

You don't have to stop Foxhound to reinstall the software, but you do have to run the special "unsetup" process to prepare for the reinstallation:
All Programs - Foxhound4 - Tools - Unsetup Foxhound

-- The "unsetup" process gets rid of the Windows shortcuts and other items, 
-- but it leaves the Foxhound database alone... and even leaves it running.

Step 4: Run The Foxhound 4 Setup Up To The PLEASE READ THIS

In this example, the new Foxhound 4 build 4740 is used... run it all the way to the PLEASE READ THIS window:
Foxhound-Version-4-0-4740-setup.exe


Step 5: Enter The FOXHOUND4UPGRADE=yyyymmdd Value

You can copy and paste the FOXHOUND4UPGRADE=yyyymmdd value, and press Enter twice to continue:

Step 6: Wait... and Wait... and Wait Some More

It takes quite a while for 35G of data to be copied and loaded, even half of 35G.


Eventually, the upgrade process will finish, and shut down, and Foxhound 4 will be restarted with the newer, smaller, reorganized database:
 Directory of C:\ProgramData\RisingRoad\Foxhound4

10/06/2017  07:25 PM    21,720,817,664 foxhound4.db
That's 42% smaller that before... not exactly half, but it meets the "more-or-less" standard :)

Thursday, October 5, 2017

New Foxhound 4 Build 4740

A new build of Foxhound 4 is available here.

  • You can use it to upgrade an existing copy of Foxhound 4 for free.

  • You can also use it to install a new copy of Foxhound 4, or

  • to upgrade an existing copy of Foxhound 1, 2 or 3.
If you are already using Foxhound 4, here's why you should consider upgrading to build 4740:
  • Build 4740 "rolls up" all five patches that were previously released for the original Foxhound 4 build 4729.

  • Several performance problems have been fixed, making it less likely that Foxhound will become unresponsive when the database grows very large.

  • If you want to use the "Reinstall Foxhound" method to reorganize and shrink the Foxhound database, the new build 4740 will let you do that... unlike the previous Patch 10 which made it impossible to preserve any data if you subsequently reinstalled the original build 4729.

  • Foxhound's own purge process has been improved, making it more likely the purge will keep database growth under control... and this fix was not included in the previous patches.
On the other hand...
  • If you don't need any of changes in build 4740, there's no need to upgrade.

  • If you only need one or two of the fixes, applying a patch to to build 4729 is a lot quicker than upgrading to build 4740.
Either way, here's a tip...
Tip: Set the Purge Speed to 10 Fastest on section 6. Purge of the Foxhound Options page.

Do this whether or not you upgrade to build 4740, if your Foxhound database is growing rapidly.


Sunday, September 24, 2017

Latest Foxhound 4 Patches

Two new patches have been posted recently on the Foxhound 4 page:

[ Download Patch 3 ]  Fix "unable to load dll" messages:
 Could not load dynamic library
 Unable to load either rroad4.dll or rroad464.dll
[ Patch 3 Readme ]
[ Download Patch 4 ]  Fix Display Schema for billion-row tables:
 Value nnn out of range for destination
 [ Patch 4 Readme ]
[ Download Patch 5 ]   NEW Prevent Foxhound from becoming unresponsive
 when the Monitor cannot connect to a network target database
 and an endless loop of bogus error messages appears:
  Attempt to reload definition for event "rroad_monitor_sample_loop"
  failed due to concurrent event execution.
 [ Patch 5 Readme ]
[ Download Patch 8 ]  Prevent Foxhound from becoming unresponsive
 when Ping-Only Sampling is stopped and restarted, or
 when full sampling is attempted for an arbiter server.
 [ Patch 8 Readme ]
[ Download Patch 10 ]   NEW Prevent Foxhound from becoming unresponsive
 on startup, especially when the Foxhound database is very large.
 [ Patch 10 Readme ]

The two new patches are more likely to help Foxhound users than the others:

Patch 5 will help folks who are using recent builds of SQL Anywhere 16 and 17 to run Foxhound. In particular, it implements a workaround for a bug behavior change introduced after SQL Anywhere 16.0.0.2193 and SQL Anywhere 17.0.0.1359.

Patch 10 will help anyone with a Foxhound database that has grown into the multi-gigabyte range by decreasing the time it takes for Foxhound to start up. For example, here are the test results for a 1.4G database:
Before applying Patch 10:  Foxhound startup time: 10.8s
After:                     Foxhound startup time:  1.1s
That's a factor of 10, but... you might not notice a difference of only 9 seconds.

However, you will notice the effect with a larger Foxhound database. For example, here's what happened with a 35G database:
Before applying Patch 10:  Foxhound startup time: 7m 17s ...plus 30 minutes more for the Foxhound Menu to appear!
After:                     Foxhound startup time: 4.6s
If you see this message every time you start Foxhound 4, you probably need to apply Patch 10:
Foxhound was not available yet, probably because the Foxhound engine had not finished starting up.
It may be ready now, so click here to try again.

If the situation persists contact RisingRoad.
If that message goes away after a long while, replaced by a blank page, and you still don't see the Foxhound menu appear, you definitely need Patch 10... especially if Foxhound's instance of dbsrv16.exe is using up this much CPU:

The "Foxhound startup time: 7m 17s" only tells part of the story in the example above... after Foxhound finished starting up, it took a further 30 minutes before the menu page appeared!

How Patch 10 Was Created...
Step 1: Find the Queries From Hell


The first step was to start SQL Anywhere's Procedure Profiler as soon as Foxhound started executing, by adding this statement to DatabaseStart event script:
CALL sa_server_option ( 'ProcedureProfiling', 'YES' );
Then, after the Foxhound menu finally appeared, this dbisql query showed which SQL statements took the longest to execute:
SELECT * 
  FROM sa_procedure_profile()
 where millisecs >= 100
 ORDER BY object_name, line_num;
Here's the row that shows line 102 in the rroad_monitor_list_html stored procedure took 473,628 milliseconds to run, which is 8 minutes:
object_type, object_name,               owner_name, table_name, line_num, executions, millisecs, percentage,       foreign_owner, foreign_table
'F',         'rroad_monitor_list_html', 'DBA',      ,           102,      1,          473628,    99.9972553051784, ,
Here's how to find line 102 that stored procedure:
Tip: You can't use Sybase Central to view the source code because the version it displays (SYSPROCEDURE.source) isn't the version that executes (SYSPROCEDURE.proc_defn), so you have use an Old School query.
UNLOAD SELECT proc_defn 
         FROM SYSPROCEDURE 
        WHERE proc_name = 'rroad_monitor_list_html' 
    TO 'C:\temp2\\temp_rroad_monitor_list_html.txt' 
    DELIMITED BY '' ESCAPES OFF HEXADECIMAL OFF QUOTES OFF;
Here's the Query From Hell at line 102:
select "rroad_alert_union"."sampling_id" as "sampling_id",
  "COUNT"(distinct "rroad_alert_union"."alert_number") as "active_alert_count",
  "LIST"(
  distinct "STRING"(
  '#',
  "rroad_alert_union"."alert_number"),
  ', ' order by
  "rroad_alert_union"."alert_number" asc) as "active_alert_number_list"
  into local temporary table "active_alert_count"
  from "rroad_alert_union"
  where "rroad_alert_union"."record_type" = 'Alert'
  and "rroad_alert_union"."alert_is_clear_or_cancelled" = 'N'
  group by "rroad_alert_union"."sampling_id";

Step 2: Study the Query Plans From Hell


The next step was to copy and paste the Query From Hell into dbisql and click on Tools - Plan Viewer.

In this case, the "Main Query" didn't look too bad, just one table scan for 77K rows, but the subquery called "SubQ 5" was truly breathtaking... a 77K table scan repeated 77K times!

SubQ 5 was found lurking down inside a view, and there was no apparent way to make it run faster; no indexes helped, nor did a materialized view:
LEFT OUTER JOIN ( SELECT *, 
                         'Y' AS alert_is_clear
                    FROM rroad_all_clear 
                ) AS rroad_all_clear
             ON rroad_all_clear.sampling_id        = rroad_alert.sampling_id
            AND rroad_all_clear.alert_number       = rroad_alert.alert_number
            AND rroad_all_clear.alert_in_effect_at = rroad_alert.alert_in_effect_at 

The only solution was eliminate SubQ 5 altogether by adding a new derived (redundant) column to one of the tables.

Step 3: Fix the Queries From Hell


Patch 10 actually introduces two new derived columns, for two separate Queries From Hell; here's what the code looks like.

The first derived column...
ALTER TABLE rroad_alert 
   ADD alert_is_clear_or_cancelled   VARCHAR ( 1 ) NOT NULL DEFAULT 'N';
allowed this slow query code...
CREATE VIEW rroad_alert_union AS
SELECT CAST ( 'Alert' AS VARCHAR ( 20 ) )       AS record_type,
       ...
       COALESCE ( 
          rroad_all_clear.alert_is_clear, 
          rroad_alert_cancelled.alert_is_cancelled, 
          'N' )                                 AS alert_is_clear_or_cancelled
  FROM rroad_alert
          INNER JOIN rroad_sampling_options
                  ON rroad_sampling_options.sampling_id = rroad_alert.sampling_id
          LEFT OUTER JOIN ( SELECT *, 
                                   'Y' AS alert_is_clear
                              FROM rroad_all_clear 
                          ) AS rroad_all_clear
                       ON rroad_all_clear.sampling_id        = rroad_alert.sampling_id
                      AND rroad_all_clear.alert_number       = rroad_alert.alert_number
                      AND rroad_all_clear.alert_in_effect_at = rroad_alert.alert_in_effect_at 
          LEFT OUTER JOIN ( SELECT *,  
                                   'Y' AS alert_is_cancelled
                              FROM rroad_alert_cancelled 
                          ) AS rroad_alert_cancelled
                       ON rroad_alert_cancelled.sampling_id        =  rroad_alert.sampling_id
                      AND rroad_alert_cancelled.alert_number       =  rroad_alert.alert_number
                      AND rroad_alert_cancelled.alert_in_effect_at = rroad_alert.alert_in_effect_at
to be simplified (and speeded up) by eliminating both outer join subqueries (including SUBQ 5)...
CREATE VIEW rroad_alert_union AS
SELECT CAST ( 'Alert' AS VARCHAR ( 20 ) )       AS record_type,
       ...
       rroad_alert.alert_is_clear_or_cancelled  AS alert_is_clear_or_cancelled
  FROM rroad_alert
          INNER JOIN rroad_sampling_options
                  ON rroad_sampling_options.sampling_id = rroad_alert.sampling_id
Two new triggers were required to maintain the new column:
CREATE OR REPLACE TRIGGER tri_rroad_all_clear
   AFTER INSERT ON rroad_all_clear
   REFERENCING NEW AS new_rroad_all_clear
   FOR EACH ROW
BEGIN
   UPDATE rroad_alert
      SET rroad_alert.alert_is_clear_or_cancelled = 'Y'
    WHERE rroad_alert.sampling_id        = new_rroad_all_clear.sampling_id
      AND rroad_alert.alert_number       = new_rroad_all_clear.alert_number
      AND rroad_alert.alert_in_effect_at = new_rroad_all_clear.alert_in_effect_at;
END;

CREATE OR REPLACE TRIGGER tri_rroad_alert_cancelled
   AFTER INSERT ON rroad_alert_cancelled
   REFERENCING NEW AS new_rroad_alert_cancelled
   FOR EACH ROW
BEGIN
   UPDATE rroad_alert
      SET rroad_alert.alert_is_clear_or_cancelled = 'Y'
    WHERE rroad_alert.sampling_id        = new_rroad_alert_cancelled.sampling_id
      AND rroad_alert.alert_number       = new_rroad_alert_cancelled.alert_number
      AND rroad_alert.alert_in_effect_at = new_rroad_alert_cancelled.alert_in_effect_at;
END;
Patch 10 contains another derived column...
ALTER TABLE rroad_sampling_options 
   ADD latest_sample_set_number      UNSIGNED BIGINT NOT NULL DEFAULT 0;
which allowed this slow query code...
LEFT OUTER JOIN ( SELECT rroad_group_1_property_pivot.*
                    FROM rroad_group_1_property_pivot
                         INNER JOIN ( SELECT rroad_group_1_property_pivot.sampling_id               AS sampling_id,
                                             MAX ( rroad_group_1_property_pivot.sample_set_number ) AS sample_set_number
                                        FROM rroad_group_1_property_pivot
                                       GROUP BY rroad_group_1_property_pivot.sampling_id 
                                    ) AS latest_primary_key
                                 ON latest_primary_key.sampling_id       = rroad_group_1_property_pivot.sampling_id
                                AND latest_primary_key.sample_set_number = rroad_group_1_property_pivot.sample_set_number
                 ) AS rroad_group_1_property_pivot
             ON rroad_group_1_property_pivot.sampling_id = rroad_sampling_options.sampling_id
to be simplified (and speeded up) by eliminating the complex inner join subquery:
LEFT OUTER JOIN rroad_group_1_property_pivot
             ON rroad_group_1_property_pivot.sampling_id       = rroad_sampling_options.sampling_id
            AND rroad_group_1_property_pivot.sample_set_number = rroad_sampling_options.latest_sample_set_number
Two existing triggers had to be modified to maintain the second derived column:
ALTER TRIGGER tri_rroad_sample_set
   AFTER INSERT ON rroad_sample_set
   REFERENCING NEW AS new_rroad_sample_set
   FOR EACH ROW
BEGIN
...
UPDATE rroad_sampling_options
   SET rroad_sampling_options.latest_sample_set_number = new_rroad_sample_set.sample_set_number
 WHERE rroad_sampling_options.sampling_id              = new_rroad_sample_set.sampling_id
   AND rroad_sampling_options.latest_sample_set_number < new_rroad_sample_set.sample_set_number;
...
END; -- tri_rroad_sample_set

ALTER TRIGGER tru_rroad_sample_set
   BEFORE UPDATE OF sample_finished_at ON rroad_sample_set
   REFERENCING OLD AS old_rroad_sample_set NEW AS new_rroad_sample_set
   FOR EACH ROW
BEGIN
...
IF new_rroad_sample_set.sample_set_number <> old_rroad_sample_set.sample_set_number THEN

   UPDATE rroad_sampling_options
      SET rroad_sampling_options.latest_sample_set_number = new_rroad_sample_set.sample_set_number
    WHERE rroad_sampling_options.sampling_id              = new_rroad_sample_set.sampling_id
      AND rroad_sampling_options.latest_sample_set_number < new_rroad_sample_set.sample_set_number;

END IF;
...
END; -- tru_rroad_sample_set

Sunday, July 23, 2017

Characteristic Errors, Revision 6

UPDATE: See the latest version of this article here.


A characteristic error is a mistake so easy to make it appears you're actively encouraged to make it by the very nature of the software you're using.

Here's the latest entry...

  1. SQL Anywhere: Forgetting to code AUTOSTOP OFF on a START DATABASE statement, then wondering why the database immediately stopped after starting.
Here's the full list...
  1. SQL: Seeing too little data, or no data at all, because a predicate in the WHERE clause effectively turned your OUTER JOIN into an INNER JOIN.

  2. SQL: Seeing too much data because a missing predicate in the WHERE clause effectively turned your INNER JOIN into a CROSS JOIN.

  3. SQL: Getting the wrong COUNT() or SUM() because you forgot to code WHERE ... IS NOT NULL, or you *did* code it when you shouldn't have.

  4. SQL: Getting the wrong answer because you forgot that, in general, NULL values [cough] suck.

  5. SQL Anywhere: Not seeing MESSAGE output because you forgot to run SET TEMPORARY OPTION DEBUG_MESSAGES = 'ON';

  6. SQL Anywhere: Not seeing any data because you forgot ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.

  7. SQL Anywhere: Coding ENDIF where END IF was required, or vice versa (before Version 11).

  8. SQL Anywhere: Connecting to the wrong server because you forgot DOBROAD=NONE (before Version 12).

  9. SQL Anywhere: Forgetting the asterisk in SELECT TOP 10 FROM ...

  10. SQL Anywhere: Coding IF NOT VAREXISTS ( 'x' ) THEN ... instead of IF VAREXISTS ( 'x' ) = 0 THEN ...

  11. SQL Anywhere: Coding the wrong magic numbers 1, 2, 3, ... in the get_value() and set_value() calls in an EXTERNAL C DLL function.

  12. SQL Anywhere: Getting proxy table ODBC errors because the engine's running as a service and you've set up a User DSN instead of System DSN.

  13. SQL Anywhere: Getting file-related errors because the file specifications are relative to the server rather than the client.

  14. SQL Anywhere: Getting file-related errors because the engine's running as a service without the necessary permissions.

  15. SQL Anywhere: Coding CREATE TRIGGER IF NOT EXISTS instead of CREATE OR REPLACE TRIGGER, or vice versa for CREATE TABLE (in 11.0.1 or later).

  16. SQL Anywhere: Getting integer arithmetic when you wanted fractional parts because you forgot to CAST.

  17. Stored procedure debugger: Setting it to watch a specific user id other than the one you're using to test your code.

  18. Sybase Central: Setting it to display objects for owner names other than the one you're interested in.

  19. Copy and paste: Forgetting to edit after pasting; e.g., Copy and paste SET @continue = 'Y' into the body of a WHILE loop and then forgetting to change it to 'N'.

  20. MobiLink: Forgetting to call ml_add_column for any of the columns you're trying to synchronize, thus guaranteeing yourself a "Sassen Frassen Fricken Fracken!" moment when you run the first test.

  21. MobiLink: Forgetting to call ml_add_[various] with the NULL parameter to delete old ml_[whatever] rows, thus ending up with thousands of orphan system table rows in the consolidated database.

  22. OLAP Windowing: Coding the wrong combination of ASC and DESC in an inner OVER ORDER BY clause and the outer SELECT ORDER BY: different when they should be the same, the same when they should be different, or some other variation of "wrong combination"...
    SELECT older_sample_set.sample_set_number
                  INTO @20_older_sample_set_number
                  FROM ( SELECT TOP 20
                                ROW_NUMBER() OVER ( ORDER BY rroad_sample_set.sample_set_number ASC ) AS scrolling_row_number,
                                rroad_sample_set.sample_set_number                                    AS sample_set_number
                           FROM rroad_sample_set
                          WHERE rroad_sample_set.sampling_id       = @sampling_id
                            AND rroad_sample_set.sample_set_number < @sample_set_number
                          ORDER BY rroad_sample_set.sample_set_number DESC ) AS older_sample_set
                 WHERE older_sample_set.scrolling_row_number = 20;

  23. MobiLink: Forgetting to call ml_add_column() when trying to use named parameters instead of "?" in versions 10 and 11 MobiLink scripts, resulting in a "What the ... ? Sassen Frassen Fricken Fracken!" moment during the first test (thank you, Jeff Albion).

  24. SQL: Omitting a PRIMARY KEY column from the WHERE clause, thus turning a singleton SELECT (or DELETE!) into something rather more enthusiastic than expected (thank you, Ron Hiner).

  25. HTTP web services: Leaving an & in the code when a ? is required, and vice versa, when editing service URLs; e.g., 'HTTP://localhost:12345/web_service&service_parm2=!parm2'

  26. SQL Anywhere: Forgetting that not all functions look like functions: SELECT CAST ( CURRENT TIMESTAMP, VARCHAR )

  27. Batch file: Trailing spaces on SET commands; e.g., SELECT CAST ( xp_getenv ( 'DEBUG_MESSAGES' ) AS VARCHAR ) returns 'OFF ' instead of 'OFF' after SET DEBUG_MESSAGES=OFF

  28. Forum: Clicking Reply on the main Question or Answer entry instead of the comment you wanted.

  29. SQL Anywhere: Forgetting to run dblog to tell the database file where the log is now, after moving the database and log files to a different folder (thank you, Justin Willey).

  30. SQL Anywhere: Having to look up WAIT in the Help ... every ... single ... time, to be reminded that's it's WAITFOR, not WAIT.

  31. SQL: Forgetting to check the SELECT against the GROUP BY, resulting in "Function or column reference to ... must also appear in a GROUP BY" (thank you, Glenn Paulley).

  32. SQL: Coding too much in the GROUP BY (like, say, the primary key) so every group contains but a single row (thank you, Glenn Paulley).

  33. Design: Forgetting to accomodate or prevent loops in a tree structure, resulting in a tree traversal process that pegs the CPU at 100%... forever (thank you, Ove B).

  34. MobiLink: Unwittingly using a variety of user ids when running sync*.sql, updating MobiLink scripts and running the MobiLink server, resulting in inexplicable inconsistencies.

  35. MobiLink: Accidentally creating multiple script versions and then getting them crossed up when updating MobiLink scripts and running the MobiLink client.

  36. SQL Anywhere: Forgetting to run the 32-bit version of SQL Anywhere when working with Excel proxy tables.

  37. ODBC Administrator: Running the 64-bit version (huh?) of odbcad32.exe (say what?) when you need 32-bit version at C:\WINDOWS\SysWOW64\odbcad32.exe (oh, fer #*@&!!!)

  38. ODBC Administrator: Forgetting to click OK ... twice ... to actually save your new ODBC DSN after celebrating your success with Test Connection.

  39. ODBC Administrator: Setting up an ODBC DSN on the wrong computer: "It goes with the client!" ... but sometimes it's not obvious where the client is actually located.

  40. Security: Forgetting which Windows user id you're using on which system, then spending too much time with Windows menus, firewall software and Google searches before the "Doh!" moment.

  41. SQL: Getting an exception that is not only completely inexplicable, but absolutely impossible for the statement that raised it... until you think to look inside the triggers.

  42. SQL Anywhere: Getting an exception because a FOR loop variable has a scope conflict with a column name, or worse, NOT getting an exception, just a wrong result.

  43. SQL: Forgetting the comma between two columns in SELECT list, thus turning the second column name into a profoundly misleading alias name for the first column.

  44. SQL Anywhere: Coding SET OPTION instead of SET TEMPORARY OPTION, then wondering where the commit came from.

  45. SQL: Not bothering to check that you have the same number of columns and arguments in an INSERT statement and then wondering why you have an error (thank you, Justin Willey).

  46. SQL: Forgetting to omit your autoincrement primary key from your INSERT column name list (thank you, Justin Willey).

  47. Foxhound: Changing the "Default" Monitor option settings, then expecting them to apply to an existing target database.

  48. Foxhound: Creating two separate Foxhound Monitor sessions for the same actual target database, perhaps one using a DSN and the other a DSN-less connection string.

  49. SQL Anywhere: Forgetting to code AUTOSTOP OFF on a START DATABASE statement, then wondering why the database immediately stopped after starting.



Tuesday, July 11, 2017

Billion-Row Tables

Here's the good news, and the bad news, all on one line:


Foxhound fails when a single table hits one billion rows.

The bad news, it's a bug in Foxhound:

The Display Schema feature shows Value out of range for destination because some intermediate calculations use DECIMAL ( 11, 2 ) to hold the row count for indivdual tables.

The good news is

SQL Anywhere is being used to hold billion-row tables!

That's billion rows, not bytes.

And the problem was first reported for SQL Anywhere 9 which has been around longer than Facebook.

The Display Schema side of Foxhound isn't exactly popular, it's the Foxhound Monitor folks want. Nevertheless, Display Schema does have its fans:
Question: Do you like the Display Schema feature? If so, I can create a patch for you.

Answer: "Yes, it's a life saver being that I'm in the middle of converting from ASA9 to ASA17.

It gives me quick view of database information to compare each database stat from before unload and after reload."
Innocent Ekhelar
Dir. Network Services
LOG-NET, Inc.
More good news, a fix is available for Foxhound - click "Show Patches" and read about "Patch 4".
"It works perfectly!!"
Innocent Ekhelar

Saturday, March 25, 2017

Extending Foxhound, Part 3: Web Service

This article is the third in a series about extending Foxhound with adhoc queries:

  • The first article showed how to run adhoc queries in ISQL to simulate a single Monitor dashboard tab,

  • the second article showed how to use proxy tables to combine data from multiple Foxhound databases into one display, and

  • this article shows how to write a web service to show the results in a web browser like Chrome or, in this case, Vivaldi...

The first step is to enable SQL Anywhere's built-in HTTP server with the dbsrv17 -xs option:
"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -o dbsrv17_log_dashboard17.txt^
  -x tcpip^
  -xs http(port=12345)^
  dashboard17.db 
Tip: This is a separate SQL Anywhere 17 database, created by you, that is different from any of the Foxhound databases, and different from any of your target databases being monitored by Foxhound. It is the same database as the one created in Step 1 of the previous article, except for the dbsrv17 -xs option which turns on HTTP processing.
The second step is to use the CREATE SERVICE statement to set up a web service in SQL Anywhere that calls a procedure:
CREATE SERVICE dashboard
   TYPE 'RAW' AUTHORIZATION OFF URL ON USER DBA
   AS CALL dashboard ( :refresh_count );
Tip: Don't get the CREATE SERVICE statement confused with CREATE SERVER. The CREATE SERVICE statement creates a web service in the local database whereas CREATE SERVER creates a pointer to a remote server, and both are used in this article.
The third step is to create a procedure that builds the HTML text in a single LONG VARCHAR string and returns that string to the browser when the service calls the procedure:
  • The "Refresh count:" display field is implemented by
    • the @in_refresh_count parameter defined on line 2 below
    • which is updated by the code on lines 12 through 18,
    • passed to the next (automatic) execution of the web service by the META HTTP-EQUIV="refresh" tag on line 42 and
    • displayed in the browser window on line 58.

  • The code on lines 20 through 127 is a rewrite of the code in Step 5: Query Data In Local Tables in the previous article.

  • The call on line 32 uses a whole whackload of code from the previous article, which you can see at the bottom of this article in the Supporting Code appendices.

  • The long LIST ( STRING ( ... calls starting on lines 73 and 107 turn the result sets from Step 5 into strings of HTML text.

  • The SELECT starting on line 39 fills the local variable @result with HTML text built from data retrieved from the "Summary List Of Target Databases" table.

  • The SELECT starting on line 95 appends more HTML text build from the "Active Alerts For Each Target Database" table to @result and returns the whole thing to the caller...

  • ...which is the CREATE SERVICE statement, which is how SQL Anywhere's HTTP server gets the HTML and returns it to the browser.
CREATE PROCEDURE dashboard (
   IN @in_refresh_count  LONG VARCHAR )
RESULT ( html_string LONG VARCHAR )
BEGIN

DECLARE @refresh_count   INTEGER;
DECLARE @result          LONG VARCHAR;

---------------------------------------------------------------------------------------- 
-- Initialize or increment the refresh count to pass to the next execution.

IF ISNUMERIC ( COALESCE ( @in_refresh_count, '0' ) ) = 1 THEN
   SET @refresh_count = CAST ( COALESCE ( @in_refresh_count, '0' ) AS INTEGER );
ELSE
   SET @refresh_count = 0;
END IF;

SET @refresh_count = @refresh_count + 1;

---------------------------------------------------------------------------------------- 
-- Fill in the list of remote Foxhound server names.

TRUNCATE TABLE foxhound_server_list;
INSERT foxhound_server_list VALUES ( 'Foxhound4' );
INSERT foxhound_server_list VALUES ( 'Foxhound4b' );
INSERT foxhound_server_list VALUES ( 'Foxhound4c' );
COMMIT;

---------------------------------------------------------------------------------------- 
-- Fill "Summary List Of Target Databases" and "Active Alerts For Each Target Database".

CALL refresh_dashboard_data();

---------------------------------------------------------------------------------------- 
-- Build part of the return HTML.

CALL dbo.sa_set_http_header( 'Content-Type', 'text/html' );

SELECT STRING ( 
   '<HTML>',
   '<HEAD>',
   '<META HTTP-EQUIV="refresh" CONTENT="10; URL=dashboard?refresh_count=', @refresh_count, '">',
   '<TITLE>Foxhound Dashboard</TITLE>',
   '<STYLE>',
      'BODY { margin: 0; padding: 0; font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; 
              font-size: 100%; }',
      'IMG { border: 0; margin: 0; }',
      'INPUT { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
      'P { font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
      'TABLE { margin: 0; padding: 0; }',
      'TD { margin: 0; padding: 0 0 0 0.6em; vertical-align: bottom; 
            font-family: "VERDANA", "ARIAL", "HELVETICA", "SANS-SERIF"; font-size: 1em; }',
      'TR { margin: 0; padding: 0; }',
   '</STYLE>',
   '</HEAD>',
   '<BODY>',
   '<P STYLE="padding-left: 0.6em;">',
   'Refresh count: ', @refresh_count,
   '<P>',
   '<TABLE>',
   '<TR>',
   '<TD STYLE="text-align: center;"><B>Foxhound<BR />Server</B></TD>',
   '<TD STYLE="text-align: center;"><B>ID</B></TD>',
   '<TD STYLE="text-align: center;"><B>Target Database</B></TD>',
   '<TD STYLE="text-align: center;"><B>Monitor Status</B></TD>',
   '<TD STYLE="text-align: center;"><B>Active<BR />Alerts</B></TD>',
   '<TD STYLE="text-align: center;"><B>Heartbeat</B></TD>',
   '<TD STYLE="text-align: center;"><B>Unsch<BR />Req</B></TD>',
   '<TD STYLE="text-align: center;"><B>Conns</B></TD>',
   '<TD STYLE="text-align: center;"><B>Blocked<BR />Conns</B></TD>',
   '<TD STYLE="text-align: center;"><B>CPU Time</B></TD>',
   '</TR>',
   LIST ( STRING ( 
      '<TR>',
      '<TD STYLE="text-align: left;">',   "Summary List Of Target Databases"."Foxhound Server", '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."ID",              '</TD>',
      '<TD STYLE="text-align: left;">',   "Summary List Of Target Databases"."Target Database", '</TD>',
      '<TD STYLE="text-align: left;">',   "Summary List Of Target Databases"."Monitor Status",  '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."Active Alerts",   '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Heartbeat,         '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."Unsch Req",       '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Conns,             '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases".Blocked,           '</TD>',
      '<TD STYLE="text-align: center;">', "Summary List Of Target Databases"."CPU Time",        '</TD>',
      '</TR>' ),
      '' -- empty LIST row delimiter
      ORDER BY "Target Database", "ID" ),
   '</TABLE>' )
  INTO @result
  FROM "Summary List Of Target Databases";

---------------------------------------------------------------------------------------- 
-- Finish building the HTML page and return it to the browser.

SELECT STRING ( 
   @result,
   '<P>',
   '<TABLE>',
   '<TR>',
   '<TD STYLE="text-align: center;"><B>Foxhound<BR />Server</B></TD>',
   '<TD STYLE="text-align: center;"><B>ID</B></TD>',
   '<TD STYLE="text-align: center;"><B>Target Database</B></TD>',
   '<TD STYLE="text-align: center;"><B>Time Since<BR />Alert Recorded</B></TD>',
   '<TD STYLE="text-align: center;"><B>Active<BR />Alert #,</B></TD>',
   '<TD STYLE="text-align: left;"  ><B>Description</B></TD>',
   '</TR>',
   LIST ( STRING ( 
      '<TR>',
      '<TD STYLE="text-align: left;">',
         "Active Alerts For Each Target Database"."Foxhound Server", '</TD>',
      '<TD STYLE="text-align: center;">',
         "Active Alerts For Each Target Database"."ID", '</TD>',
      '<TD STYLE="text-align: left;">',
         "Active Alerts For Each Target Database"."Target Database", '</TD>',
      '<TD STYLE="text-align: center;">',
         "Active Alerts For Each Target Database"."Time Since Alert Recorded", '</TD>',
      '<TD STYLE="text-align: right;">',
         "Active Alerts For Each Target Database"."Alert #", '</TD>',
      '<TD STYLE="text-align: left;" NOWRAP>',
         "Active Alerts For Each Target Database"."Alert Description", '</TD>',
      '</TR>' ),
      '' -- empty LIST row delimiter
      ORDER BY "Target Database", "ID", "Alert #" ),
   '</TABLE>',
   '</BODY>',
   '</HTML>' )
  FROM "Active Alerts For Each Target Database";

END;

Here's the URL for launching the web service:
http://localhost:12345/dashboard

Supporting Code

The following appendices show the code described in the previous article and used here.

Appendix 1: Create Remote Server And Proxy Tables

CREATE SERVER Foxhound_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere Native; ENG=Foxhound4; DBN=f; UID=ADHOC; PWD=SQL;';

CREATE EXISTING TABLE sampling_options AT 'Foxhound_server...sampling_options';
CREATE EXISTING TABLE sample_detail    AT 'Foxhound_server...sample_detail';
CREATE EXISTING TABLE alert_union      AT 'Foxhound_server...alert_union';

CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
   ( IN @msecs   INTEGER )
   RETURNS VARCHAR ( 20 )
   AT 'Foxhound_server...rroad_f_msecs_as_abbreviated_d_h_m_s_ms';

Appendix 2: Create Local Views

CREATE VIEW v_latest_primary_key AS 
   SELECT sample_detail.sampling_id                           AS sampling_id,
          MAX ( sample_detail.sample_set_number )             AS sample_set_number
     FROM sample_detail
    GROUP BY sample_detail.sampling_id;

CREATE VIEW v_sample_detail AS
   SELECT sample_detail.*
     FROM sample_detail
             INNER JOIN v_latest_primary_key
                     ON v_latest_primary_key.sampling_id       = sample_detail.sampling_id
                    AND v_latest_primary_key.sample_set_number = sample_detail.sample_set_number;

CREATE VIEW v_active_alerts AS
   SELECT alert_union.sampling_id                             AS sampling_id,
          LIST ( DISTINCT STRING ( 
                     '#',  
                     alert_union.alert_number ),
                 ', '
                 ORDER BY alert_union.alert_number )          AS active_alert_number_list
    FROM alert_union
   WHERE alert_union.record_type                 = 'Alert'
     AND alert_union.alert_is_clear_or_cancelled = 'N'
    GROUP BY alert_union.sampling_id;

CREATE VIEW "vSummary List Of Target Databases" AS 
SELECT sampling_options.sampling_id                                AS "ID",
       STRING ( 
          sampling_options.selected_name,
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                                 AS "Target Database",
       sampling_options.connection_status_message                  AS "Monitor Status",
       COALESCE ( v_active_alerts.active_alert_number_list, '-' )  AS "Active Alerts",
       IF  sampling_options.sampling_should_be_running = 'Y' 
       AND sampling_options.connection_status_message  = 'Sampling OK'
          THEN rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
                 v_sample_detail.canarian_query_elapsed_msec ) 
          ELSE '-'
       ENDIF                                                       AS "Heartbeat",
       IF COALESCE ( v_sample_detail.UnschReq, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.UnschReq )
       ENDIF                                                       AS "Unsch Req",
       IF COALESCE ( v_sample_detail.ConnCount, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.ConnCount )
       ENDIF                                                       AS "Conns",
       IF COALESCE ( v_sample_detail.total_blocked_connection_count, 0 ) = 0
          THEN '-'
          ELSE STRING ( 
             v_sample_detail.total_blocked_connection_count )
       ENDIF                                                       AS "Blocked",
       CASE
          WHEN COALESCE ( v_sample_detail.interval_CPU_percent, 0.0 ) = 0.0
             THEN '-'
          ELSE STRING ( ROUND ( 
             v_sample_detail.interval_CPU_percent, 1 ), '%' )
       END                                                         AS "CPU Time"
  FROM sampling_options
          LEFT OUTER JOIN v_sample_detail
                       ON v_sample_detail.sampling_id = sampling_options.sampling_id
          LEFT OUTER JOIN v_active_alerts
                       ON v_active_alerts.sampling_id = sampling_options.sampling_id;

CREATE VIEW "vActive Alerts For Each Target Database" AS 
SELECT sampling_options.sampling_id                 AS "ID",
       STRING ( 
          sampling_options.selected_name,
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                  AS "Target Database",
       rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
          DATEDIFF ( MILLISECOND, 
                     alert_union.recorded_at, 
                     CURRENT TIMESTAMP ) )          AS "Time Since Alert Recorded",
       alert_union.alert_number                     AS "Alert #",
       alert_union.alert_description                AS "Alert Description"
  FROM sampling_options
          INNER JOIN alert_union
                  ON alert_union.sampling_id = sampling_options.sampling_id
 WHERE alert_union.record_type                 = 'Alert'
   AND alert_union.alert_is_clear_or_cancelled = 'N';

Appendix 3: Create Local Permanent Tables

CREATE TABLE "Summary List Of Target Databases" (
   "Foxhound Server"  VARCHAR ( 128 )   NOT NULL,
   "ID"               UNSIGNED INT      NOT NULL,
   "Target Database"  VARCHAR ( 133 )   NOT NULL,   
   "Monitor Status"   VARCHAR ( 32767 ) NOT NULL,   
   "Active Alerts"    LONG VARCHAR      NOT NULL, 
   Heartbeat          VARCHAR ( 20 )    NOT NULL,   
   "Unsch Req"        VARCHAR ( 21 )    NOT NULL,   
   Conns              VARCHAR ( 21 )    NOT NULL,   
   Blocked            VARCHAR ( 21 )    NOT NULL,   
   "CPU Time"         VARCHAR ( 34 )    NOT NULL,
   PRIMARY KEY ( "Foxhound Server", "ID" ) );

CREATE TABLE "Active Alerts For Each Target Database" (
   "Foxhound Server"            VARCHAR ( 128 )   NOT NULL,
   "ID"                         UNSIGNED INT      NOT NULL,
   "Target Database"            VARCHAR ( 133 )   NOT NULL,   
   "Time Since Alert Recorded"  VARCHAR ( 20 )    NOT NULL,   
   "Alert #"                    INTEGER           NOT NULL,
   "Alert Description"          LONG VARCHAR      NOT NULL,  
   PRIMARY KEY ( "Foxhound Server", "ID", "Alert #" ) );

CREATE TABLE foxhound_server_list (
   foxhound_server  VARCHAR ( 128 )   NOT NULL PRIMARY KEY );

Appendix 4: CREATE PROCEDURE refresh_dashboard_data

CREATE PROCEDURE refresh_dashboard_data()
BEGIN

TRUNCATE TABLE "Summary List Of Target Databases";

TRUNCATE TABLE "Active Alerts For Each Target Database";

FOR f_server AS c_server INSENSITIVE CURSOR FOR
SELECT foxhound_server AS @foxhound_server
  FROM foxhound_server_list
 ORDER BY foxhound_server
FOR READ ONLY
DO

   DROP REMOTE CONNECTION TO Foxhound_server CLOSE ALL;

   ALTER SERVER Foxhound_server 
      USING 'DRIVER=SQL Anywhere Native; ENG={@foxhound_server}; DBN=f; UID=ADHOC; PWD=SQL;';

   INSERT "Summary List Of Target Databases" 
   SELECT @foxhound_server,
          *
     FROM "vSummary List Of Target Databases";

   INSERT "Active Alerts for each target database" 
   SELECT @foxhound_server,
          *
     FROM "vActive Alerts for each target database";

   COMMIT;

END FOR;

END;


Saturday, March 18, 2017

Extending Foxhound, Part 2: Proxy Tables

In theory, a single copy of the Foxhound Extended Edition can monitor up to 100 target databases, each with an unlimited number of connections, but in reality multiple copies of Foxhound are often required to handle a large number of heavily-loaded target databases.

What you end up with, after following the instructions in Starting Multiple Copies of Foxhound, is a different browser window for each copy of Foxhound. Here's what that looks like for three copies of Foxhound monitoring a total of 31 target databases with 973 connections:



The previous article in this series showed how to code adhoc queries that simulate a single Monitor dashboard tab; this article shows how to code queries that combine multiple dashboards into one.

Result Set 1: Summary List Of All Target Databases

Here's what the result set looks like for three copies of Foxhound in one combined "Summary List":


Result Set 2: Active Alerts For All Target Databases

Here's what the combined "Active Alerts" result set looks like for three copies of Foxhound:



Since the Foxhound shortcut "Adhoc Query Foxhound Database via ISQL" can only connect to one Foxhound database at a time, and the result sets shown above require data from three different Foxhound databases, a different mechanism is required.

Remote Data Access

One solution is to create a separate SQL Anywhere database and use the Remote Data Access feature (also known as "remote servers" and "proxy tables") to gather data from all three Foxhound databases in one place.

Here's how the solution works:
  1. One proxy table is created in the new database for each of Foxhound adhoc views that were used by the original queries: sampling_options, sample_detail and alert_union.

  2. Two local views are created to gather the data from the proxy tables; these local views look almost identical to the original queries.

  3. Two local permanent tables are created to store the data gathered by the local views.

  4. A SQL FOR loop is executed to switch the proxy table definitions to point to each of the three Foxhound databases in turn, and to gather data from the two local views and store it in the local tables.

  5. Two local queries are written to display the local table data as shown earlier in this article: Result Set 1: Summary List Of All Target Databases and Result Set 2: Active Alerts For All Target Databases.

Step 1: Create Database And Proxy Tables

Here are the Windows commands to create and start a new database and launch ISQL:
"%SQLANY17%\bin64\dbinit.exe"^
  -dba dba,sql^
  -mpl 3^
  dashboard17.db

"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -o dbsrv17_log_dashboard17.txt^
  -x tcpip^
  dashboard17.db 

"%SQLANY17%\bin64\dbisql.com"^
  -c "ENG=dashboard17; DBN=dashboard17; UID=dba; PWD=sql; CON=dashboard17-1"
...and here are the SQL statements to set up the proxy tables:
CREATE SERVER Foxhound_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere Native; ENG=Foxhound4; DBN=f; UID=ADHOC; PWD=SQL;';

CREATE EXISTING TABLE sampling_options AT 'Foxhound_server...sampling_options';
CREATE EXISTING TABLE sample_detail    AT 'Foxhound_server...sample_detail';
CREATE EXISTING TABLE alert_union      AT 'Foxhound_server...alert_union';

CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
   ( IN @msecs   INTEGER )
   RETURNS VARCHAR ( 20 )
   AT 'Foxhound_server...rroad_f_msecs_as_abbreviated_d_h_m_s_ms';
  • The CREATE SERVER statement on lines 1 and 2 specifies the remote server name (Foxhound_server) and connection string (the USING clause) that will be used to retrieve data from the Foxhound databases.

  • The three CREATE EXISTING TABLE statements on lines 4, 5 and 6 create proxy tables (schema but no data) in the local database.

  • Each proxy table corresponds to one Foxhound adhoc schema view in the Foxhound database: sampling_options, sample_detail and alert_union (yes, a proxy table can point to a view in the remote database).

  • The CREATE FUNCTION statement on lines 8 through 11 makes the internal Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms available on a "remote procedure call" basis for proxy table queries.

  • Tip: The code shown here uses SQL Anywhere 17 but SQL Anywhere 16 will work just fine.

  • Caution: A "Count field incorrect" bug in both SQL Anywhere 16 and 17 prevented the CREATE FUNCTION statement on lines 8 through 11 from working properly. That bug was fixed in 16.0.0.2342, 17.0.4.2177 and later builds.

Step 2: Create Local Views

The previous article in this series showed a single SELECT statement for Query 1: Summary List Of Target Databases. That SELECT has been recoded as four separate CREATE VIEW statements, with only minor changes required:
CREATE VIEW v_latest_primary_key AS 
   SELECT sample_detail.sampling_id                           AS sampling_id,
          MAX ( sample_detail.sample_set_number )             AS sample_set_number
     FROM sample_detail
    GROUP BY sample_detail.sampling_id;

CREATE VIEW v_sample_detail AS
   SELECT sample_detail.*
     FROM sample_detail
             INNER JOIN v_latest_primary_key
                     ON v_latest_primary_key.sampling_id       = sample_detail.sampling_id
                    AND v_latest_primary_key.sample_set_number = sample_detail.sample_set_number;

CREATE VIEW v_active_alerts AS
   SELECT alert_union.sampling_id                             AS sampling_id,
          LIST ( DISTINCT STRING ( 
                     '#',  
                     alert_union.alert_number ),
                 ', '
                 ORDER BY alert_union.alert_number )          AS active_alert_number_list
    FROM alert_union
   WHERE alert_union.record_type                 = 'Alert'
     AND alert_union.alert_is_clear_or_cancelled = 'N'
    GROUP BY alert_union.sampling_id;

CREATE VIEW "vSummary List Of Target Databases" AS 
SELECT sampling_options.sampling_id                                AS "ID",
       STRING ( 
          sampling_options.selected_name,
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                                 AS "Target Database",
       sampling_options.connection_status_message                  AS "Monitor Status",
       COALESCE ( v_active_alerts.active_alert_number_list, '-' )  AS "Active Alerts",
       IF  sampling_options.sampling_should_be_running = 'Y' 
       AND sampling_options.connection_status_message  = 'Sampling OK'
          THEN rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
                 v_sample_detail.canarian_query_elapsed_msec ) 
          ELSE '-'
       ENDIF                                                       AS "Heartbeat",
       IF COALESCE ( v_sample_detail.UnschReq, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.UnschReq )
       ENDIF                                                       AS "Unsch Req",
       IF COALESCE ( v_sample_detail.ConnCount, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.ConnCount )
       ENDIF                                                       AS "Conns",
       IF COALESCE ( v_sample_detail.total_blocked_connection_count, 0 ) = 0
          THEN '-'
          ELSE STRING ( 
             v_sample_detail.total_blocked_connection_count )
       ENDIF                                                       AS "Blocked",
       CASE
          WHEN COALESCE ( v_sample_detail.interval_CPU_percent, 0.0 ) = 0.0
             THEN '-'
          ELSE STRING ( ROUND ( 
             v_sample_detail.interval_CPU_percent, 1 ), '%' )
       END                                                         AS "CPU Time"
  FROM sampling_options
          LEFT OUTER JOIN v_sample_detail
                       ON v_sample_detail.sampling_id = sampling_options.sampling_id
          LEFT OUTER JOIN v_active_alerts
                       ON v_active_alerts.sampling_id = sampling_options.sampling_id;
The previous article in this series showed a single SELECT statement for Query 2: Active Alerts For Each Target Database. That SELECT has been recoded as a single CREATE VIEW statement which looks almost exactly the same:
CREATE VIEW "vActive Alerts For Each Target Database" AS 
SELECT sampling_options.sampling_id                 AS "ID",
       STRING ( 
          sampling_options.selected_name,
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                  AS "Target Database",
       rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
          DATEDIFF ( MILLISECOND, 
                     alert_union.recorded_at, 
                     CURRENT TIMESTAMP ) )          AS "Time Since Alert Recorded",
       alert_union.alert_number                     AS "Alert #",
       alert_union.alert_description                AS "Alert Description"
  FROM sampling_options
          INNER JOIN alert_union
                  ON alert_union.sampling_id = sampling_options.sampling_id
 WHERE alert_union.record_type                 = 'Alert'
   AND alert_union.alert_is_clear_or_cancelled = 'N';

Step 3: Create Local Permanent Tables

These two local tables look exactly like the views in Step 2; same column names and same data types:
CREATE TABLE "Summary List Of Target Databases" (
   "Foxhound Server"  VARCHAR ( 128 )   NOT NULL,
   "ID"               UNSIGNED INT      NOT NULL,
   "Target Database"  VARCHAR ( 133 )   NOT NULL,   
   "Monitor Status"   VARCHAR ( 32767 ) NOT NULL,   
   "Active Alerts"    LONG VARCHAR      NOT NULL, 
   Heartbeat          VARCHAR ( 20 )    NOT NULL,   
   "Unsch Req"        VARCHAR ( 21 )    NOT NULL,   
   Conns              VARCHAR ( 21 )    NOT NULL,   
   Blocked            VARCHAR ( 21 )    NOT NULL,   
   "CPU Time"         VARCHAR ( 34 )    NOT NULL,
   PRIMARY KEY ( "Foxhound Server", "ID" ) );

CREATE TABLE "Active Alerts For Each Target Database" (
   "Foxhound Server"            VARCHAR ( 128 )   NOT NULL,
   "ID"                         UNSIGNED INT      NOT NULL,
   "Target Database"            VARCHAR ( 133 )   NOT NULL,   
   "Time Since Alert Recorded"  VARCHAR ( 20 )    NOT NULL,   
   "Alert #"                    INTEGER           NOT NULL,
   "Alert Description"          LONG VARCHAR      NOT NULL,  
   PRIMARY KEY ( "Foxhound Server", "ID", "Alert #" ) );

Step 4: Gather Data In A FOR Loop

A stored procedure is used to gather the data; here's how it works:
  • The local permanent table defined on lines 1 and 2 is used to hold the Foxhound server names that drive the FOR statement on lines 11 through 16.

  • The DROP REMOTE CONNECTION statement on line 18 causes the local remote server to be disconnected from the previous Foxhound database.

  • The ALTER SERVER statement on lines 20 and 21 changes the remote server USING clause to point to the next Foxhound database. The special {@foxhound_server} syntax in the USING clause allows it to be dynamically modified at run time without having to code an EXECUTE IMMEDIATE statement.

  • The two INSERT SELECT statements on lines 23 through 31 copies the data from the two local views defined in Step 2 and appends it to the local permanent tables defined in Step 3.

  • Tip: Remote server connections are not opened by the CREATE SERVER and ALTER SERVER statements, but by the first statement that actually needs data from the remote server; e.g., the CREATE EXISTING TABLE and SELECT statements. This is a subtle point, but it does explain why connection errors aren't detected as soon as a remote server is created.
CREATE TABLE foxhound_server_list (
   foxhound_server  VARCHAR ( 128 )   NOT NULL PRIMARY KEY );

CREATE PROCEDURE refresh_dashboard_data()
BEGIN

TRUNCATE TABLE "Summary List Of Target Databases";

TRUNCATE TABLE "Active Alerts For Each Target Database";

FOR f_server AS c_server INSENSITIVE CURSOR FOR
SELECT foxhound_server AS @foxhound_server
  FROM foxhound_server_list
 ORDER BY foxhound_server
FOR READ ONLY
DO

   DROP REMOTE CONNECTION TO Foxhound_server CLOSE ALL;

   ALTER SERVER Foxhound_server 
      USING 'DRIVER=SQL Anywhere Native; ENG={@foxhound_server}; DBN=f; UID=ADHOC; PWD=SQL;';

   INSERT "Summary List Of Target Databases" 
   SELECT @foxhound_server,
          *
     FROM "vSummary List Of Target Databases";

   INSERT "Active Alerts for each target database" 
   SELECT @foxhound_server,
          *
     FROM "vActive Alerts for each target database";

   COMMIT;

END FOR;

END;

Step 5: Query Data In Local Tables

Here's how the the two result sets are displayed:
  • The TRUNCATE TABLE and INSERT statements on lines 1 through 6 reload the foxhound_server table with the Foxhound ENG= values that drive the FOR loop.

  • The CALL statement on line 9 runs the FOR loop, and the SELECT statements on lines 10 through 19 display the data; the primary ORDER BY column is "Target Database" which is more convenient than sorting by Foxhound server name.
TRUNCATE TABLE foxhound_server_list;

INSERT foxhound_server_list VALUES ( 'Foxhound4' );
INSERT foxhound_server_list VALUES ( 'Foxhound4b' );
INSERT foxhound_server_list VALUES ( 'Foxhound4c' );
COMMIT;

CALL refresh_dashboard_data();

SELECT * 
  FROM "Summary List Of Target Databases"
 ORDER BY "Target Database", 
       "ID";

SELECT * 
  FROM "Active Alerts for each target database"
 ORDER BY "Target Database", 
       "ID", 
       "Alert #";
The two result sets are shown earlier; see Result Set 1: Summary List Of All Target Databases and Result Set 2: Active Alerts For All Target Databases;