Help for Foxhound 5.0.5432a

Table of Contents     [RisingRoad]
search engine by freefind advanced


Foxhound 5 

Introduction and Setup

Foxhound is two products in one: it is both a database monitor and a schema troubleshooter for SQL Anywhere.

A database monitor is a computer program that measures the activity of a database management system and displays those measurements in a meaningful way, so that you can easily see that everything's OK or quickly learn about problems and threats to performance and availability. A monitor can also send email alerts and even drop connections when something goes wrong.

A schema troubleshooter is a computer program that performs a static analysis of the tables, columns, indexes, foreign key relationships and other objects in the database, displays those objects in a way that's easy to understand and easy to navigate, together with various facts, figures and curiosities which may be problems that need attention.

Windows Start Menu

Hallmarks of Foxhound

Requirements

Limitations

Foxhound Setup

Starting Foxhound

Customizing the Setup

Running Foxhound as a Service

Connecting to Target Databases

Backup and Restore

Controlling Growth

Safe Mode Startup

Starting Multiple Copies of Foxhound

Offline Foxhound Sampling Service (OFSS)

Environment Variables

Scripts Subfolder

End User License Agreement

Support and Consulting

How to CALL unload_samples()

SAP® SQL Anywhere® is a trademark of SAP AG.


Foxhound 5 » Introduction and Setup 
Windows Start Menu

Here's what the Windows - Start - Foxhound5 menu looks like:

1 Start Foxhound See Starting Foxhound
2 Adhoc Query via ISQL See Adhoc Queries
3 Backup Database See Full Backup and Restore
4 Help Browse this Help on your local hard drive
5 FAQ Browse the Foxhound Frequently Asked Questions on the Web
6 Debug Start Foxhound See Starting Foxhound Method 2: Debug Startup
7 Start Foxhound Engine See Starting Foxhound Method 3: Do-It-Yourself
8 Stop Foxhound Engine Shut down the dbsrv17.exe that is running Foxhound
9 Mini-Restore See Mini-Restore
10 Unsetup Foxhound Use this before reinstalling Foxhound or upgrading to a new build


Foxhound 5 » Introduction and Setup 
Hallmarks of Foxhound

