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":
- An Alert that is currently active, and
- 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.
