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.

---