Here's a list of Foxhound characteristics you can take advantage of:

  1. Functionality out of the box  Foxhound just requires a connection string or DSN to get started; the next thing you see is your database in the Foxhound Monitor page; there are no widgets or wizards to deal with, and no need to pick and choose which statistics to display.

  2. Guidance  Foxhound provides on-screen guidance for all the data it displays and all the options it offers. The column titles, tooltip text, field descriptions, context-sensitive Help and performance tips are all designed to help you understand what you are looking at and what you can do about it.

  3. Information at a glance  Foxhound presents a straightforward interface for professional developers with no graphs, waveforms or "data visualization" artwork. All the relevant data and value-added calculations are gathered together on single pages with peak highlighting and hypertext links to scroll through history and open different views in new browser tabs.

  4. Open access  Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it. The Foxhound Help contains sample queries, and to make your own queries easier to write important internal primary key values are displayed together with the data on the Monitor and other pages; e.g., sampling_id, sample_set_number and the new connection_id_string.

  5. Legacy support  Foxhound supports target databases running all versions of SQL Anywhere from 6 to 17. And while it's true that older versions of SQL Anywhere don't provide (and Foxhound doesn't display) modern performance statistics, statistics that are available do get displayed, not lowest common denominators.

  6. Robustness  The Foxhound Monitor keeps on trying to connect to the target database during an outage. Foxhound also silently handles invalid performance statistics, and it keeps working after it throws an exception, thus meeting the definition of robustness: "The degree to which a system or component can function correctly in the presence of invalid inputs or stressful environmental conditions."

  7. Dogfooding  Foxhound is regularly run against the Foxhound database itself; to test new features, sure, but primarily to look for performance bottlenecks. Foxhound is also the first tool of choice on client performance and tuning assignments; that's how the Foxhound Monitor was created in the first place, on assignment in 2003 as a Blocked Connection Display for a SQL Anywhere 9 database at UNO Money Transfers. The Schema Display feature was created even earlier, in 1998, for an immense Oracle database at Princess Margaret Hospital... which makes 2018 the twentieth anniversary of Foxhound!


Foxhound 5 » Introduction and Setup 
Requirements

1. Windows - Foxhound works with target databases running on other operating systems but the Foxhound engine itself only runs on Windows, and has been tested on the 64-bit version of Microsoft Windows 10.

2. ODBC - Foxhound itself uses ODBC to connect to your target databases. You don't necessarily need ODBC installed on the servers running your target databases, and you can specify DSN-less connections for Foxhound by using the "String" tab on the main menu page.

3. Chrome, Firefox - The Foxhound client has been tested with the latest versions of Chrome and Firefox.

4. JavaScript - Foxhound needs "JavaScript" or "Active scripting" to be enabled in your browser.

5. SQL Anywhere 6, 7, 8, 9, 10, 11, 12, 16 and 17 for target databases - Foxhound has also been tested with target databases created with SQL Anywhere 5.5 and running on SQL Anywhere 6 software.

6. SQL Anywhere 17 for Foxhound - The Foxhound database must run on its own instance of the 32-bit or 64-bit SQL Anywhere 17 network server build 4882 or later. This restriction does NOT apply to your target databases which can run on any version of SQL Anywhere 6, 7, 8, 9, 10, 11, 12, 16 and 17.

7. SQLANY17 - The delivered Foxhound shortcuts expect that the SQLANY17 environment variable will be created by the SQL Anywhere 17 installation (which will happen by default). If that is not the case you may have to modify the corresponding *.bat files. Here is the default setting for Windows 10:

   SET SQLANY17=C:\Program Files\SQL Anywhere 17 

8. FOXHOUND5 - The FOXHOUND5 environment variable is created by the Foxhound installation to contain the drive and path of the folder where Foxhound is installed. It is no longer required by the delivered Windows shortcuts or by any of the code inside Foxhound. It is used by several of the optional Windows command files in the scripts subfolder, and by the $post_setup.bat file which is launched by the Foxhound installation process. Here is the default setting for Windows 10:

   SET FOXHOUND5=C:\ProgramData\RisingRoad\Foxhound5\

9. Disk space - The Foxhound database file may grow in size at a rate of 100K per hour per target database connection... or more; your mileage may vary.

10. OFSS - The Offline Foxhound Sampling Service (OFSS) is only supported for subject databases running on SQL Anywhere 12, 16 and 17.

11. Data Upgrade - The Foxhound 5 installation process will copy and upgrade the data from an existing Foxhound Version 3, 4 or 5 database, but it will ignore a Version 1 or 2 database.


Foxhound 5 » Introduction and Setup 
Limitations

Here are the limitations of the Rental, Basic and Extended Editions of Foxhound:


General Characteristics
Rental

Basic

Extended

Type of registration key
required for activation

Rental Basic Extended
How you obtain a
registration key

Purchase online Purchase online Purchase online
Installed copy
will expire

Yes, when the
rental period
expires

No No
Installed copy may
be renewed before
or after expiry date

Yes, via purchase of
another Rental
registration key

- -
How is the new expiry
date for a renewal
calculated?

By adding a new rental
period to the current
date or existing expiry
date, whichever is later

- -
Activation upgrade to a
different edition
is available

Yes, via purchase of a
New Basic Key or a
New Extended Key
Yes, via purchase of a
Basic To Extended Key
-
Adhoc reporting is
always available

Yes, even after
the rental period
expires

Yes

Yes


Limitations on the Database Monitor
Rental

Basic

Extended

Schedules may be used
to control when samples
are recorded

No No Yes
Schedules may be used
to control when Alert
emails are sent

No No Yes
The AutoDrop feature may
be used to automatically
drop errant connections

No No Yes
The DEDICATED_TASK
option may be set for
Foxhound connections
to target databases

No No Yes
The Foxhound custom 'ping'
process may be run in
addition to, and instead of,
the Foxhound sample process

No No Yes
Maximum number of
connections to each
target database

100 * 100 * No limit
Maximum number of physical
CPUs used for each target
database on a SQL Anywhere
Version 10, 11, 12, 16, 17 server

1 ** 1 ** No limit
Target database may run on a
SQL Anywhere 11.0.1 or later
Standard or Advanced Edition
server

No *** No *** Yes
Maximum number of target
databases which may be
monitored at the same time
by a single copy of Foxhound

10 10 100
Maximum number of different
server names which may be
used when starting separate
instances of Foxhound ****

1 **** 1 **** Unlimited ****

* This limit on connections applies to all versions and editions of SQL Anywhere target databases and servers. The actual limit is 101 connections when you count the connection from Foxhound itself. Internal connections (e.g., connections made by events and web services) are counted in the total.

** This limit on physical CPUs does not apply to SQL Anywhere 5.5, 6, 7, 8 or 9 target databases and servers. Also, it applies to the number of physical processors as given by the NumPhysicalProcessorsUsed server property, not the number of logical processors shown by "Using n CPUs" value in the Foxhound Database Monitor. For example, a single Intel Pentium 4 processor counts as 1 physical processor even though the separate HyperThreading units may show up as "Using 2 CPUs". Similarly, a single Intel Core2 Quad processor also counts as only 1 physical processor even though it may show up as "Using 4 CPUs".

*** This restriction on SQL Anywhere editions does not apply to SQL Anywhere target databases and servers earlier than version 11.0.1, which is when SQL Anywhere introduced the notion of different editions.

**** The Rental and Basic Editions allow only one instance of Foxhound to be started on the same local network, and the SQL Anywhere server name must be "foxhound5". The Extended Edition allows an unlimited number of Foxhound instances to be started on the same local network, with no restrictions on the SQL Anywhere server names; however, the License Agreement requires that a separate Extended Edition registration key be purchased for each multiple of 10 instances started.


Foxhound 5 » Introduction and Setup 
Foxhound Setup

Foxhound consists of server and client components.

The Foxhound server (or "engine") runs as a single SQL Anywhere database in a Version 16 or 17 network server configured to run as an HTTP (web) server.

The Foxhound client is browser based, with the default URL being this:

http://localhost:80/

The standard Foxhound installation creates Windows Start menu shortcuts to batch files that start both the Foxhound engine and the Foxhound client on the same computer.


Foxhound 5 » Introduction and Setup 
Starting Foxhound

Starting Foxhound is a two-step process: Starting the Foxhound database in a SQL Anywhere engine, and connecting to the Foxhound HTTP server from your browser.

Method 1: Normal Startup

This Windows shortcut does both steps, start the database and open the browser:

Foxhound5 - 1 Start Foxhound
The shortcut above runs dbping to determine if the Foxhound database is already running, and if it is, it skips the dbsrv17 step and immediately start the browser.

Method 2: Debug Startup

The following "debug" shortcut is also available:

6 Debug Start Foxhound

It does exactly the same thing as the Foxhound5 - 1 Start Foxhound shortcut, except:

Method 3: Do-It-Yourself

You can also start the Foxhound database and open the browser in separate steps:

Foxhound5 - 7 Start Foxhound Engine

Specify http://localhost in your browser.

By default the Foxhound shortcuts use the 64-bit of SQL Anywhere if it exists, otherwise they use the 32-bit version.

You can override those choices with the FOXHOUND5BIN environment variable.

This applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.

Here's how those choices are made by the command files used by the Foxhound shortcuts:

IF EXIST "!SQLANY17!\Bin32" (SET BIN=Bin32)
IF EXIST "!SQLANY17!\Bin64" (SET BIN=Bin64)
IF /I "%FOXHOUND5BIN%z"=="Bin32z" (SET BIN=Bin32)
IF /I "%FOXHOUND5BIN%z"=="Bin64z" (SET BIN=Bin64)

Here's the list of Foxhound command files which contain the dbsrv17.exe command:

C:\ProgramData\RisingRoad\Foxhound5\

    $backup_foxhound5.bat
    $start_foxhound5_default_browser_debug.bat
    $start_foxhound5_default_browser.bat
    $start_foxhound5_engine.bat

C:\ProgramData\RisingRoad\Foxhound5\scripts

    $create_SQL_Anywhere_17_bin64_service.bat
    $MULTI_Step_2_dbsrv_all.bat
    $MULTI_Step_2b_dbsrv_one.bat
    $run_most_recent_mini_restore.bat
    $TEN_INSTANCES_2_dbsrv.bat 


Foxhound 5 » Introduction and Setup 
Customizing the Setup Here's the list of Foxhound command files which contain the dbsrv17.exe command:
C:\ProgramData\RisingRoad\Foxhound5\

    $backup_foxhound5.bat
    $start_foxhound5_default_browser_debug.bat
    $start_foxhound5_default_browser.bat
    $start_foxhound5_engine.bat

C:\ProgramData\RisingRoad\Foxhound5\scripts

    $create_SQL_Anywhere_17_bin64_service.bat
    $MULTI_Step_2_dbsrv_all.bat
    $MULTI_Step_2b_dbsrv_one.bat
    $run_most_recent_mini_restore.bat
    $TEN_INSTANCES_2_dbsrv.bat 

You can customize those batch files, or create new ones as you see fit. Each one contains the following command or a variation thereof:

"!SQLANY17!\!BIN!\dbspawn.exe"^
  -f "!SQLANY17!\!BIN!\dbsrv17.exe"^
  -c 25p^
  -ch 50p^
  -cr-^
  -gk all^
  -gl all^
  -gn 220^
  -gna 0^
  -m^
  -n foxhound5^
  -o foxhound5_debug.txt^
  -oe foxhound5_debug_startup.txt^
  -on 1M^
  -qn^
  -sb 0^
  -ufd restart^
  -x tcpip^
  -xd^
  -xs http(port=80;maxsize=0;to=600;kto=600)^
  foxhound5.db^
  -n f

Here is a description of each part of the command:


Foxhound 5 » Introduction and Setup 
Running Foxhound as a Service

Foxhound is often used to monitor heavily-loaded target databases, and in such an environment some care must be taken to ensure Foxhound's own CPU and disk requirements don't adversely affect overall performance. The simplest solution is to run the Foxhound engine on its own computer that is:

It is often more important to locate the Foxhound engine close to the target database, than to locate the client-side web browser close to the Foxhound engine. That's because the communication between Foxhound and the target database is more intense and more susceptible to long-range response time (latency) issues than is the HTTP traffic between the browser and Foxhound.

Having said all that, a centrally-located Foxhound engine is easier to set up and administer when it is run as a Windows service using the dbsrv17.exe network server.

You can use Sybase Central or the dbsvc.exe utility to create Windows service; both techniques are described in the SQL Anywhere Help.

The Foxhound Samples folder contains a command file that deletes and create a Windows service for running Foxhound using the 64-bit version of SQL Anywhere 17. It also displays the service parameters after it is created, and then lists all the SQL Anywhere-related services that exist:

C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat

Note: When you run Foxhound as a service, you will probably have to create System DSNs instead of User DSNs if you want your target databases to appear on the DSN tab of the Foxhound Menu page.

You may also have to launch the ODBC Administrator from Control Panel - Administrative Tools rather than from the Foxhound Menu page, and you may have to specify TCP/IP rather than Shared Memory for the Foxhound connections to your target databases even if they are running on the same computer.

Also, those DSNs must be set up on the same computer that is running the Foxhound engine. In other words, if you move the Foxhound engine from your workstation to a server somewhere else, you will have to move or copy the DSNs over there as well.


Foxhound 5 » Introduction and Setup 
Connecting to Target Databases

Foxhound uses ODBC to connect to the target databases. If you have an ODBC DSN, you can choose it on the DSN tab of the Foxhound Menu page.

If you don't have a DSN, or don't want to use one, you can specify a DSN-less connection by specifying the connection string on the String tab of the Menu page. The main difference between using the String tab and setting up a DSN in the ODBC Administrator is the String tab needs to know what driver to use, as in:

DRIVER=SQL Anywhere Native;

GRANT CONNECT to Foxhound

Foxhound needs to connect to the target database in order to collect Monitor statistics and display schema information. For most purposes, a user id with CONNECT permission is sufficient:

GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';

   -- or --

CREATE USER FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';

That user id can then be used to connect to a SQL Anywhere database using a connection string like this:

Name: ddd

String: ENG=ddd; DBN=ddd; UID=FOXHOUND; PWD=ZAdt5Yq8; DRIVER=SQL Anywhere Native; 

Debugging ODBC Connections

Tip: To debug problems with Foxhound's ODBC connection to your database, try enabling the ODBC LOG file:

Name: ddd

String: ENG=ddd; DBN=ddd; UID=FOXHOUND; PWD=ZAdt5Yq8; DRIVER=SQL Anywhere Native;  LOG=C:\TEMP\LOG.TXT;

GRANT Other Permissions to Foxhound

A user id with only CONNECT permission does have some limitations, depending on the version of SQL Anywhere being used to run the target database.

Tip: For a SQL Anywhere Version 12 or earlier target database, here's how to let the FOXHOUND user id do everything EXCEPT the following:

  1. Perform AutoDrop processing shown in section 12. AutoDrop Criteria of the Monitor Options page.
    Message: Permission denied: you do not have permission to disconnect "xxx"

  2. Use any actions in section 14. Change Target Settings of the Monitor Options page
    Message: Permission denied: you do not have permission to execute the procedure "sa_server_option"

  3. Set the DEDICATED_TASK option in section 15. Monitor Connection Settings of the Monitor Options page.
    Message: Only the DBA can set the option 'dedicated_task' SQLCODE=-204, ODBC 3 State="42000"

    GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';
    GRANT RESOURCE TO FOXHOUND;
    GRANT EXECUTE ON dbo.sa_locks TO FOXHOUND;
    GRANT EXECUTE ON dbo.sa_disk_free_space TO FOXHOUND;
    

Tip: For a SQL Anywhere Version 16 or later target database, here's how to let the FOXHOUND user id do everything with no exceptions:

CREATE USER FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';
GRANT RESOURCE TO FOXHOUND;
GRANT MANAGE ANY DBSPACE TO FOXHOUND;
GRANT MONITOR TO FOXHOUND;
GRANT DROP CONNECTION TO FOXHOUND;
GRANT SET ANY SYSTEM OPTION TO FOXHOUND;
GRANT SERVER OPERATOR TO FOXHOUND;

Tip: If you are willing to GRANT DBA TO FOXHOUND then all the problems go away.

If not, here's a table introducing the symptoms and solutions:

Target SQL
Anywhere  
Version          Symptom                     Reason                  Solution(s)
================================================================================================
                                                                     GRANT CONNECT TO FOXHOUND 
                                                                       IDENTIFIED BY 'ZAdt5Yq8';
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound Monitor displays   Foxhound is not         GRANT RESOURCE TO FOXHOUND;
                 "SPs NNN" and may perform   allowed to CREATE    
                 the target database.        three special              -- or --
                                             stored procedures          
                                             on the target           CREATE the procedures 
                                             database.               manually using SQL files 
                                                                     delivered with Foxhound
                                                                       (see below).
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound doesn't display    Foxhound is not         GRANT EXECUTE ON
                 free space on the target    allowed to call the       dbo.sa_disk_free_space
                 database disk drives,       sa_disk_free_space()      TO FOXHOUND;
                 and doesn't issue Alerts    procedure on the        
                 5, 6, 7 or 8 about low      target database.        -- or --
                 disk space.
                                                                     GRANT DBA TO FOXHOUND;

                                                                     -- this solves all the
                                                                        other problems too.
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound doesn't display    Foxhound is not         GRANT EXECUTE ON
                 detailed information        allowed to call the       dbo.sa_locks
                 about why a connection is   sa_locks() procedure      TO FOXHOUND;
                 blocked.                    on the target                  
                                             target database.        -- or --

                                                                     GRANT DBA TO FOXHOUND;

                                                                     -- this solves all the
                                                                        other problems too.
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound can't AutoDrop     Foxhound is not         GRANT DBA TO FOXHOUND;
                 any connections on the      allowed to execute 
                 target database.            the DROP CONNECTION     -- this solves all the
                                             statement on the           other problems too.        
                                             target database.   
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound can't set the      Foxhound is not         GRANT DBA TO FOXHOUND;
                 DEDICATED_TASK option on    allowed to execute
                 the target database.        the SET TEMPORARY       -- this solves all the
                                             OPTION DEDICATED_TASK      other problems too.
                                             statement on the          
                                             target database.   
--------------   -------------------------   ---------------------   ---------------------------
12 and earlier   Foxhound can't make the     Foxhound is not         GRANT DBA TO FOXHOUND;
                 changes specified by the    allowed to call the
                 Change Target Settings      sa_server_option()      -- or --
                 section of the Monitor      procedure on the        
                 Option page.                target database.        Use dbsrv12 -zp -zl -zt   
================================================================================================
                                                                     CREATE USER FOXHOUND 
                                                                       IDENTIFIED BY 'ZAdt5Yq8';
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound Monitor displays   Foxhound is not         GRANT RESOURCE TO FOXHOUND;
                 "SPs NNN" and may perform   allowed to CREATE    
                 slowly.                     three special           -- or --
                                             stored procedures          
                                             on the target           CREATE the procedures 
                                             database.               manually using SQL files 
                                                                     delivered with Foxhound 
                                                                       (see below).
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound doesn't display    Foxhound is not         GRANT MANAGE ANY DBSPACE
                 anything in Free Disk       allowed to call the       TO FOXHOUND;
                 Space Sys, Temp, Log,       sa_disk_free_space()
                 Other, and doesn't issue    procedure on the         
                 Alerts 5, 6, 7 or 8 about   target database.
                 low disk space.                                      
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound Monitor doesn't    Foxhound is not         GRANT MONITOR TO FOXHOUND;
                 gather information about    allowed to call the 
                 any connections other       CONNECTION_PROPERTY() 
                 than Foxhound's own         function on the             
                 connection to the target    target database for     
                 database.                   any other connections. 
                                             Foxhound is also not
                                             allowed to call the
                                             sa_locks() procedure
                                             on the target 
                                             database.
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound can't AutoDrop     Foxhound is not         GRANT DROP CONNECTION
                 any connections on the      allowed to execute        TO FOXHOUND;
                 target database.            the DROP CONNECTION 
                                             statement on the           
                                             target database.   
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound can't set the      Foxhound is not         GRANT SET ANY SYSTEM OPTION
                 DEDICATED_TASK option on    allowed to execute        TO FOXHOUND;
                 the target database.        the SET TEMPORARY 
                                             OPTION DEDICATED_TASK  
                                             statement on the          
                                             target database.   
--------------   -------------------------   ---------------------   ---------------------------
16 and later     Foxhound can't make the     Foxhound is not         GRANT SERVER OPERATOR 
                 changes specified by the    allowed to call the       TO FOXHOUND;
                 Change Target Settings      sa_server_option()
                 section of the Monitor      procedure on the        -- or --
                 Option page.                target database.           
                                                                     Use -zp -zl -zt 
================================================================================================

All the solutions use simple GRANT statements shown above, except one; here's the full story on the special Foxhound stored procedures:

How To Install The Foxhound SPs On Non-OFSS Target Databases

Three stored procedures are delivered with Foxhound for installation on your target databases.

These procedures are optional but highly recommended:

  • rroad_connection_properties greatly improves Foxhound performance when the target database has a large number of client connections.

  • rroad_database_properties improves Foxhound performance when gathering database-level properties, as well as enabling these Foxhound display fields:
    • Log Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'translog' )
    • Log Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'translog' )
    • Temp Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'temp' )
    • Temp Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'temp' )
    • Temp Frags - based on DB_EXTENDED_PROPERTY ( 'DBFileFragments', 'temp' )

  • rroad_engine_properties improves Foxhound performance when gathering server-level properties.

