Wednesday, February 26, 2020

DIY CREATE VIEW Statements In Foxhound 5

Open access is a hallmark of Foxhound:

Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it.
Foxhound Version 5 extends that to include CREATE VIEW and other DDL statements; here's what you can and cannot do:
      no  CREATE EVENT
      no  CREATE EXISTING TABLE 
 yes      CREATE FUNCTION
      no  CREATE FUNCTION [External call]
 yes      CREATE INDEX
      no  CREATE MATERIALIZED VIEW
 yes      CREATE PROCEDURE
      no  CREATE PROCEDURE [External call]
      no  CREATE SEQUENCE
      no  CREATE SERVICE
 yes      CREATE TABLE
 yes      CREATE TEMPORARY PROCEDURE
      no  CREATE TRIGGER
      no  CREATE USER
 yes      CREATE VARIABLE [Connection-scope]
      no  CREATE VARIABLE [Database-scope] 
 yes      CREATE VIEW 
 yes      DELETE
 yes      INSERT
 yes      SELECT
 yes      TRUNCATE
 yes      UNLOAD
 yes      UPDATE

    Note that DELETE, INSERT, TRUNCATE and UPDATE statements 
         are allowed, but they'll only work on tables you create, 
         not the pre-existing Foxhound tables. 

For Example: Active Alerts


There are two definitions for "Active Alert":
  1. An Alert that is currently active, and

  2. an Alert that was active at some earlier point in time.
Adhoc queries for the first kind are easy; there's a column for that:
SELECT *
  FROM alert
 WHERE alert_is_clear_or_cancelled = 'N' 
   AND sampling_id = 3;
Foxhound 5 displays the second kind in the new scroll to Active Alert drop-down shown in this earlier blog post:
Here's prototype query for the same thing, using a proposed view named "active_alert":
SELECT alert_number,
       alert_title
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726  
 ORDER BY alert_occurrence DESC;

alert_number alert_title               
------------ ------------------------- 
            1 Database unresponsive      
           16 I/O operations        
           13 File fragmentation    

Do-It-Yourself: CREATE VIEW ADHOC.active_alert

With Foxhound 5 you can now CREATE your own views:
CREATE VIEW ADHOC.active_alert AS
SELECT sample_header.sampling_id,
       sample_header.sample_set_number,
       alert.alert_occurrence,
       alert.alert_number,
       LEFT ( alert_title.alert_title, 25 ) AS alert_title,
       active_alert_range.clear_or_cancelled_locator_number
  FROM sample_header
          LEFT OUTER JOIN active_alert_range
                       ON active_alert_range.sampling_id                       =  sample_header.sampling_id
                      AND active_alert_range.active_locator_number             <= sample_header.sample_set_number
                      AND active_alert_range.clear_or_cancelled_locator_number >  sample_header.sample_set_number 
          LEFT OUTER JOIN alert
                       ON alert.alert_occurrence = active_alert_range.active_locator_number
          LEFT OUTER JOIN alert_title
                       ON alert_title.alert_number = alert.alert_number;
The Foxhound drop-down list box uses a query like this to display first two columns, and it also uses the third column (alert_occurrence) to implement the "scroll to Active Alert" feature:
SELECT alert_number,
       alert_title,
       alert_occurrence
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726 
 ORDER BY alert_occurrence DESC;

alert_number alert_title                   alert_occurrence 
------------ ------------------------- -------------------- 
          13 File fragmentation                        2703 
          16 I/O operations                            2441 
          13 File fragmentation                         155 
For example, when the user clicks on "#16 I/O operations" the History page is scrolled to the Alert #16 on line 2441:
When you use active_alert in your own adhoc queries, you have access to several columns including clear_or_cancelled_locator_number which tells you when (and if) each Alert became inactive.

Here's what the whole result set looks like:
SELECT *
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726  
 ORDER BY alert_occurrence DESC;

sampling_id    sample_set_number     alert_occurrence alert_number alert_title               clear_or_cancelled_locator_number 
----------- -------------------- -------------------- ------------ ------------------------- --------------------------------- 
          3                 2726                 2703            1 Database unresponsive                                  2745 
          3                 2726                 2441           16 I/O operations                                         2830 
          3                 2726                  155           13 File fragmentation                                     2829 
The active_alert view is based on several views built-in to Foxhound:
active_alert_range   One row per alert containing from and to locator numbers and timestamps for 
                        the range of lines in the History page for which this Alert was active.
alert                One row per alert.
alert_cancelled      One row per alert cancellation; indirectly referenced by active_alert_range.
alert_title          One row for the text title corresponding to each Alert number.
all_clear            One row per alert all-clear; indirectly referenced by active_alert_range.
sample_header        One row for each sample recorded by the Foxhound Database Monitor, 
                        holding various server and database-level properties and computed columns.
For more information about writing and running adhoc SQL in Foxhound 5, see this chapter in the Help.



No comments: