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;


Sunday, March 12, 2017

Extending Foxhound, Part 1

Adhoc queries are commonly run against the Foxhound Monitor database for one of two reasons:

  1. to search historical data for performance anomalies that aren't apparent in the standard Foxhound displays, and

  2. to duplicate and enhance Foxhound features.
This article explores the second point by constructing adhoc queries that mimic the Monitor dashboard tab of the Foxhound Menu page:


Here's where the data for the top list comes from, in the screenshot above:

Query 1: Summary List Of Target Databases

Jumping ahead, here's what the custom Summary List Of Target Databases query looks like when it's run in All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL:



The SELECT below uses a WITH clause on lines 1 through 23 to create three local views:
  • v_latest_primary_key finds the latest sample_detail row for each target database; this is a big deal since Foxhound can store many thousands of sample_detail rows for each target database.

  • v_sample_detail makes use of v_latest_primary_key to return all the columns in those latest sample_detail rows.

  • v_active_alerts uses SQL Anywhere's LIST function to gather the alert_number values for all the Alerts that aren't Cancelled or All Clear; the predicate alert_union.alert_is_clear_or_cancelled = 'N' is an prime example of the value added by Foxhound's underlying rroad_alert_union view.

  • Tip: Foxhound doesn't let you create permanent views via CREATE VIEW, but it does let you create temporary views as "common table expressions" in the WITH clause. It also lets you code temporary views as "derived tables" inside the FROM clause, but the WITH clause is sometimes easier to understand and maintain.
WITH 
   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 ),
   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 ),
   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 )
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
 ORDER BY "Target Database", 
       "ID";
The SELECT statement on lines 24 through 64 gathers columns from sampling_options, v_sample_detail and v_active_alerts; in particular:
  • The STRING function call on lines 25 through 30 determines if the sampling_options.selected_name column contains a DSN or a Foxhound connection string name.

  • The IF expression on lines 33 through 38 makes use of the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms() to display the sample_detail.canarian_query_elapsed_msec column.

Query 2: Active Alerts For Each Target Database

Here's what the ISQL output looks like for the second custom query, the one listing "Active Alerts":



Here's where the data comes from:
  • The ID and Target Database columns come from sampling_id and selected_name in the sampling_options view.

  • The Time Since Alert Recorded column is calculated from the recorded_at column in the alert_union view, using DATEDIFF(), CURRENT TIMESTAMP and the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms().

  • The Alert # and Alert Description columns come from alert_number and alert_description in the alert_union view.
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'
 ORDER BY "Target Database", 
       "ID", 
       "Alert #";