Foxhound will automatically install and call these stored procedures if it can.

The phrase "if it can" means "the user id with which Foxhound connects to the target database has the RESOURCE authority" as in:

GRANT RESOURCE TO FOXHOUND;
Foxhound will keep trying to install those procedures each time it connects to the target database; e.g., each time sampling is stopped and started, or each time Foxhound is started.
Performance Tip: Here's how to force Foxhound to re-install those procedures on your target database:
  1. Stop Foxhound sampling on your database.

  2. Run these commands via ISQL on your database:

    DROP PROCEDURE rroad_connection_properties;
    DROP PROCEDURE rroad_database_properties;
    DROP PROCEDURE rroad_engine_properties;
    

    You might have to specify the user id that owns the procedures; i.e., the user id that Foxhound uses to connect to your database:

    DROP PROCEDURE FOXHOUND.rroad_connection_properties;
    DROP PROCEDURE FOXHOUND.rroad_database_properties;
    DROP PROCEDURE FOXHOUND.rroad_engine_properties;
    

  3. Start sampling on your target database; Foxhound should re-send those procedures when it discovers they don't exist.

As mentioned above, Monitor performance will suffer without those procedures, especially if the target database has a lot of connections. The Foxhound Monitor page displays SPs YYY if finds the three procedures, and SPs NNN if it doesn't.

. . . but Wait, There Is Another Way!

If you don't want to let the Foxhound user id to have either RESOURCE or DBA privileges on the target database, but you are willing to install those three procedures on the target database yourself, here's how:

(Note: If you choose to use this method, you will have to repeat these steps for each target database you wish to monitor, and again each time you upgrade to a new version or build of Foxhound.)
  1. Create a user id to be used by Foxhound on the target database; e.g.:
    GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';
    

  2. Find the following three text files in the Foxhound folder (which defaults to C:\ProgramData\RisingRoad\Foxhound5 on Windows 7):
    rroad_connection_properties.sql
    rroad_database_properties.sql
    rroad_engine_properties.sql
    

  3. Edit each file to specify the user id you chose in step 1; e.g., change {OWNER} to FOXHOUND:
    CREATE PROCEDURE FOXHOUND.rroad_connection_properties() 
    CREATE PROCEDURE FOXHOUND.rroad_engine_properties() 
    CREATE PROCEDURE FOXHOUND.rroad_database_properties(d INTEGER) 
    

  4. Use dbisql to run those files against the target database.

  5. If the Foxhound Monitor is already connected to the target database, click on Stop Sampling, then Start Sampling.

  6. Check to make sure "SPs YYY" appears on the Foxhound Monitor page.

Tip: One of the reasons to run Foxhound on the 32-bit version of SQL Anywhere is to make it easier to use the 32-bit version of ODBC to connect to your target database... but that may not be necessary!

For example, if you have an Adaptive Server Anywhere 9.0 target database you may be able to connect to it using a more recent SQL Anywhere ODBC driver (DRIVER=SQL Anywhere 10 through 16).

It's easiest to do this with Foxhound Menu - String tab rather than the DSN tab... you don't have to mess around with the ODBC Administrator.

The following connection string worked for Foxhound connecting to an Adaptive Server Anywhere 9.0.2.3951 database:

Name:   ddd9
String: ENG=ddd9; DBN=ddd9; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

This also works for SQL Anywhere 6.0.3 database running on a 6.0.4 (3594) server:

Name:   ddd6
String: ENG=ddd6; DBN=ddd6; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

However, neither DRIVER=SQL Anywhere 17 nor DRIVER=SQL Anywhere Native appear to work properly, when trying to connect to an Adaptive Server Anywhere 9.0 or earlier target; you may see this error message: The remote table 'pxxx..SYS.DUMMY' could not be found.


Foxhound 5 » Introduction and Setup
Backup and Restore

Mini-Backup

Mini-Restore

Full Backup

Full Restore

Transaction Log Backup

Transaction Log Restore


Foxhound 5 » Introduction and Setup » Backup and Restore 
Mini-Backup

The Mini-Backup process is a new feature in Foxhound 5 that automatically backs up all the changes you make to Foxhound options and settings:


Foxhound 5 » Introduction and Setup » Backup and Restore 
Mini-Restore

A Mini-Restore is the process whereby an empty Foxhound database is copied, activated, restored and restarted using a previously-created Mini-Backup file.

Mini-Restore Method 1: All Programs - Foxhound5 - 9 Mini-Restore

Mini-Restore Method 2: Run Most Recent Mini-Restore

Mini-Restore Method 3: Do It Yourself


Foxhound 5 » Introduction and Setup » Backup and Restore » Mini-Restore 
Mini-Restore Method 1: All Programs - Foxhound5 - 9 Mini-Restore

The simplest way to launch a Mini-Restore is to use this Windows shortcut:

All Programs 
   - Foxhound5 
      - 9 Mini-Restore

C:\ProgramData\RisingRoad\Foxhound5>ECHO OFF
Foxhound5 - 9 Mini-Restore
****************************************
CAUTION:
A Mini-Restore will restore an EMPTY
Foxhound database, with ONLY your
options and settings, but NO samples.
Are you sure you want to proceed?
Press any key to continue . . .

If there were any sampling sessions running when the Mini-Backup file was originally created, they will start sampling again when you restart Foxhound... but only new samples will be stored.

Administrative Tip: Any Alert #1 Database unavailable that was in effect before the Mini-Restore was run will not immediately re-appear after the Mini-Restore is run.

This behavior is a side-effect of the way Alert #1 is handled: "...no Alert #1 will be produced for a brand-new sampling session that hasn't recorded any successful samples yet no matter how much time has passed, but checking for Alert #1 starts as soon as a successful sample is recorded or sampling is stopped and started."

When you run a Mini-Restore, the current foxhound5.db and foxhound5.log files are renamed:

C:\ProgramData\RisingRoad\Foxhound5\ 

   foxhound5.db.RENAMED-AT-yyyy-mm-dd-Thh-nn-ss

   foxhound5.log.RENAMED-AT-yyyy-mm-dd-Thh-nn-ss

Old renamed files are automatically deleted after new ones are renamed.

You can change the retention count for these old files; see section 5 Mini-Restore on the Foxhound Options page.

Foxhound 5 » Introduction and Setup » Backup and Restore » Mini-Restore 
Mini-Restore Method 2: Run Most Recent Mini-Restore

If you already know that you want to restore the most recent Mini-Backup file, a script has been provided that skips the Foxhound Activation page:

C:\ProgramData\RisingRoad\Foxhound5\scripts$run_most_recent_mini_restore.bat

C:\ProgramData\RisingRoad\Foxhound5\scripts>ECHO OFF
Foxhound5 - Run Most Recent Mini-Restore
****************************************
CAUTION:
A Mini-Restore will restore an EMPTY
Foxhound database, with ONLY your
options and settings, but NO samples.
Are you sure you want to proceed?

Administrative Tip: This script depends on the FOXHOUND5 environment variable to determine where Foxhound resides, which is C:\ProgramData\RisingRoad\Foxhound5 by default.

That means you may have to copy and edit this script if you want it work for multiple copies of Foxhound stored in different locations, as described in Starting Multiple Copies of Foxhound.

Other scripts (such as the Windows shortcuts for starting Foxhound, etc.) no longer depend on FOXHOUND5.


Foxhound 5 » Introduction and Setup » Backup and Restore » Mini-Restore 
Mini-Restore Method 3: Do It Yourself

If you want to customize the Mini-Restore process, the scripts for Method 1 and Method 2 contain all the code you need to copy and paste:

Method 1: C:\ProgramData\RisingRoad\Foxhound5\$mini_restore_foxhound5.bat

Method 2: C:\ProgramData\RisingRoad\Foxhound5\scripts\$run_most_recent_mini_restore.bat

The main difference between the two is that Method 2 runs a SQL script before launching the browser, and that SQL script automates Method 1's "Select a file and click on Run Mini-Restore":

