Pages

Saturday, March 18, 2017

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.

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).

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:

Step 4: Gather Data In A FOR Loop

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.

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.