"!SQLANY17!\!BIN!\dbisql.com"^
  -c "ENG=foxhound5; DBN=f; UID=ADHOC; PWD=SQL; CON=Mini-Restore"^
  CALL rroad_run_most_recent_mini_restore()

Note: Don't try to CALL rroad_run_most_recent_mini_restore() without first replacing foxhound5.db with a copy of foxhound5.db.5.0.bbbb.ORIGINAL_COPY.

If you do, the CALL will be ignored with an error message like this:

CALL rroad_run_most_recent_mini_restore()
-- A Mini-Restore is starting.
-- Mini-Restore bypassed because the sampling tables were not empty
-- Procedure completed


Foxhound 5 » Introduction and Setup » Backup and Restore 
Full Backup

The Start - Foxhound5 - 3 Backup Database shortcut runs the SQL Anywhere dbbackup.exe utility to create a full backup of the foxhound5.db and foxound.log files.

The Full Backup saves three generations (copies) of the backup files before it starts deleting older copies. The most recent backup is always saved in the generation3 subfolder, and the oldest in generation1.

Here's what the backup folders contain after the Full Backup has been run three or more times:

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup

07/03/2019  07:27 AM    <DIR>          generation1
07/03/2019  09:03 AM    <DIR>          generation2
07/03/2019  10:21 AM    <DIR>          generation3

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation1

07/03/2019  07:27 AM               535 backup_log.txt
07/03/2019  07:27 AM        41,435,136 foxhound5.db
07/03/2019  07:27 AM           344,064 foxhound5.log

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation2

07/03/2019  09:03 AM               535 backup_log.txt
07/03/2019  09:03 AM        42,795,008 foxhound5.db
07/03/2019  09:03 AM           352,256 foxhound5.log

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3

07/03/2019  10:27 AM             2,442 backup_log.txt
07/03/2019  10:20 AM        45,072,384 foxhound5.db
07/03/2019  10:20 AM         2,940,928 foxhound5.log


Foxhound 5 » Introduction and Setup » Backup and Restore 
Full Restore

Here's how to restore the Foxhound database from one of the full backups (in this case, the most recent one):

  1. Stop Foxhound (if it is still running).

  2. Delete or move the Foxhound files (if they still exist):

    C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log
    

  3. Copy the backup files:

    from:  C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.db
           C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.log
    
      to:  C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
           C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log
    

  4. Start Foxhound.


Foxhound 5 » Introduction and Setup » Backup and Restore 
Transaction Log Backup

 Caution: Foxhound 5 does not support transaction log backups... unless you make some changes. 

If you want to make transaction log backups in between full backups, you will first have to stop Foxhound from truncating the transaction log every time it takes a checkpoint.

To do that, you have to remove the dbsrv17 -m option from the Windows command files you will be using to start Foxhound; here's a list of all those files delivered with Foxhound:

C:\ProgramData\RisingRoad\Foxhound5\

    $backup_foxhound5.bat
    $start_foxhound5_default_browser_debug.bat
    $start_foxhound5_default_browser.bat
    $start_foxhound5_engine.bat

C:\ProgramData\RisingRoad\Foxhound5\scripts

    $create_SQL_Anywhere_17_bin64_service.bat
    $MULTI_Step_2_dbsrv_all.bat
    $MULTI_Step_2b_dbsrv_one.bat
    $run_most_recent_mini_restore.bat
    $TEN_INSTANCES_2_dbsrv.bat 

Here's the change you'll have to make:

"!SQLANY17!\!BIN!\dbspawn.exe"^
  -f "!SQLANY17!\!BIN!\dbsrv17.exe"^
  -c 25p^
  -ch 50p^
  -cr-^
  -gk all^
  -gl all^
  -gn 220^
  -gna 0^
  -m^              <== remove this line
  -n foxhound5^
  -o foxhound5_debug.txt^
  -oe foxhound5_debug_startup.txt^
  -on 1M^
  -sb 0^
  -ufd restart^
  -x tcpip^
  -xd^
  -xs http(port=80;maxsize=0;to=600;kto=600)^
  foxhound5.db^
  -n f

Once Foxhound is preserving the transaction log (no more dbsrv17 -m) you can run this script file every time you want to create an incremental transaction log file backup:

C:\ProgramData\RisingRoad\Foxhound5\scripts\

$backup_foxhound5_log.bat

The term "incremental log file backup" means that each log backup contains the changes made only since the previous log backup. In turn, that means a restore will have to apply every single log backup to the restored database file.

The $backup_foxhound5_log.bat script runs the dbbackup.exe utility with these options:

dbbackup.exe"^
  -c "ENG=foxhound5;DBN=f;UID=BACKER;PWD=SQL"^
  -o backup\generation3\backup_log.txt^
  -n^                      - yymmddxx.log naming convention
  -t^                      - create a transaction log backup only
  -x^                      - delete and restart the current transaction log
  backup\generation3\logs  - where to put the log backup files 

Note that the dbbackup -x option has the same effect as dbsrv17 -m; i.e., it truncates the current log file, only a lot less frequently.

Here's what the backup folders look like after the most recent full backup has been followed by five runs of the $backup_foxhound5_log.bat script:

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3

07/03/2019  10:27 AM             2,442 backup_log.txt
07/03/2019  10:20 AM        45,072,384 foxhound5.db
07/03/2019  10:20 AM         2,940,928 foxhound5.log

 Directory of C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs

07/03/2019  10:21 AM           483,328 190703AA.log
07/03/2019  10:23 AM            73,728 190703AB.log
07/03/2019  10:24 AM            81,920 190703AC.log
07/03/2019  10:26 AM         1,024,000 190703AD.log
07/03/2019  10:27 AM         1,064,960 190703AE.log


Foxhound 5 » Introduction and Setup » Backup and Restore 
Transaction Log Restore

If you have run a Full Backup followed by one or more Transaction Log Backups, you can run a Transaction Log Restore; here's an example.

  1. Stop Foxhound (if it is still running).

  2. Delete or move the Foxhound files (if they still exist):

    C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    C:\ProgramData\RisingRoad\Foxhound5\foxhound5.log
    

  3. Copy the Full Backup database files:

    from:  C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.db
    
      to:  C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    

  4. Choose (or create) an empty temporary folder; e.g., c:\temp.

  5. Copy the Full Backup transaction log file, plus one or more Temporary Log Backup files, to the temporary folder.

    In this case, only the first two Temporary Log Backup files are copied because the changes made after that point are no longer desired:

    from:  C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\foxhound5.log
           C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs\190703AA.log
           C:\ProgramData\RisingRoad\Foxhound5\backup\generation3\logs\190703AB.log
    
      to:  C:\temp\foxhound5.log
           C:\temp\190703AA.log
           C:\temp\190703AB.log
    

  6. Create, edit and run this Windows command file to run the restore:

    REM c:\temp\restore.bat
    "%SQLANY17%\bin64\dbsrv17.exe"^
      -gn 220^
      -o C:\ProgramData\RisingRoad\Foxhound5\foxhound5_debug.txt^
      -oe C:\ProgramData\RisingRoad\Foxhound5\foxhound5_debug_startup.txt^
      -on 1M^
      C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db^
      -ad C:\temp
    PAUSE
    

    The dbsrv17 -ad C:\temp option tells SQL Anywhere to apply all the transaction log files in C:\temp to the foxhound5.db file, and then stop the engine.

    The foxhound5_debug.txt file shows that SQL Anywhere is smart enough to know which order to apply the log files in:

    I. 07/03 10:31:12. SQL Anywhere Network Server Version 17.0.9.4882I. 07/03 10:31:12. Developer edition, not licensed for deployment.
    ...
    I. 07/03 10:31:12. Starting database "foxhound5" (C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:12. Database recovery in progress
    I. 07/03 10:31:12.     Last checkpoint at Wed Jul 03 2019 10:19
    I. 07/03 10:31:12.     Checkpoint log...
    I. 07/03 10:31:13.     Transaction log: C:\temp\foxhound5.log...
    I. 07/03 10:31:13.     Parallel recovery enabled
    I. 07/03 10:31:13. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:13. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:13.     Transaction log: C:\temp\190703AA.log...
    I. 07/03 10:31:13. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:13. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15.     Transaction log: C:\temp\190703AB.log...
    I. 07/03 10:31:15. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:15. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:16. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:16. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:16.     Checkpointing...
    I. 07/03 10:31:16. Starting checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:16. Finished checkpoint of "foxhound5" (foxhound5.db) at Wed Jul 03 2019 10:31
    I. 07/03 10:31:16. Recovery complete
    I. 07/03 10:31:16. Database server shutdown automatically after log applied
    I. 07/03 10:31:18. Database server stopped at Wed Jul 03 2019 10:31
    

  7. Start Foxhound.


Foxhound 5 » Introduction and Setup 
Controlling Growth

The Foxhound Monitor process will cause the Foxhound database to grow in size by as much as several gigabytes per day, if target databases have hundreds or thousands of connections.

There are several ways you can limit the growth of the Foxhound database:

The growth of the Foxhound transaction log is limited by the dbsrv17 -m option which tells SQL Anywhere to truncate the transaction log whenever a checkpoint is performed.

Performance Tip: If you want to start all the Monitor sampling sessions over again, try getting rid of all the old sample data with the Mini-Restore feature; it will

Performance Tip: If you are in the habit of creating and deleting sample sessions, but you don't want to purge data "after [xxx] day(s)", try this: enable the automatic purge schedule but disable purging of all data after [xxx] days.

Here's how:

Foxhound Options - section 6. Purge 
   check    Enable automatic purge schedule 
   uncheck  Enable purging of all data after [xxx] day(s)
   click    Save

Here's why it works: the Purge process will always remove orphan data (those rows that remain after you delete a sampling session), even when purging of all data after [xxx] days is disabled.

Orphan data is useless, and it only hangs around because the "delete sampling session" process is designed to respond quickly rather than actually delete all the rows right away.

Performance Tip: The quickest way to shrink the foxhound5.db file is to re-install Foxhound and carefully choose the value for FOXHOUND5UPGRADE; here's how:

Go through the setup until it asks this question:

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=

The default ALL will copy all the data and leave some free space, so this might not be what you want... it uses ALTER DBSPACE on the new file to allocate the free space.

The most thorough is OPTIONS which throws away all the samples BUT keeps all your settings, so when you start Foxhound it will immediately start gathering samples again; the database file will be verrrrry small. This is pretty much the same as running a Mini-Restore.

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=OPTIONS

The coolest FOXHOUND5UPGRADE value 100000, as in "upgrade the settings plus all the samples recorded in the last 100000 days" which is effectively ALL the samples... but ALTER DBSPACE is NOT run; the database file will be no bigger than necessary.

Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=100000

For more information about these and other values see FOXHOUND5UPGRADE.


Foxhound 5 » Introduction and Setup 
Safe Mode Startup

If Foxhound takes too long to start up, perhaps because there are a large number of Monitor sessions to start or because Foxhound simply becomes unresponsive, you can try starting Foxhound in "safe mode".

Safe mode startup, also known as "safe startup", stops sampling of all target databases by the Foxhound Monitor when Foxhound is started, and then temporarily disables most Monitor functionality. This sometimes helps if Foxhound is unresponsive when it starts.

Here's how you can turn safe mode startup on and off to "reset" Foxhound:

  1. Stop Foxhound.

  2. Turn on safe mode startup by creating a text file named startup.txt containing these exact 4 characters
    safe
    and placing that file in the Foxhound installation folder.

    By default, Foxhound is installed in this location on Windows 10:

    C:\ProgramData\RisingRoad\Foxhound5

  3. Start Foxhound. All the Monitor sessions will now be stopped, and most Monitor functionality will be disabled (the History page will still work, however).

    Administrative Tip: Safe mode startup can be used to prevent the Foxhound purge process from immediately deleting data when you start (or upgrade) an old Foxhound database.

    For example, if you start an old Foxhound database that hasn't been running for six months, and that database is set to purge data that's older than 30 days, the very first purge will try to delete all the samples.

    However, safe mode startup disables the purge process before it can start, so you can change the purge settings before you lose any data.

    If you are going to upgrade an old Foxhound 4 database, you can create the C:\ProgramData\RisingRoad\Foxhound5 folder and startup.txt file ahead of time to disable the purge process.

  4. Stop Foxhound.

  5. Turn safe mode startup off by deleting or renaming the startup.txt file described above.

  6. Start Foxhound. All the Monitor sessions will still be stopped, but all Monitor functionality will be available again.


Foxhound 5 » Introduction and Setup 
Starting Multiple Copies of Foxhound

You can use the Extended Edition of Foxhound to start multiple copies of the Foxhound database, each running in a separate SQL Anywhere database server and monitoring a separate set of target databases.

Here's what the End User License Agreement has to say...

In the case of the Extended Edition, an unlimited number of copies of the Foxhound database may be created and started using separate instances of SQL Anywhere on the same local network, with the requirement that a separate Extended Edition registration key be purchased for each multiple of 10 copies of Foxhound started.

In particular,

Here's how to create and customize multiple copies of Foxhound on one computer, using the following names and values:

                  Folders: C:\ProgramData\RisingRoad\Foxhound5\a, \b, \c, ...
SQL Anywhere server names: fh5_a, fh5_b, fh5_c, ...
               HTTP ports: 50201, 50202, 50203, ...
    Windows service names: fh5_a, fh5_b, fh5_c, ...
  1. Create a new folder or subfolder for each new copy of Foxhound.

    MD C:\ProgramData\RisingRoad\Foxhound5\a
    MD C:\ProgramData\RisingRoad\Foxhound5\b
    MD C:\ProgramData\RisingRoad\Foxhound5\c
    

  2. If the master copy of Foxhound is running, stop it so the foxhound5.db file can be copied.

  3. Three files are required for each new copy of Foxhound, all the others are optional:

    CD C:\ProgramData\RisingRoad\Foxhound5\a
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll
    
    CD C:\ProgramData\RisingRoad\Foxhound5\b
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll
    
    CD C:\ProgramData\RisingRoad\Foxhound5\c
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\foxhound5.db
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin32.dll
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\rroad5_sa17_bin64.dll
    

  4. If you didn't stop Foxhound cleanly before copying foxhound5.db, you will have to copy this file as well; otherwise, it will be created when you start foxhound5.db for the first time.

    foxhound5.log
    

    You don't have to run the dblog.exe utility to tell Foxhound where to find foxhound5.log; it knows to look in the current folder.

  5. You may want to copy and customize some additional command files:

    CD C:\ProgramData\RisingRoad\Foxhound5\a
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat
    
    CD C:\ProgramData\RisingRoad\Foxhound5\b
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat
    
    CD C:\ProgramData\RisingRoad\Foxhound5\c
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\scripts\$create_SQL_Anywhere_17_bin64_service.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$adhoc_query_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$admin_update_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$backup_foxhound5.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_default_browser.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$start_foxhound5_engine.bat
    COPY /Y C:\ProgramData\RisingRoad\Foxhound5\$stop_foxhound5_engine.bat
    

  6. Here's how to create a custom Windows service for each new copy of Foxhound;

    ECHO Create SQL Anywhere 17 Bin64 fh5_a Service
    
    ECHO To delete existing fh5_a service...
    PAUSE
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -d fh5_a -y 
    
    REM Tip: To avoid SQLCODE -602, -1066 and other exceptions when using UNC 
    REM and other file specifications in Adhoc and OFSS SQL statements, try 
    REM changing the dbsvc -as option to dbsvc -a and -p to provide the name 
    REM and password for a Microsoft Windows account you have created:
    REM   -a youraccount^
    REM   -p yourpassword^
    
    ECHO To create the fh5_a service...
    PAUSE
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe"^
      -o "C:\ProgramData\RisingRoad\Foxhound5\a\dbsvc_log.txt"^
      -y^
      -as^
      -s Automatic^
      -sn fh5_a^
      -sd "fh5_a Database Monitor Bin64 Service"^
      -t Network^
      -w fh5_a "C:\Program Files\SQL Anywhere 17\Bin64\dbsrv17.exe"^
      -c 25p^
      -ch 50p^
      -cr-^
      -gk all^
      -gl all^
      -gn 220^
      -gna 0^
      -m^
      -n fh5_a^
      -o "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5_debug.txt"^
      -oe "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5_debug_startup.txt"^
      -on 1M^
      -qn^
      -sb 0^
      -ufd restart^
      -x tcpip^
      -xd^
      -xs http(port=50201;maxsize=0;to=600;kto=600)^
      "C:\ProgramData\RisingRoad\Foxhound5\a\foxhound5.db"^
      -n f
    
    ECHO To display the fh5_a service definition...
    PAUSE
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -g fh5_a
    
    ECHO To list all the SQL Anywhere service names...
    PAUSE
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -l
    
    ECHO All done...
    PAUSE
    

  7. Here's how to start, browse and stop the three new copies of Foxhound:

    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_a
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_b 
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -u fh5_c
    
    START http://localhost:50201/foxhound
    START http://localhost:50202/foxhound
    START http://localhost:50203/foxhound
    
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_a
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_b 
    "C:\Program Files\SQL Anywhere 17\Bin64\dbsvc.exe" -x fh5_c
    

  8. Here's how to customize the other command files:

    $adhoc_query_foxhound5.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    -c "ENG=foxhound5;  -c "ENG=fh5_a;
    CON=Foxhound5       CON=fh5_a
    

    $admin_update_foxhound5.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    -c "ENG=foxhound5;  -c "ENG=fh5_a;
    CON=Foxhound5       CON=fh5_a
    

    $backup_foxhound5.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    %SERVER%            fh5_a
    -xs http(port=80;   -xs http(port=50201;
    

    $start_foxhound5_default_browser.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    %SERVER%            fh5_a
    -xs http(port=80;   -xs http(port=50201;
    localhost:80        localhost:50201
    

    $start_foxhound5_engine.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    %SERVER%            fh5_a
    -xs http(port=80;   -xs http(port=50201;
    localhost/          localhost:50201/
    

    $stop_foxhound5_engine.bat

    Change...           To...
    
    ECHO Foxhound5      ECHO fh5_a
    %SERVER%            fh5_a
    


Foxhound 5 » Introduction and Setup 
Offline Foxhound Sampling Service (OFSS)
Introducing OFSS

OFSS Terminology

OFSS Limitations

Step 1: Set up OFSS on your database: OFSS_1_setup.sql

Step 2: Run the OFSS Monitor on your database: CALL start_OFSS_monitor()

Step 3: Run the OFSS Load component on Foxhound: CALL load_OFSS_samples()

How to use OFSS on multiple databases

How to choose an Air Gap technique for OFSS

How to customize OFSS

How to debug OFSS code on your database


Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
Introducing OFSS

The Offline Foxhound Sampling Service (OFSS) provides an alternative method for Foxhound to gather and display performance statistics from a subject database when Foxhound cannot (or must not) establish a direct client server connection to that database.

OFSS is supported for subject databases running on SQL Anywhere 12, 16 and 17.

Figure 1: OFSS Architecture

  1. The Capture component of the OFSS Monitor gathers and inserts one OFSS Sample in your Subject Database every 10 seconds.

  2. The Unload component of the OFSS Monitor periodically writes a batch of OFSS Samples to an external OFSS Batch text file.

  3. Some unspecified method is used to transport the OFSS Batch files across the Air Gap from your Subject Server environment to the Foxhound Server environment.

  4. The OFSS Load component of Foxhound periodically reads one or more OFSS Batch files and stores the data as Foxhound Samples for display on the Sample History page.

Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
OFSS Terminology

The word "Offline" in Offline Foxhound Sampling Service emphasizes that Foxhound receives the samples in files long after they have been recorded by an entirely separate process.

The word "Service" in Offline Foxhound Sampling Service is a general term meaning "supplier" or "provider". In particular, OFSS is not implemented as a Windows or HTTP service.

The phrase "Subject Database" is used in OFSS documentation instead of the Foxhound term "Target Database" to draw attention to the fact that while the database may be the subject of Foxhound performance monitoring it is not the target of any direct connection from Foxhound.

The "OFSS Monitor" is an open source collection of SQL Anywhere tables and stored procedures that you install on your Subject Database via the delivered script OFSS_1_setup.sql and invoke via CALL start_OFSS_monitor().

The word "Batch" refers to a consecutive set of samples gathered, compressed, optionally encrypted and then passed from the OFSS Monitor across the Air Gap to Foxhound as a single text file.

The term "Air Gap" is a network security term for the way your subject database is isolated from the computer running Foxhound. For security reasons this document doesn't suggest (or even discuss) methods for implementing or bridging a real air gap; the examples use a shared folder which effectively means "no air gap".


Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
OFSS Limitations

Some of the limitations of OFSS are a result of the way it works, and others (like AutoDrop) are simply omitted features:


Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
Step 1: Set up OFSS on your database: OFSS_1_setup.sql

1(a) Connect to your database via ISQL

1(b) Run this script: OFSS_1_setup.sql

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_1_setup.sql
SQL - Execute
Owner: OFSS
OK

Administrative Tip: You can choose any user id you want as Owner, including DBA. If you choose a user id that doesn't exist, it will be created for you.

1(c) Observe that the script ran OK

...
OFSS_1_setup.sql done OK.

Administrative Tip: You can re-run the setup script at any time, and when you do, all the OFSS-related objects inside your subject databases are dropped and recreated.

This has the additional effect of telling Foxhound to treat the subsequent OFSS batches as coming from a different subject database.

For more information on how unique Foxhound display names are assigned to OFSS subject databases, see 8. Adhoc Queries » 8.5 OFSS Connection String Names.

Administrative Tip: OFSS doesn't work on read-only subject databases because the OFSS scripts can't modify the database. This limitation affects High Availability secondary (mirror) and read-only scale-out databases in particular.

Administrative Tip: When using OFSS on a High Availability setup, run the OFSS setup and start_OFSS_monitor() on the primary, not the underlying partner. After a failover, you can then rerun the CALL start_OFSS_monitor() on the primary because it will be updatable.

You can also run OFSS on the arbiter server if it has a database.

Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
Step 2: Run the OFSS Monitor on your database: CALL start_OFSS_monitor()

2(a) Connect to your database via ISQL

2(b) Load this script: OFSS_2_run_the_OFSS_monitor.sql

Note: The sample scripts shown here use a shared folder to transport the OFSS batches; they do not show how to implement an "air gap".

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_2_run_the_OFSS_monitor.sql

2(c) Edit the script as desired

For example, change the path specification for the batch files, and the duration of each batch, and then start the OFSS Monitor:

-- Run this code on your OFSS subject database, not the Foxhound database.

PARAMETERS OWNER;

UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSS\\'; -- default is 'C:\\temp\\'
UPDATE "{OWNER}".OFSS_settings SET max_batch_duration = '5m';        -- default is '10m', range is '1m' to '1h'

CALL "{OWNER}".start_OFSS_monitor();

Administrative Tip: If the CALL start_OFSS_monitor() fails with "Cannot access file ... Permission denied SQLCODE=-602", and

Administrative Tip: If you stop and then re-execute the CALL start_OFSS_monitor(), there will be a gap in the batch numbering.

Whether one or two batch numbers are skipped depends on the timing of the interruption.

See also...

How to customize OFSS

2(d) Run the script OFSS_2_run_the_OFSS_monitor.sql

SQL - Execute
Owner: OFSS
OK

Administrative Tip: You can stop and rerun the CALL start_OFSS_monitor() at any time, and when you do, the current batch of samples being gathered will be discarded and a new batch of samples will be started.

This may create a gap in the samples displayed by Foxhound, but it does have the benefit of letting you change the OFSS settings that control how the batch files are created. For more information see How to customize OFSS.

2(e) Observe the OFSS sampling progress on your subject database

Use a separate ISQL session to run this query:

SELECT subject_name,
       batch_number,
       updated_at
  FROM OFSS.OFSS_settings;

subject_name                                                                     batch_number updated_at              
-------------------------------------------------------------------------------- ------------ ----------------------- 
ddd12                                                                                      10 2019-02-27 13:45:38.192 

Administrative Tip: Be sure to use the right Owner name in the query above, in this case "OFSS". It's quite easy to accidentally run Step 1 with different owners, and get multiple different sets of OFSS objects set up; e.g., one set owned by OFSS and another by DBA.

2(f) Observe the new OFSS batch files being created

Administrative Tip: You'll have to wait a while (by default 10 minutes) before a DIR command will show any batch files... and if you run Step 3 right away, the files may be deleted before you get to see them.

 Directory of C:\DATA\OFSS

02/27/2019  01:52 PM    <DIR>          .
02/27/2019  01:52 PM    <DIR>          ..
02/27/2019  01:45 PM           118,024 OFSS-ddd12-batch-----10-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  12:14 PM           121,005 OFSS-ddd12-batch------1-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  12:24 PM           116,979 OFSS-ddd12-batch------2-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  12:34 PM           117,231 OFSS-ddd12-batch------3-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  12:44 PM           117,417 OFSS-ddd12-batch------4-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  12:54 PM           117,661 OFSS-ddd12-batch------5-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  01:05 PM           117,810 OFSS-ddd12-batch------6-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  01:15 PM           117,041 OFSS-ddd12-batch------7-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  01:25 PM           117,614 OFSS-ddd12-batch------8-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
02/27/2019  01:35 PM           117,365 OFSS-ddd12-batch------9-uuid-4f0692fe-5c3c-4e9e-9564-e24e83917d83.txt
              12 File(s)      1,178,163 bytes
               2 Dir(s)  337,448,173,568 bytes free

Administrative Tip: OFSS samples are temporarily stored inside your subject database by the OFSS Monitor until they have been written to the OFSS batch files, at which point they are automatically deleted from your database.

As far as the OFSS batch files are concerned, it is up to your implementation of the air gap to delete those files when they are no longer needed.

However, if the OFSS Monitor and Foxhound Load processes both use the same shared folder to write and read the files, the OFSS Load component on Foxhound will take care of deleting the files; see the next section Step 3: Run the OFSS Load component on Foxhound: CALL load_OFSS_samples().

In particular, if the OFSS Load component on Foxhound is running at the same time as the OFSS Monitor on your source database, and they are both using exactly the same physical folder, the batch files may be deleted almost as fast as they are written and the folder may be empty most of the time.


Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
Step 3: Run the OFSS Load component on Foxhound: CALL load_OFSS_samples()

3(a) Run: Foxhound5 - 1 Start Foxhound
Don't connect to Foxhound to your database; i.e., do not click on Monitor Database.

3(b) Run: Foxhound5 - 2 Adhoc Query Via ISQL

3(c) Load this script: OFSS_3_load_OFSS_batches_into_Foxhound.sql

File - Open... C:\ProgramData\RisingRoad\Foxhound5\scripts\OFSS_3_load_OFSS_batches_into_Foxhound.sql

3(d) Edit the script as desired

For example, change the path specification for the batch files and start the load process:

-- Run this code on the Foxhound database, not your OFSS subject database...
--    use the Foxhound5 shortcut "2 Adhoc Query via ISQL"

CALL load_OFSS_samples ( 'C:\\DATA\\OFSS\\' ); 

Note: The sample scripts shown here use a shared folder to receive the OFSS batches; they do not show how to implement an "air gap".

Administrative Tip: You can use the same folder to receive the batches from multiple different OFSS subject databases.

In that case, you must run only one instance of the script to CALL load_OFSS_samples(), and Foxhound will use OFSS batch file names to determine which database is which.

Administrative Tip: If the CALL load_OFSS_samples() fails with "File system error: ... SQLCODE=-1066", and

Administrative Tip: If you stop and start Foxhound, you will have to re-run the script to CALL load_OFSS_samples().

3(e) Run the script OFSS_3_load_OFSS_batches_into_Foxhound.sql

SQL - Execute

3(f) View the samples in Foxhound

The OFSS subject database will automatically appear on the Foxhound Menu page. You can then open the History page (not the Monitor page), and then click on the Newest button to see new OFSS batches as they are added.

See also...

How to customize OFSS

Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
How to use OFSS on multiple databases

You can use OFSS on multiple subject database and display all the samples in one copy of Foxhound; here's how:

OFSS uses a UUID to identify each subject database, and that UUID appears in the OFSS batch file names so you don't have to worry about overlaps or collisions.


Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
How to choose an air gap technique for OFSS

First, the bad news...

By its very definition a network air gap is a security mechanism you will want to keep secret. This Help document is openly available on the internet so any specific details found here would break that secrecy.

Now, the good news...

OFSS writes data to text files into a folder, then Foxhound reads those files from a folder. How those files get from one computer to another is the "secret network air gap technique"... shhh, don't tell anyone, but a diskette or USB drive may be all you need.

More good news...

If you have more than one OFSS subject database on one computer, you can put all the files in one folder. Their file names are all unique, and Foxhound keeps the data separate.

The same applies to sending OFSS files from several computers to one folder on one Foxhound computer: the file names are globally unique and Foxhound keeps everything straight.

For testing purposes...

A single network shared drive works fine.

Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
How to customize OFSS

OFSS can be customized by using one or more of the following techniques when calling start_OFSS_monitor() and load_OFSS_samples() on Foxhound:

A. Continue processing until a specific time.

B. Specify the database name to be displayed in Foxhound.

C. Specify the maximum batch duration and storage size.

D. Disable capturing of server messages

E. Use a different folder to store the OFSS batch files.

F. Enable encryption by providing a non-empty key.

G. Change multiple settings.

H. Use different folders to use different encryption keys.

[Top]

-- A. Continue processing until a specific time.

-- On your subject database:

PARAMETERS OWNER;
CALL "{OWNER}".start_OFSS_monitor ( @stop_sampling_after = DATEADD ( DAY, 10, CURRENT TIMESTAMP ) );

-- On Foxhound:

CALL load_OFSS_samples ( @stop_loading_after = DATEADD ( DAY, 10, CURRENT TIMESTAMP ) );
Set @stop_sampling_after to the future timestamp when you want start_OFSS_monitor() to stop recording samples from your subject database.

Set @stop_loading_after to the future timestamp when you want load_OFSS_samples() to stop loading samples into the Foxhound database.

The defaults for @stop_sampling_after and @stop_loading_after are both '9999-01-01' which means "run forever" (until you stop the process manually).

Administrative Tip: To ensure all the samples are loaded into Foxhound, use a @stop_loading_after value that is slightly larger than @stop_sampling_after... or just wait a while before calling load_OFSS_samples().

[Top]

-- B. Specify the database name to be displayed in Foxhound. 

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET subject_name = 'Inventory - Cleveland';
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound:

CALL load_OFSS_samples();
Foxhound displays the name of each OFSS subject database in two locations:

By default, the name is set to the first 80 characters of your subject database name; i.e., LEFT ( DB_PROPERTY ( 'Name' ), 80 ).

If you want to change the default, do it before you call start_OFSS_monitor() for the first time.

When you call load_OFSS_samples(), Foxhound appends the suffix "(offline)" to create the display name; e.g., "Inventory - Cleveland (offline)".

Note: It's OK to use the same name for multiple subject databases. For example, three different SQL Anywhere servers could all be running databases with the same name "Inventory". In this case Foxhound will assign different display names as follows:

Inventory (offline)
Inventory(02) (offline)
Inventory(03) (offline)

Foxhound uses a different column to tell different OFSS subject databases apart; i.e., the UUID value in the OFSS_subject_uuid column is used to tell which OFSS batch file belongs to which subject database.

For more information about how Foxhound handles OFSS display names, see OFSS Connection String Names section in the Adhoc Queries topic.

Administrative Tip: If you change OFSS_settings.subject_name on-the-fly and then stop and rerun the CALL start_OFSS_monitor(), the new subject_name will not affect the display name in Foxhound. The new subject_name will be used in the OFSS batch file names, and the Foxhound load_OFSS_samples() procedure will read those files OK, but the samples will be displayed under the old name.

[Top]

-- C. Specify the maximum batch duration and storage size.

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET max_batch_duration = '1h';   -- range 1m to 1h
UPDATE "{OWNER}".OFSS_settings SET max_batch_size     = '512M'; -- range 10k to 512M
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound:

CALL load_OFSS_samples();
The default values are '10m' and '512k' respectively. There are several factors to consider when choosing the maximum size of OFSS batch files.

[Top]

-- D. Disable capturing of server messages (don't call sa_server_messages).

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET server_messages_are_to_be_captured = 'N';
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound:

CALL load_OFSS_samples();
The default for server_messages_are_to_be_captured is 'Y'.
[Top]

-- E. Use a different folder to store the OFSS batch files.

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSS\\'; 
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound: 

CALL load_OFSS_samples ( @load_path = 'C:\\DATA\\OFSS\\' ); 
The default path is 'C:\\temp\\'.

Note: All the examples shown here assume there is no air gap; i.e., start_OFSS_monitor() writes the batch files to exactly the same folder that load_OFSS_samples() reads them from: same folder, same computer.

When a real air gap is implemented,
  • the unload_path used by start_OFSS_monitor() is relative to the computer running your subject database, and

  • the @load_path used by load_OFSS_samples() is relative to the computer running Foxhound.

  • In other words, the relative path specifications may look the same but the actual folders reside on different computers.

Administrative Tip: If the CALL start_OFSS_monitor() fails with "Cannot access file ... Permission denied SQLCODE=-602", and

Administrative Tip: If the CALL load_OFSS_samples() fails with "File system error: ... SQLCODE=-1066", and

[Top]

-- F. Enable encryption by providing a non-empty key.

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e'; -- the values must agree
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound: 

CALL load_OFSS_samples ( @encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' ); -- the values must agree
The default encryption key is '' which means "no encryption".
[Top]

-- G. Change multiple settings.

-- On your subject database:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET unload_path    = 'C:\\DATA\\OFSS\\'; 
UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e';
CALL "{OWNER}".start_OFSS_monitor ( @stop_sampling_after = DATEADD ( HOUR, 1, CURRENT TIMESTAMP ) );

-- On Foxhound: 

CALL load_OFSS_samples ( 
   @load_path          = 'C:\\DATA\\OFSS\\',
   @stop_loading_after = DATEADD ( MINUTE, 70, CURRENT TIMESTAMP ),
   @encryption_key     = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' ); 

[Top]

-- H. Use different folders to use different encryption keys..

-- On subject database 1:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd12\\';               -- folder 1
UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e'; -- encryption key 1
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound Adhoc Query connection 1: 

CALL load_OFSS_samples ( 
   @load_path = 'C:\\DATA\\OFSSddd12\\',                   -- folder 1 
   @encryption_key = 'KeHX8?k1Rqz/5FvOu{nhx1#mQxy0@95e' ); -- encryption key 1

-- On subject database 2:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd16\\';               -- folder 2
UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'gigd}qh]enUU2Y/-j4)Sdj_uP5T{!CpH'; -- encryption key 2
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound Adhoc Query connection 2: 

CALL load_OFSS_samples ( 
   @load_path = 'C:\\DATA\\OFSSddd16\\',                   -- folder 2 
   @encryption_key = 'gigd}qh]enUU2Y/-j4)Sdj_uP5T{!CpH' ); -- encryption key 2

-- On subject database 3:

PARAMETERS OWNER;
UPDATE "{OWNER}".OFSS_settings SET unload_path = 'C:\\DATA\\OFSSddd17\\';               -- folder 3
UPDATE "{OWNER}".OFSS_settings SET encryption_key = 'GPPSsMsPSFGC:MBl[-+D)P+F3rPYVu0V'; -- encryption key 3
CALL "{OWNER}".start_OFSS_monitor();

-- On Foxhound Adhoc Query connection 3: 

CALL load_OFSS_samples ( 
   @load_path = 'C:\\DATA\\OFSSddd17\\',                   -- folder 3 
   @encryption_key = 'GPPSsMsPSFGC:MBl[-+D)P+F3rPYVu0V' ); -- encryption key 3

It's OK to run multiple CALL load_OFSS_samples() statements at one time on multiple connections to Foxhound if each one reads the OFSS batch files from a different folder...

...and that's what you have to do if you want to use different encryption keys for different subject databases.

I.e., if you want to use a different encryption key for each different subject database, you have to put the OFSS batch files in different folders. The underlying technical reason is this: the load_OFSS_samples() procedure reads all the OFSS batch files from one folder, and uses the same encryption key on all of them.

Foxhound 5 » Introduction and Setup » Offline Foxhound Sampling Service (OFSS) 
How to debug OFSS code on your database

If you modify the OFSS setup script OFSS_1_setup.sql you may introduce bugs that raise SQL exceptions, and those exceptions may be recorded in this table:

CREATE TABLE "{OWNER}".OFSS_exception (
   exception_id     BIGINT       NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED,
   inserted_at      TIMESTAMP    NOT NULL DEFAULT CURRENT TIMESTAMP,
   diagnostic_text  LONG VARCHAR NOT NULL ); 

Here's a query that shows the most recent 10 exceptions (assuming that you used specified OFSS as the OWNER when you ran the setup script):

SELECT TOP 10 *
  FROM OFSS.OFSS_exception
 ORDER BY OFSS_exception.inserted_at DESC;

It is possible to run OFSS and a direct connection from Foxhound at the same time. This can be helpful while you're testing changes to the OFSS setup, but the following scenario may also be helpful in production:

  1. Use OFSS to gather high volumes of sample data that is loaded into Foxhound during off hours, and

  2. use a direct Foxhound connection to continuously monitor database availability and server messages but not the connection-level detail.

Here's how to disable connection-level samples for a direct connection from Foxhound (not OFSS):

  • Open the Foxhound Monitor Options page.

  • Check Enable Schedules in section 1. Global Overrides of the Monitor Options page.

  • Select the target database that Foxhound is directly monitoring.

  • Use section 6. Connection Sample Schedule to stop Foxhound from gathering connection-level samples, as follows:

    • Check Enforce the Connection Sample Schedule,

    • click on the Clear button for all the days of the week Mon through Sun, and

    • click Save ( don't forget that! :)...


Foxhound 5 » Introduction and Setup 
Environment Variables
FOXHOUND5 - optional

FOXHOUND5BIN - optional

FOXHOUND5UPGRADE - optional

SQLANY17 - required


Foxhound 5 » Introduction and Setup » Environment Variables 
FOXHOUND5 Optional Environment Variable

By default FOXHOUND5 is created by the Foxhound InstallShield installation to contain the drive and path of the folder where the original copy of Foxhound is installed; e.g., C:\ProgramData\RisingRoad\Foxhound5\

It is no longer required by the delivered Windows shortcuts or by any of the code inside Foxhound. That's because multiple copies of Foxhound in different folders can be created (see Starting Multiple Copies of Foxhound) and they all need to be able to locate their own folders... which they do by using the new local DBFOLDER environment variable.

It is used by one of the optional command files in the scripts subfolder ($run_most_recent_mini_restore.bat), and by the $post_setup.bat file which is launched by the Foxhound installation process.

Normally you do not have to create the FOXHOUND5 environment variable, but if you do, here's how:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5
                Variable value:  C:\ProgramData\RisingRoad\Foxhound5\

Foxhound 5 » Introduction and Setup » Environment Variables 
FOXHOUND5BIN Optional Environment Variable

FOXHOUND5BIN is an optional environment variable. It can be used to change the default SQL Anywhere installation subfolder used by Foxhound from Bin32 to Bin64 or vice versa.

By default Foxhound will use Bin32 subfolder unless Bin64 exists in which case Foxhound will use Bin64. To override this rule create FOXHOUND5BIN as follows:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5BIN
                Variable value:  Bin32
                      -- or --   Bin64

After setting FOXHOUND5BIN you must stop and restart the Foxhound database to put the change into effect.

For example, if you have installed both 32-bit and 64-bit versions of SQL Anywhere, you can force the Foxhound 5 shortcuts to use the 32-bit version by setting FOXHOUND5BIN to Bin32.

Note: This discussion of the FOXHOUND5BIN environment variable applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.

Tip: One of the reasons to run Foxhound on the 32-bit version of SQL Anywhere is to make it easier to use the 32-bit version of ODBC to connect to your target database... but that may not be necessary!

For example, if you have an Adaptive Server Anywhere 9.0 target database you may be able to connect to it using a more recent SQL Anywhere ODBC driver (DRIVER=SQL Anywhere 10 through 16).

It's easiest to do this with Foxhound Menu - String tab rather than the DSN tab... you don't have to mess around with the ODBC Administrator.

The following connection string worked for Foxhound connecting to an Adaptive Server Anywhere 9.0.2.3951 database:

Name:   ddd9
String: ENG=ddd9; DBN=ddd9; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

This also works for SQL Anywhere 6.0.3 database running on a 6.0.4 (3594) server:

Name:   ddd6
String: ENG=ddd6; DBN=ddd6; UID=dba; PWD=sql; DRIVER=SQL Anywhere 16; 

However, neither DRIVER=SQL Anywhere 17 nor DRIVER=SQL Anywhere Native appear to work properly, when trying to connect to an Adaptive Server Anywhere 9.0 or earlier target; you may see this error message: The remote table 'pxxx..SYS.DUMMY' could not be found.


Foxhound 5 » Introduction and Setup » Environment Variables 
FOXHOUND5UPGRADE Optional Environment Variable

FOXHOUND5UPGRADE is an optional environment variable that is very rarely used. It can be used to change the factory setting default behavior of the post-setup data upgrade process when reinstalling Foxhound.

The factory default setting is ALL which means "upgrade all the data."

When you run the data upgrade process you will be asked to change or confirm the setting:

Post-Setup Process for Foxhound Version 5.0
*** Checking for a post-setup path parameter...
*** A post-setup path parameter was provided...
C:\ProgramData\RisingRoad\Foxhound5\
*******************************************************************
***                   Foxhound 5.0.xxxx
***
*** Here's where Foxhound is being installed:
*** C:\ProgramData\RisingRoad\Foxhound5\
***
*** Starting the Foxhound 5.0.xxxx post-setup process...
*** Creating foxhound5.db.4.0.xxxx.ORIGINAL_COPY...
*** Checking for an existing Foxhound5 installation...
***  ...yes, there is an existing Foxhound5 installation.
*** Checking if the existing data should be upgraded...
***  ...yes, the existing Foxhound5 data should be upgraded.
******************************************************************
*** PLEASE READ THIS, AND CONFIRM OR CHANGE **********************
******************************************************************
***
*** "FOXHOUND5UPGRADE" specifies how much data is to be upgraded.
***
*** If you want to CHANGE the setting, type in a new value...
***    ALL       - upgrade all the data
***    OPTIONS   - no samples, just the Foxhound options
***    yyyymmdd  - options plus samples since yyyymmdd
***    nnn       - options plus last nnn days of samples
***    NOTHING   - don't upgrade any data
*** and press Enter to continue.
***
*** If you LIKE the current setting...
***    FOXHOUND5UPGRADE=ALL which means upgrade all the data
*** just press Enter.
******************************************************************
Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=

You can change the default setting ahead of time by creating the FOXHOUND5UPGRADE environment variable:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   FOXHOUND5UPGRADE
                Variable value:  ALL
                      -- or --   OPTIONS
                      -- or --   yyyymmdd
                      -- or --   nnn
                      -- or --   NOTHING

If you specify an invalid value, Foxhound uses OPTIONS.
Foxhound 5 » Introduction and Setup » Environment Variables 
SQLANY17 Required Environment Variable

The SQLANY17 environment variables must have a valid value for the Foxhound 5 shortcuts to work properly.

By default SQLANY17 is set by the SQL Anywhere 17 installation process to contain the drive and path of the folder where SQL Anywhere 17 is installed.

Normally you do not have to create the SQLANY17 environment variable, but if you do, here's how:

Start 
  - Control Panel 
    - System 
      - Advanced system settings 
        - Environment Variables... 
          - System Variables
            - Edit...
                Variable name:   SQLANY17 
                Variable value:  C:\Program Files\SQL Anywhere 17

Note: This discussion of the SQLANY17 environment variable applies to the version of SQL Anywhere being used to run Foxhound itself, and has nothing to do with the version of SQL Anywhere being used to start your target databases.


Foxhound 5 » Introduction and Setup 
Scripts Subfolder

Several optional and supporting SQL scripts and Windows command files are stored in a separate subfolder:

C:\ProgramData\RisingRoad\Foxhound5\scripts

Here's a cross-reference list of the scripts:

File Name
Description See Also
$backup_foxhound5_log.bat
Backup Foxhound Transaction Log Transaction Log Backup
$create_SQL_Anywhere_17_bin64_service.bat
Create SQL Anywhere 17 Bin64
Foxhound Service
Running Foxhound as a Service
$run_most_recent_mini_restore.bat
Automatically Restore Most Recent
Mini-Backup
Mini-Restore
OFSS_1_setup.sql
The Required OFSS Setup How to set up OFSS on your database
OFSS_2_sample_loop_to_capture_and_unload_batches.sql
Run the OFSS Capture and Unload
components
How to run the OFSS Capture and
Unload components on your database
OFSS_3_sample_loop_to_load_batches_into_Foxhound.sql
Run the OFSS Load component How to run the OFSS Load component
on Foxhound


Foxhound 5 » Introduction and Setup 
End User License Agreement

RisingRoad - 3QC Inc. License Terms

Foxhound Version 5

By using the software, you accept the following terms. IF YOU DO NOT ACCEPT THEM, DO NOT USE THE SOFTWARE.

1. No-Nonsense License Agreement

The Foxhound software is protected by United States and Canadian copyright law and international copyright treaty provisions. Therefore, you must treat Foxhound just like a book, except that you may copy it onto a computer to be used and you may make archival copies of Foxhound for the sole purpose of backing-up our software and your data and protecting your investment from loss.

By saying "just like a book," RisingRoad means, for example, that one copy of Foxhound may be executed on any number of computers, and may be freely moved from one computer to another, so long as there is no possibility of it being executed on one computer while it's being executed on another.

By saying "one copy of Foxhound", RisingRoad means a copy of the Foxhound software that has been activated by the application of one single unique registration key obtained from RisingRoad.

Just like one copy of a book can't be read by two different people in two different places at the same time, neither can one copy of Foxhound be executed on two different computers at the same time. (Unless, of course, this License Agreement has been violated.)

2. Use on a Network and the Internet

In the case of the Rental and Basic Editions, one copy of Foxhound may be executed on a computer attached to a local area network and/or the internet, with multiple users accessing the single Foxhound database from browsers running on different computers. The "just like a book" analogy begins to weaken at this point, but it still applies if you think of more than one person reading the same book over someone else's shoulder... it's still one copy of the book, and one copy of Foxhound executing.

In the case of the Extended Edition, an unlimited number of copies of the Foxhound database may be created and started using separate instances of SQL Anywhere on the same local network, with the requirement that a separate Extended Edition registration key be purchased for each multiple of 10 copies of Foxhound started.

3. Further Explanation of Copyright Law and the Scope of This License Statement

You may not download or transmit your copy of Foxhound electronically (either by direct connection or telecommunication transmission) for the purpose of executing it on multiple computers at the same time.

You may transfer all of your rights to use your copy of Foxhound to another person, provided that you transfer to that person (or destroy) all of the software and documentation provided in this package, together with all copies, tangible or intangible, including copies in RAM or installed on a disk, as well as all back-up copies. Remember, once you transfer your copy of Foxhound, it may only be executed on the single computer to which it is transferred and, of course, only in accordance with copyright law and international treaty provisions.

Except as stated in this paragraph, you may not otherwise transfer, rent, lease, sub-license, time-share, or lend the Foxhound software or documentation. Your use of Foxhound is limited to acts that are essential steps in the use of Foxhound on your computer as described in the documentation. You may not otherwise modify, alter, adapt, merge, decompile or reverse-engineer Foxhound, and you may not remove or obscure RisingRoad copyright notices.

6. Disclaimer of Warranty

The software is licensed "as is". You bear the risk of using it. RisingRoad gives no express warranties, guarantees or conditions. RisingRoad excludes the implied warranties of merchantability, fitness for a particular purpose and non-infringement.


Foxhound 5 » Introduction and Setup 
Support and Consulting

Foxhound support is free: Send all your questions about how to set up and use Foxhound to Breck.Carter@gmail.com.

If you have specific questions about how to improve the performance of your database, the first hour of SQL Anywhere performance and tuning consulting is also free, via email.

Here's how it works:

  1. You run the Foxhound 5 Database Monitor to gather samples from your SQL Anywhere database,

  2. then you send me a copy (or a subset) of your Foxhound database

  3. which I look at for one hour

  4. and then I send you my observations and suggestions.

Yes, the first hour of consulting is free... and you can send me your entire foxhound5.db file if it fits in an email or dropbox.com.

But . . . if your Foxhound database is huge . . .

If your foxhound5.db file is too big to send by email or dropbox, you can unload and send a smaller subset like this:

  1. Run Foxhound5 - 2 Adhoc Query via ISQL

  2. Call unload_samples procedure to gather a subset.

    For example, the following statements unload all the samples recorded since May 1 and write the files into C:\temp:

    CALL unload_samples ( '2019-05-01' ); -- see below for more examples
    

  3. Attach the files from C:\temp to a covering email and send it to me: Breck.Carter@gmail.com

That's it! ...you'll hear back from me soon.


Foxhound 5 » Introduction and Setup 
How to CALL unload_samples()

-- unload_samples - Unloads some or all of the rows from the Foxhound 4 sample-related views.
--
-- Syntax
--
-- CALL unload_samples ( from_timestamp,  
--                       to_timestamp, 
--                       sampling_id, 
--                       folder_path );
--
-- from_timestamp The TIMESTAMP value for the earliest rows to be selected from the views
--                that contain timestamp columns; e.g., alert but not alerts_criteria. 
--                A NULL or omitted value means that no earliest limit is placed on these rows.
--
-- to_timestamp   The TIMESTAMP value for the latest rows to be selected from the views 
--                that contain timestamp columns; e.g., alert but not alerts_criteria. 
--                A NULL or omitted value means that no latest limit is placed on these rows.
--
-- sampling_id    The UNSIGNED INT value for the one sampling_id value to be selected 
--                from views that are specific to individual target databases; e.g., 
--                alerts_criteria but not exception_diagnostic. 
--                A NULL or omitted value means all target databases are included.
--
-- folder_path    The string specifying the path where the output text files are written. 
--                A NULL or omitted value is interpreted as C:\temp.
-- 
-- Examples
--
-- 1. Unload all the samples recorded since May 1 2019 and write the files to C:\temp.
--    CALL unload_samples ( '2019-05-01' );
--
-- 2. Unload the last 7 day's samples to C:\temp.
--    CALL unload_samples ( CURRENT TIMESTAMP - 7 );
--
-- 3. Unload all samples for target database 2 to C:\temp.
--    CALL unload_samples ( @sampling_id = 2 );
--
-- 4. Unload samples for the first week of April 2019 for target database 5 to C:\data\db5.
--    CALL unload_samples ( '2019-04-01', '2019-04-07', 5, 'C:\\data\\db5' );
--
-- 5. Same as Example 5, using named arguments in a different order.
--    CALL unload_samples ( @input_folder_path = 'C:\\data\\db5',
--                          @sampling_id = 5, 
--                          @from_timestamp = '2019-04-01',  
--                          @to_timestamp = '2019-04-07' );
--
-- Remarks
--
-- Here are the views that are unloaded:
--
--  1. alert                  One row per alert.
--  2. alert_cancelled        One row per alert cancellation.
--  3. alerts_criteria        One row for the Monitor Options page settings for each sampling session.
--  4. all_clear              One row per alert all-clear.
--  5. autodropped_connection One row per connection that was dropped by the AutoDrop facility.
--  6. exception_diagnostic   One row for each time Foxhound detected an error or other important event.
--  7. peaks                  One row for each target database, holding various peak values.
--  8. ping_log               One row for each time the custom ping process was run.
--  9. purge_run              One row for each run of the database purge process.
-- 10. sample_connection      One row for each connection for each sample holding connection-level properties.
-- 11. sample_detail          One row for each sample.
-- 12. sample_header          One row for each sample holding server and database-level properties.
-- 13. sampling_options       One row for each target database.
-- 14. schedule               One row for each week-long schedule defined on the Monitor Options page.
-- 15. schedule_day_entry     One row for each day in one schedule.
-- 16. schedule_period_entry  One row for each 15-minute period in one day in one schedule.
-- 17. server_message         One row for sa_server_message() row that was captured.


Foxhound 5 » Introduction and Setup