SQL Anywhere

OEM Authentication

noreply@blogger.com (Breck Carter) — Wed, 16 Jun 2021 19:22:00 +0000

Catch 22

OEM authentication is explained in the SQL Anywhere Help, but that explanation can only be understood by someone who already understands OEM authentication.

John Yossarian's Explanation

This blog post came in the form of an email from a client who offered to help me set Foxhound up to work for clients who use the OEM Edition of SQL Anywhere.

This explanation from John Yossarian (not his real name) was so clear, concise and helpful that I asked for (and received) permission to publish it here.

Breck

from:    John Yossarian <jy@stark.co.uk>
to:      Breck Carter <breck.carter@gmail.com>
date:    Jun 12, 2021, 4:28 PM
subject: Authenticated OEM SQL Anywhere Connections

The three parts of the authentication business are 
  a) licencing the server engine 
  b) authenticating the database and 
  c) authenticating the connection.
 
When you go into the authentication business SAP give you three keys - the keys are all different but match 
each other and the publishing company & application names (ie they won't work with other keys):

- an install key: you use this with dblic.exe -k to create OEM lic files (just like a normal install key) - but 
  we can then redistribute those engines. (this bit isn't your problem of course)

- a database key: this is used once on a database to make it an Authenticated database which will work on 
  the server created with the install key - the setting is persistent. This is done with a SET OPTION command; e.g.

  SET OPTION PUBLIC.DATABASE_AUTHENTICATION='Company=Stark;Application=Winterfell;Signature=XXXXXXXXXX-XXXXXXXXXX'; 

  This step needs SET ANY SECURITY OPTION rights.

- a connection key: This is used by connections to the database. Each connection has a 30 second (or so) grace 
  period to set a temporary Connection_authentication option; e.g.

  SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='Company=Stark;Application=Winterfell;Signature=YYYYYYYYYY-YYYYYYYYYY'

- a non-authenticated connection to an authenticated database running on an OEM engine is allowed to connect 
  but after thirty seconds is strictly read-only.
 
So, if you wanted to support authenticated servers fully, you would need users to supply both the database key 
and the connection key, in the set-up process & then use them as above.

Embedding Highcharts In SQL Anywhere

noreply@blogger.com (Breck Carter) — Mon, 08 Feb 2021 18:39:00 +0000

Introduction
Hello, Chart! Is Embedded In SQL Anywhere
How Hello, Chart! Works
See Also...

Introduction

Highcharts is a bare-metal browser-based JavaScript API for adding interactive charts to HTML pages.

Highcharts is powerful, well documented and (relatively) easy to use, plus it's been around a long time (2009), it's popular and it's commercially successful.

Here's the Highcharts version of Hello, World!:

Demo, Chart!

[ Click the buttons to see data, then move the mouse to see tooltips ]
( if the buttons don't work, try the Static, Chart! demo instead )

Demo, Chart! is one of three demonstrations, all of which look the same but work differently:

Demo, Chart! Static, Chart! Hello, Chart!

Where does it run? In this blog post In a website (no CORS) In a SQL Anywhere 17 database

HTML location: blogspot.com bcarter.com SQL Anywhere 17 database

Highcharts JavaScript: code.highcharts.com code.highcharts.com SQL Anywhere 17 database

Data location: CSV files in static website bcarter.com CSV files in static website bcarter.com SQL Anywhere 17 database

Data retrieval: Static file via XMLHttpRequest() Static file via XMLHttpRequest() Web service via XMLHttpRequest()

CORS headers: github.com/Rob--W/cors-anywhere Not required Not required

The code for Demo, Chart! is under your fingertips: View page source, then Find 

The code for Static, Chart! is here:

https://www.bcarter.com/blog/Highcharts/static.html
https://www.bcarter.com/blog/Highcharts/demochart1.csv
https://www.bcarter.com/blog/Highcharts/demochart2.csv

The third demo is shown below.

Hello, Chart! Is Embedded In SQL Anywhere

There are advantages to storing code together with the data in a relational database. For example, application deployment, backup, recovery, portability, security all involve a single *.db file, and the Rules For Relational Databases guarantee consistency among various components.

If you have SQL Anywhere 17 installed, embedding Hello, Chart! is a two-step "Download And Go" process:

Step 1: Download four files into a local folder.

This command file uses the wget utility for Windows to download the files into C:\temp:

CD C:\temp
"C:\download\wget\wget.exe" "https://code.highcharts.com/highcharts.js"
"C:\download\wget\wget.exe" "https://code.highcharts.com/modules/data.js"
"C:\download\wget\wget.exe" "https://www.bcarter.com/blog/highcharts/setup_hello.html"
"C:\download\wget\wget.exe" "https://www.bcarter.com/blog/highcharts/setup_hello.sql.txt"
PAUSE

Step 2: Load the code and launch it in a browser.

The following command file

creates a new SQL Anywhere 17 database chart.db via dbinit.exe,

starts the database via dbsrv17.exe

with the builtin HTTP server running on port 54321,

loads the code and data by running setup_hello.sql.txt via dbisql.com

and then uses the Windows START command to launch the hello.html web service in a browser window:

"%SQLANY17%\bin64\dbinit.exe"^
  -dba dba,sql^
  -mpl 3^
  "C:\temp\chart.db"

"%SQLANY17%\bin64\dbspawn.exe"^
  -f "%SQLANY17%\bin64\dbsrv17.exe"^
  -xs http(port=54321)^
  "C:\temp\chart.db"

"%SQLANY16%\bin64\dbisql.com"^
  -c "ENG=chart; DBN=chart; UID=dba; PWD=sql;"^
  READ ENCODING Cp1252 "C:\temp\setup_hello.sql.txt"

START http://localhost:54321/hello.html

PAUSE

Here's what it looks like in Edge:

How Hello, Chart! Works

Here's how the code works, starting at the point the web browser launches http://localhost:54321/hello.html.

Step 1: The browser passes the request for "hello.html" to the HTTP server embedded inside the SQL Anywhere 17 process dbsrv17.exe, which is listening for HTTP traffic on port 54321 because of the option -xs http(port=54321).

It could use port 80, but this is a demo, and demos are supposed to work, not generate conflicts with existing software :)

Step 2: SQL Anywhere passes the request to the web service named "hello.html" that is defined in this snippet.

CREATE SERVICE "hello.html" TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_static_text_file ( 'hello.html' );

Step 3: The web service passes the request on to the procedure shown in this snippet.

The RESULT clause on line 61 specifies that the procedure will return a result set to the caller (the browser) consisting of a single LONG VARCHAR column.
The CALL on line 75 sets the HTTP Content-Type header to 'text/html' because the input file extension is 'html'.
The SELECT on line 91 gets the text file data from a table rather than an actual file; this is the SELECT that defines the RESULT set returned by this procedure.

CREATE PROCEDURE get_static_text_file ( IN @file_name VARCHAR ( 255 ) )
   RESULT ( text_string LONG VARCHAR )
BEGIN

DECLARE @extension_pos INTEGER;
DECLARE @extension     VARCHAR ( 100 );

SET @extension_pos = LOCATE ( @file_name, '.', -1 );

SET @extension 
   = IF @extension_pos = 0
        THEN ''
        ELSE SUBSTR ( @file_name, @extension_pos + 1 )
     ENDIF;

CALL dbo.sa_set_http_header ( 
   'Content-Type', 
   CASE @extension
      WHEN 'css'  THEN 'text/css' 
      WHEN 'csv'  THEN 'text/csv' 
      WHEN 'htm'  THEN 'text/html'
      WHEN 'html' THEN 'text/html'
      WHEN 'js'   THEN 'application/javascript' 
      WHEN 'json' THEN 'application/json' 
      WHEN 'xml'  THEN 'application/xml' 
      ELSE             'text/plain'
   END CASE ); 

IF EXISTS ( SELECT *
              FROM static_text_file
             WHERE static_text_file.file_name = @file_name ) THEN
   SELECT static_text_file.file_text
     FROM static_text_file
    WHERE static_text_file.file_name = @file_name;
ELSE
   SELECT STRING ( '[', @file_name, ' not found]' );
END IF;

END;

Step 4: At this point in "how the code works", the browser receives the text for hello.html and starts processing by loading two Highcharts Javascript text files from SQL Anywhere...

<script src="get_static_text_file?f=highcharts.js"></script>
<script src="get_static_text_file?f=data.js"></script>

...using another web service that calls the same stored procedure shown earlier:

CREATE SERVICE get_static_text_file TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_static_text_file ( :f );

Here's where all the "static text file" data came from: it was pre-loaded via xp_read_file() calls from actual files into the static_text_file table.

CREATE TABLE static_text_file (
   file_name  VARCHAR ( 255 ) NOT NULL,
   file_text  LONG VARCHAR NOT NULL,
   PRIMARY KEY ( file_name ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'highcharts.js', 
     dbo.xp_read_file ( 'C:\\temp\\highcharts.js' ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'data.js',       
     dbo.xp_read_file ( 'C:\\temp\\data.js' ) );

INSERT static_text_file ON EXISTING UPDATE VALUES 
   ( 'hello.html',    
     dbo.xp_read_file ( 'C:\\temp\\setup_hello.html' ) );
COMMIT;

Step 5: When the browser has finished loading hello.html, it displays this...

...by executing this snippet of HTML:

<b>Hello, Chart!</b>
<p>
<button onClick="showDataSet ( 1 );">Show Data Set 1</button>
<button onClick="showDataSet ( 2 );">Show Data Set 2</button>
<div id="chartDiv" style="margin-top: 0.5em; padding: 0.5em; border-style: solid; border-width: 1px;">
[ <b>Click the buttons</b> to see data, then <b>move the mouse</b> to see tooltips ]
<p style="padding-left: 2em;">
<i>( this is the SQL Anywhere database version of 
<a href="http://sqlanywhere.blogspot.com/2021/02/embedding-highcharts-in-sql-anywhere.html" 
   target="_blank"><b><u>the code in this blog post</u></b></a> )</i>
</div>

Step 6: When one of the Show Data Set buttons is pressed, the browser executes the following function where

the requestUrl string points to a SQL Anywhere web service,
the chartXHR.open() sets up a XMLHttpRequest object to perform an asynchronous GET request for the web service and
the chartXHR.send() sends the request.

function showDataSet ( dataSet ) { 
   const requestUrl 
      = "get_csv_chart_data?data_set=" + dataSet; // SQL Anywhere service
   chartXHR.open ( "GET", requestUrl, true ); 
   chartXHR.send(); // this will eventually trigger chartXHR.onload
};

Step 7: The web service get_csv_chart_data builds a CSV result set...

Chart Timestamp,Chart Percent
2020-12-01 13:58:54,.1 
2020-12-01 13:59:04,.1 ...

...by executing this code:

CREATE SERVICE get_csv_chart_data TYPE 'RAW' AUTHORIZATION OFF USER DBA
   AS CALL get_csv_chart_data ( :data_set );

CREATE PROCEDURE get_csv_chart_data ( IN @data_set INTEGER )
   RESULT ( csv_string LONG VARCHAR )
BEGIN

CALL dbo.sa_set_http_header ( 'Content-Type', 'text/csv' );

SELECT STRING ( -- convert rows for one data set into CSV data
          'Chart Timestamp,Chart Percent', 
          '\x0d\x0a',
          LIST ( 
             STRING ( DATEFORMAT ( chart_data.chart_timestamp, 'YYYY-MM-DD HH:MM:SS' ),
                      ',',
                      chart_data.chart_percent ),
             '\x0d\x0a'
             ORDER BY chart_data.chart_timestamp ) )
  FROM chart_data
 WHERE chart_data.data_set = COALESCE ( @data_set, 1 );

END;

Here's where the CSV data originally came from; it was pre-loaded into SQL Anywhere using this snippet.

CREATE TABLE chart_data (
   data_set        INTEGER NOT NULL,
   chart_timestamp TIMESTAMP NOT NULL,
   chart_percent   DECIMAL ( 11, 1 ) NOT NULL,
   PRIMARY KEY ( chart_timestamp ) );

INSERT chart_data VALUES ( 1, '2020-12-01 13:58:54', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:04', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:14', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:24', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:34', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:44', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 13:59:54', .1 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:04', 1.2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:18', 10.4 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:28', 23.9 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:38', 29.2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:43', 14.3 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:00:53', .2 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:01:03', .7 );
INSERT chart_data VALUES ( 1, '2020-12-01 14:01:13', 1.2 );

INSERT chart_data VALUES ( 2, '2020-12-01 14:01:28', 16.1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:38', 24.1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:47', 26.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:01:53', 7.4 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:02', .9 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:12', .9 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:23', 1.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:32', .1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:46', 28.3 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:02:54', 39.2 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:02', .1 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:12', 1.0 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:22', .4 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:32', 1.7 );
INSERT chart_data VALUES ( 2, '2020-12-01 14:03:45', 18.0 );

COMMIT;

Step 8: When the get_csv_chart_data web service returns the CSV result set, it is received by the XMLHttpRequest.onload event handler.

const chartXHR = new XMLHttpRequest();
chartXHR.onload = function() { // asynchronous callback event handler for chartXHR.send()
   if ( chartXHR.status === 200 ) { // HTTP status OK
      Highcharts.chart ( "chartDiv", { // tell Highcharts where to put the chart
         chart: {
            type: "line"
         },
         data: {
            csv: chartXHR.responseText // tell Highcharts where to get the data
         },
         title: {
            text: "Chart"
         },
         xAxis: {
            title: {
               text: "Chart Timestamp"
            }
         },
         yAxis: {
            title: {
               text: "Chart Percent"
            }
         },
      } );
   } else {
      document.getElementById ( "chartDiv" ).innerHTML = "HTTP Status " + chartXHR.status;
      console.log ( "ERROR: chartXHR.status = ", chartXHR.status );
   }
   return false;
};

Comparing Execution Plans

noreply@blogger.com (Breck Carter) — Wed, 27 Jan 2021 19:42:00 +0000

Q: Why is my SELECT so slow?

Answer: Graphical Plan!

Q: Why is my SELECT sometimes slow and the rest of the time it's fast?

Answer: Compare Plans!

The new Compare Plans tool in SQL Anywhere 17 lets you compare two graphical plans to see all the differences side-by-side... not just structural differences between diagrams, but individual differences between performance statistics like the number of disk reads for a single table.

Tip: You don't have to understand every detail of the graphical plans if you can find inspiration in the differences.

Here's a demonstration using the Tutorial: Comparing Plans in Interactive SQL as a starting point:

1. Start the SQL Anywhere 17 demo database.

Tip: You can compare SQL Anywhere 16 plans but you have to launch the Compare Plans tool via ISQL 17.

2. Flush the cache in ISQL.

CALL sa_flush_cache();

3. Copy and paste this SELECT into ISQL but do not execute it (so the cache will still be empty when the next step captures the plan).

SELECT DISTINCT EmployeeID, GivenName, Surname
  FROM GROUPO.SalesOrders WITH (INDEX (SalesOrdersKey)), GROUPO.Employees, GROUPO.SalesOrderItems 
  WHERE SalesRepresentative = EmployeeID and
   SalesOrders.ID = SalesOrderItems.ID and
   SalesOrderItems.ProductID = (SELECT ProductID 
    FROM GROUPO.Products, GROUPO.MarketingInformation
    WHERE Name = 'Tee Shirt' AND
    Color = 'White' AND
    Size = 'Small' AND
    MarketingInformation.Description LIKE '%made of recycled water bottles%');

4. Open the Tools - Plan Viewer and capture the plan as FirstPlan17.saplan.

ISQL - Tools - Plan Viewer

Pick Statistics level:  Detailed and node statistics
          Cursor type:  Asensitive
        Update status:  Read-only

Click on Get Plan
   Wait for the plan to appear

Click Save As...
   Specify FirstPlan17.saplan

Click Save

5. Switch back to ISQL, then repeat Step 4 to save SecondPlan17.saplan.

6. Switch back to ISQL and open Tools - Compare Plans.

ISQL - Tools - Compare Plans

Click on Plan 1: - Browse...
   Pick FirstPlan17.saplan
   Click Open

Click on Plan 2: - Browse...
   Pick SecondPlan17.saplan
   Click Open

Click Compare Plans

Here are the side-by-side plans; the diagrams look the same but the statistics are different:

the red numbers show zero disk reads for the second execution because all the rows were in the cache, and
the yellow numbers are disk statistics that were only measured for the first plan:

Tip: You can compare plans for two different SELECT statements, but make sure you're not doing that by accident. The tool doesn't show differences between SELECT statements, just the resulting plans.

Here's a map for describing the Compare Plans display:

Plan 1: [filespec1]    [Browse...]        Plan 2: [filespec2]    [Browse...]     
                                                                                
                           [<-]  [Compare Plans]  [->]                         
--------------------------------------------------------------------------------
Comparison Overview                                                            
                                   Name   Name                                  
                              id1 node1 - id1 node1                               
                              id2 node2 - id2 node2           
--------------------------------------------------------------------------------
1. filename1                            | 2. filename2                          
Subquery: [SubQ dropdown]               | Subquery: [SubQ dropdown]                 
--------------------------------------------------------------------------------
                                        |                                       
                                        |                                       
               Diagram                  |               Diagram                
                                        |                                       
                                        |                                       
--------------------------------------------------------------------------------
[Details]  [Advanced Details]           | [Details]  [Advanced Details]         
          ------------------------------|           ---------------------------
                                        |                                       
                                        |                                       
              Statistics                |              Statistics             
                                        |                                       
                                        |                                       
--------------------------------------------------------------------------------
[Match Operators][Unmatch Operators] [Match Queries][Unmatch Queries]

Plan 1: [filespec1] is for picking the plan to show on the left side, Plan 2: [filespec2] is for the right-hand plan, and [Compare Plans] does the work.

Tip: When you restart the Compare Plans tool after using it before, [filespec1] and [filespec2] will be the same. Be careful what you pick, the tool doesn't care if you compare the same plan with itself.

Tip: When you change [filespec1] or [filespec2], even if you do it via the [Browse...] buttons, don't forget to click [Compare Plans] again.

In other words, don't rely on [filespec1] and [filespec2] for confirmation of exactly what you are looking at. If you forgot to click [Compare Plans], those aren't the plans being displayed.

The [<-] and [->] buttons scroll up and down through Comparison Overview section, highlighting different Name rows and corresponding boxes in the Diagram sections.

Each Name in the Comparison Overview section consists of a generated identifier 1, 2, 3 and a non-unique node title (SELECT, Work, DistH, etc.).

Tip: The Comparison Overview doesn't show the whole query, just the part you selected in the Subquery: [dropdown].

Big complicated SELECT statements can have many entries in the Subquery list. Some of those subqueries can be far more expensive than others, and they can be way down deep in the list.

The two Subquery: [dropdown] lists are synchronized; change one and the other changes, and so do both the Diagram sections.

Each box in the Diagram sections shows one table or other component in the SQL Anywhere query execution plan; for the full list see Execution Plan Components.

Tip: Table names inside rectangles mean table scans, trapezoids mean index scans. Table scans aren't always bad; if SQL Anywhere picks a table scan when an index exists, it's usually because the table scan is faster.

Tip: If a Diagram section shows up empty, click on the second node and then back on the top node.

The Statistics sections, with their Details and Advanced Details tabs, are where all the interesting comparisons are found.

Here's how to find them:

Step through all the diagrams in the Subquery: [dropdown] list.
Concentrate on the Actual numbers, not the Estimates.
Concentrate on the Subtree Statistics sections, not the Node Statistics.
Start by looking for large RunTime numbers, not PecentTotalCost.

One Last Thing...

If you don't like surprises, don't click on the [Match] and [Unmatch] buttons at the bottom of the screen.

If you want to learn, see Lesson 3: Manually Match and Unmatch Operators and Queries.
If you don't care, there's still a reason to click on these buttons, it's to answer this question...

Q: How do I see the original SELECT statements for the two plans?

Answer...

Click on Subquery: Main Query.

Click on the topmost SELECT box in the diagrams.

Click on [Unmatch Operators].

Voila! The original SELECT statements now appear in the Details tabs.

Tip: To reset everything, click on [Compare Plans} again... that gets rid of the effects of clicking on [Match] and [Unmatch].

A Simple JSON Example

noreply@blogger.com (Breck Carter) — Thu, 10 Dec 2020 14:40:00 +0000

Question: How do I dump one row to a json file?

Answer: Here's an adhoc query that writes all the Foxhound 5 Monitor Options values for one target database to a json text file:

-- Start - Foxhound5 - 2 Adhoc Query via ISQL
UNLOAD
SELECT REPLACE ( json_raw_table.json_raw_string, ',"', ',\X0D\X0A"' ) AS monitor_options_json
  FROM ( SELECT *
           FROM alerts_criteria
          WHERE alerts_criteria.criteria_set_type = 'Target Specific'
            AND alerts_criteria.sampling_id       = 13 -- Inventory17
            FOR JSON RAW
       ) AS json_raw_table ( json_raw_string ) 
TO 'C:\\temp\\monitor_options.json'
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF;

[{"criteria_set_type":"Target Specific",
"sampling_id":13,
"send_alert_emails":"N",
"mail_recipient":"",
"use_html_in_emails":"Y",
"attach_files_to_emails":"Y",
"email_url_host_port":"localhost",
"display_alerts":"Y",
"autodrop_mail_recipients":"",
"autodrop_mail_recipients_same_as_for_alerts":"Y",
"previous_sample_set_number_processed":3935794,
"previous_gathered_sample_finished_at":"2020-12-10 08:28:08.609",
"current_sample_finished_at":"2020-12-10 08:28:08.609",
"alert1_enabled":"Y",
"alert1_is_in_effect":"N",
"alert1_in_effect_at":"2020-12-07 11:13:00.567",
"alert1_all_clear_at":"2020-12-08 07:48:11.438",
"alert1_threshold_lost_sample_accumulated_msec":60000,
"alert1_actual_lost_sample_accumulated_msec":0,
...

The docs are light on simple questions like this, perhaps because they lead to more simple questions...

How do I get SELECT FOR JSON output into a string?

Answer: Use a SELECT INTO clause, or a subquery.

How do I actually code SELECT FOR JSON in a subquery?

Answer: Code a derived table with an alias table name and a single alias column name, because the select list for SELECT * FOR JSON consists of one single column.

How do I get line breaks?

Answer: Use REPLACE, and hope your data doesn't contain any comma-doublequote pairs... or write better code than this example :)

Foxhound 5 Giveth, Foxhound 5 Taketh Away

noreply@blogger.com (Breck Carter) — Thu, 03 Dec 2020 13:28:00 +0000

New Feature

The ADHOC user id can create procedures, functions, tables, indexes and views

If you like running adhoc queries on the Foxhound database, you'll love being able to CREATE your own procedures... and tables, and views, and functions.

For more information see What kind of adhoc queries can I write?

But . . .

Behavior Change: The ADHOC user id cannot be used in SQL Central to connect to the Foxhound database

You can use ISQL but not SQL Central; that door has been closed.

So . . .

Windows Start menu - Foxhound5 - 2 Adhoc Query via ISQL is your friend :)

Foxhound Shock! (Server Messages)

noreply@blogger.com (Breck Carter) — Wed, 02 Dec 2020 18:20:00 +0000

There's a new feature in Foxhound 5 that may come as a shock:

SQL Anywhere server messages are stored and displayed on the Monitor and History pages.

First, some background: Server messages appear in the SQL Anywhere console log, and in the dbsrv17.exe -o text file if you specify that option (you should).

Server messages are mostly boring "Information" messages

I. Database "ddd16" (ddd16.db) started at Wed Dec 02 2020 07:48
I. Finished checkpoint of "ddd16" (ddd16.db) at Wed Dec 02 2020 07:48

Some server messages are interesting

I. Database "ddd" mirroring:  becoming primary server

and some are very interesting

W. Database "ddd" mirroring:  Warning: Mirror is running behind the primary server by more than 60 seconds.
E. Database "ddd" mirroring:  mirror connection to server "partner2_server" failed

Foxhound 5 displays server messages as a convenience:

If you're going to the trouble of running Foxhound, why should you have to look somewhere else to see server messages?

But . . . server messages can be a bit of a surprise

. . . when you suddenly start seeing server messages in the Monitor page after upgrading Foxhound 5:

They can also be a SHOCK

If your application code or stored procedures contain MESSAGE TO CONSOLE statements,

and you are not in the habit of looking at the SQL Anywhere console log,
or you don't know where (or what) the SQL Anywhere console log is,

suddenly seeing a storm of MESSAGE TO CONSOLE output in the Foxhound Monitor page can be a shock.

Relax, you can turn off the storm

Just click on the little black gear (*) icon on one of the messages, and it'll take you directly to the Monitor Options page

In section 18. Server Messages, make sure you've selected the right target database,

then uncheck Display server messages on the Foxhound Monitor and Sample History pages
and click on Save.

But, maybe you don't want to

Server messages might be your friend :)

The Future Of SQL Anywhere

noreply@blogger.com (Breck Carter) — Mon, 30 Nov 2020 15:20:00 +0000

Here's a question that was asked today: https://sqlanywhere-forum.sap.com/questions/36291/sql-anywhere-future...

Question: In our company we are starting a new software project and I am in a dilemma about the database. Today we are using SQL Anywhere v17. But as I see it there is not much work going on on it. Is there any development on it? Could we expect a new version in near future?

Answer: I believe SQL Anywhere 17 has a long and successful future as an extremely powerful and stable relational database system. There probably won't be too many (any more?) "RFQ Checkpoint" features any time soon; e.g., no new kitchen-sink-in-the-database features like Java-objects-in-the-database", full-text search, spatial data.

Don't get me wrong, there's a bunch of kitchen-sink features I love; e.g., HTTP, proxy tables, HA, MobiLink, even SQL Remote. My point is, if you have a favorite non-relational, non-SQL feature you'd like stuffed into SQL Anywhere, don't hold your breath. For example, built-in JavaScript charts... but guess what? you can embed Highcharts in SQL Anywhere yourself, and use XMLHttpRequest() to dynamically request database data from the browser.

And if you're looking for further development of the in-memory database feature, or the addition of a columnar data store, well, that'll won't come until we get UltraLite HANA :)

Seriously, the "kitchen-sink" phase is over, SQL Anywhere has entered the "powerful and stable" stage in its life.

Consider this particular Use Case: SQL Anywhere is embedded in the Oracle MICROS RES 3700 Restaurant POS System which is used in hundreds of thousands of bars, restaurants, hotels and other hospitality establishments worldwide.

BTW, Highcharts rocks, so does FreeFind.

Foxhound 5 Update 1 Is Now Available

noreply@blogger.com (Breck Carter) — Wed, 18 Nov 2020 16:00:00 +0000

Foxhound is a front-line database monitor dedicated to SAP^® SQL Anywhere^® . . .

Foxhound runs all the time, in production.
It keeps watch over your databases and sends alerts when there are problems.
It keeps a record of database and connection-level performance data in its own SQL Anywhere database.
It lets you scroll and query all the data when you need to fix a problem.

Foxhound Version 5 Update 1 is Now Available

Most of the changes in Update 1 are bug fixes with a few minor improvements . . .

. . . but "minor" is in the eye of the beholder; here's a selection:

What's New in Foxhound 5 What's New in Update 1

Best Review Ever

noreply@blogger.com (Breck Carter) — Sat, 10 Oct 2020 17:28:00 +0000

     Re: Questions about Foxhound
   from: Loren Sherman at Healthpointe
     to: Breck Carter at gmail 
   date: Oct 9, 2020, 12:26 PM

This must be the most complete set of answers I ever received from anyone on anything in any universe. Thank you!!

The fRiDaY File - Connect to Running Database

noreply@blogger.com (Breck Carter) — Wed, 07 Oct 2020 18:37:00 +0000

From Connect to Running Database on another computer is failing . . .

Inside Foxhound: String De-Duplication

noreply@blogger.com (Breck Carter) — Tue, 03 Mar 2020 08:00:00 +0000

String de-duplication is the replacement of multiple duplicate string values with a single copy to save space.

Foxhound 5 uses custom code to implement de-duplication of two columns in the database, and this sometimes results in the database file being 50% smaller than in Foxhound 4.

This article shows

how existing Foxhound code was changed to implement string de-duplication, and
how concurrency problems were handled to prevent performance problems.

CREATE TABLE Changes
SELECT Changes
INSERT Changes
DELETE Changes
Final Thoughts: Is This For You?

CREATE TABLE Changes

The strings of interest are the Last Statement and Last Plan Text fields on the Foxhound Monitor and Sample History pages:

In Foxhound 4 they're stored in this table:

CREATE TABLE DBA.rroad_group_2_property_pivot (  
   sampling_id                                     UNSIGNED INT NOT NULL,
   sample_set_number               /* PK      X */ UNSIGNED BIGINT NOT NULL,
   connection_number               /* PK        */ BIGINT NOT NULL,
   ...
   LastStatement                                   LONG VARCHAR NULL,
   LastPlanText                                    LONG VARCHAR NULL DEFAULT '',
   ...
   PRIMARY KEY (
      sample_set_number,
      connection_number ) );

In Foxhound 5 they've been replaced by two unsigned integers:

CREATE TABLE dba.rroad_group_2_property_pivot (  
   sampling_id                                         UNSIGNED INT NOT NULL,
   sample_set_number                   /* PK      X */ UNSIGNED BIGINT NOT NULL,
   connection_number                   /* PK        */ BIGINT NOT NULL,
   ...
   LastStatement_id                                    UNSIGNED INT NOT NULL DEFAULT 0,
   LastPlanText_id                                     UNSIGNED INT NOT NULL DEFAULT 0,
   ...
   PRIMARY KEY (
      sample_set_number,
      connection_number ) );

The LastStatement_id and LastPlanText_id columns are effectively foreign key columns pointing to a new table (rroad_long_varchar) where the actual LONG VARCHAR values are stored:

CREATE TABLE dba.rroad_long_varchar ( 
   long_varchar_id   /* PK        */ UNSIGNED INT NOT NULL DEFAULT autoincrement,
   reference_count                   BIGINT NOT NULL,
   long_varchar      /*       U   */ LONG VARCHAR NOT NULL,
   PRIMARY KEY ( long_varchar_id ) );

CREATE UNIQUE INDEX ux_long_varchar ON dba.rroad_long_varchar ( long_varchar );

The UNIQUE INDEX has two purposes: first, it guarantees there are no duplicates, and second, it supports the SELECT WHERE rroad_long_varchar.long_varchar = @new_long_varchar clause used in a later section.

SELECT Changes

In Foxhound 5 the adhoc view sample_connection has been modified to return the same result set as in Foxhound 4. In other words, even though the underlying rroad_group_2_property_pivot table no longer contains LastStatement and LastPlanText, those columns do appear in the view.

Here's how:

In Foxhound 4 the view is a simple "SELECT everything from the base table"

create VIEW sample_connection AS 
SELECT * 
  FROM rroad_group_2_property_pivot;

whereas Foxhound 5 uses joins to turn LastStatement_id and LastPlanText_id into the original LastStatement and LastPlanText:

create VIEW sample_connection AS 
SELECT rroad_group_2_property_pivot.sampling_id,
       rroad_group_2_property_pivot.sample_set_number, -- PRIMARY KEY
       rroad_group_2_property_pivot.connection_number, -- PRIMARY KEY
       ...
       rroad_LastStatement.long_varchar   AS LastStatement,
       rroad_LastPlanText.long_varchar    AS LastPlanText,
       ...
  FROM rroad_group_2_property_pivot
       INNER JOIN ( SELECT * 
                      FROM rroad_long_varchar 
                  ) AS rroad_LastStatement
               ON rroad_LastStatement.long_varchar_id = rroad_group_2_property_pivot.LastStatement_id
       INNER JOIN ( SELECT * 
                      FROM rroad_long_varchar 
                  ) AS rroad_LastPlanText
               ON rroad_LastPlanText.long_varchar_id = rroad_group_2_property_pivot.LastPlanText_id

Foxhound itself uses the same joins to display LastStatement and LastPlanText on the Monitor and History pages.

INSERT Changes

When the Foxhound sampling process inserts a connection sample, it calls a function to convert the SQL Anywhere LastStatement and LastPlanText properties into unsigned integers:

INSERT rroad_group_2_property_pivot (
       sampling_id, 
       sample_set_number,
       connection_number,
       ...
       LastStatement_id,
       LastPlanText_id,
       ...
SELECT @sampling_id,
       @sample_set_number,
       connection_number,
       ...
       rroad_get_long_varchar_id ( LastStatement ), 
       rroad_get_long_varchar_id ( LastPlanText ),
       ...
  FROM ...

Here's that function:

CREATE FUNCTION rroad_get_long_varchar_id ( 
   IN @long_varchar        LONG VARCHAR,
   IN @is_recursive_call   VARCHAR ( 1 ) DEFAULT 'N' )
   RETURNS UNSIGNED INTEGER
   NOT DETERMINISTIC
BEGIN

DECLARE @new_long_varchar              LONG VARCHAR;
DECLARE @existing_long_varchar_id      UNSIGNED INTEGER;
DECLARE @existing_long_varchar         LONG VARCHAR;
DECLARE @sqlstate                      VARCHAR ( 5 );
DECLARE @errormsg                      VARCHAR ( 32767 );
DECLARE @sqlcode                       INTEGER;
DECLARE @diagnostic_location           VARCHAR ( 20 );
DECLARE @v_existing_long_varchar_id    VARCHAR ( 128 );

SET @new_long_varchar = TRIM ( COALESCE ( @long_varchar, '' ) );

IF @new_long_varchar = '' THEN
   RETURN 0;
ELSE

The following section checks if the LONG VARCHAR value has already been stored in the rroad_long_varchar table.

   SET @existing_long_varchar_id = NULL;

   SELECT rroad_long_varchar.long_varchar_id 
     INTO @existing_long_varchar_id
     FROM rroad_long_varchar
    WHERE rroad_long_varchar.long_varchar = @new_long_varchar;

   IF @existing_long_varchar_id IS NOT NULL THEN

At this point @existing_long_varchar_id is ready to be returned to the caller because it points to the LONG VARCHAR value that already exists in rroad_long_varchar...

...but first, some bookkeeping is necessary.

The Foxhound purge process must know when it's safe to delete old LONG VARCHAR values, and it does this by checking that rroad_long_varchar.reference_count is zero.

The following code increments the reference_count by 1 each time a new reference is encountered, or rather, it attempts to increment the reference count:

      BEGIN -- handle exception

         SET TEMPORARY OPTION BLOCKING = 'OFF';

         UPDATE rroad_long_varchar
            SET reference_count = reference_count + 1
          WHERE long_varchar_id = @existing_long_varchar_id;

         SET TEMPORARY OPTION BLOCKING = 'ON';

It is possible that multiple almost-simultaneous calls to this function can reach this point with same LONG VARCHAR value; i.e., Foxhound can sample up to 100 target databases in parallel, and it's possible for the same LastStatement and LastPlanText values could be received from multiple targets.

When that happens, the above UPDATE can fail with SQLCODE -210 Row locked, -306 Deadlock detected, or -307 All threads are blocked.

Rather than have these collisions cause waits or rollbacks, BLOCKING = 'OFF' is used to force all collisions to raise exceptions (even row locks), and those exceptions are all handled by the next section of code which defers the incrementing of reference_count until later.

      EXCEPTION WHEN OTHERS THEN

         SELECT SQLCODE INTO @sqlcode;

         SET TEMPORARY OPTION BLOCKING = 'ON';

         IF @sqlcode IN ( -210, -306, -307 ) THEN 

            -- Defer the UPDATE until later.

            INSERT rroad_long_varchar_deferred_increment ( long_varchar_id ) 
            VALUES ( @existing_long_varchar_id );

In the grand scheme of things, incrementing reference_count is very low priority; it is much more important for Foxhound sampling to proceed without delay.

Here's the table used by the above INSERT; each row represents one deferred reference_count increment of +1 for one long_varchar_id:

CREATE TABLE rroad_long_varchar_deferred_increment ( 
   increment_id               UNSIGNED INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   long_varchar_id            UNSIGNED INTEGER NOT NULL );

These deferrals are indefinite; the rows inserted in this table aren't needed until the Foxhound purge process is ready to delete unused rows in rroad_long_varchar.

         ELSE

            -- Let the outer block handle the exception.

            RESIGNAL;

         END IF;

      END; -- handle exception

      RETURN @existing_long_varchar_id;

The above RETURN finishes handling a matching row in the rroad_long_varchar table.

The next section handles a new LONG VARCHAR value by inserting a new row in rroad_long_varchar:

   ELSE -- @existing_long_varchar_id IS NULL

      BEGIN -- handle exception

         INSERT rroad_long_varchar (
            long_varchar_id, 
            reference_count,
            long_varchar )
         VALUES ( 
            DEFAULT,
            1,
            @new_long_varchar );

It is also possible that multiple almost-simultaneous calls to this function can reach this point with same LONG VARCHAR value.

When that happens, the first execution of the above INSERT statement will work and later executions will (correctly) fail with SQLCODE -196 Index 'ux_long_varchar' for table 'rroad_long_varchar' would not be unique.

The resulting exceptions are handled in the next section, by using recursive calls to this function to handle duplicate LONG VARCHAR values.

The recursive calls will ( should :) work because they won't ( shouldn't :) reach this section of code.

      EXCEPTION WHEN OTHERS THEN

         SELECT SQLCODE, SQLSTATE, ERRORMSG() 
           INTO @sqlcode, @sqlstate, @errormsg;

         IF @sqlcode = -196 THEN -- Index 'ux_long_varchar' for table 'rroad_long_varchar' would not be unique

            IF @is_recursive_call = 'N' THEN

               -- Try a recursive call, and this time @existing_long_varchar_id IS NOT NULL.

               RETURN rroad_get_long_varchar_id ( @new_long_varchar, 'Y' );

            ELSE

               -- This is already a recursive call, and it is still failing.

               CALL rroad_exception ( STRING ( 
                  @diagnostic_location, '(454eh1) Unexpected error in recursive call to rroad_get_long_varchar_id: ', 
                  ' SQLCODE = ', @sqlcode,  
                  ', SQLSTATE = ', @sqlstate,  
                  ', ERRORMSG() = ', @errormsg ) );

               RESIGNAL;

            END IF;

         ELSE

            -- Let the outer block handle the exception.

            RESIGNAL;

         END IF;

      END; -- handle exception

      RETURN @@IDENTITY;

   END IF;

END IF;

END;

DELETE Changes

In Foxhound 4, deleting old values of LastStatement and LastPlanText was easy: just DELETE the rroad_group_2_property_pivot rows holding those values.

In Foxhound 5, LastStatement and LastPlanText are stored in rroad_long_varchar, and those rows can't be deleted until rroad_long_varchar.reference_count sinks to zero.

That happens when enough rows in rroad_group_2_property_pivot are deleted to fire the following trigger enough times to force the reference_count down to zero:

CREATE TRIGGER trd_rroad_group_2_property_pivot
   BEFORE DELETE ON rroad_group_2_property_pivot
   REFERENCING OLD AS old_rroad_group_2_property_pivot
   FOR EACH ROW
BEGIN

   -- Decrement rroad_long_varchar.reference_count as necessary.

   IF old_rroad_group_2_property_pivot.LastStatement_id > 0 THEN
      UPDATE rroad_long_varchar
         SET rroad_long_varchar.reference_count = rroad_long_varchar.reference_count - 1
       WHERE rroad_long_varchar.long_varchar_id = old_rroad_group_2_property_pivot.LastStatement_id;
   END IF;

   IF old_rroad_group_2_property_pivot.LastPlanText_id> 0 THEN
      UPDATE rroad_long_varchar
         SET rroad_long_varchar.reference_count = rroad_long_varchar.reference_count - 1 
       WHERE rroad_long_varchar.long_varchar_id = old_rroad_group_2_property_pivot. LastPlanText_id;
   END IF;

END;

Note that it is possible for reference_count to sink below zero when rows are held in the rroad_long_varchar_deferred_increment table.

The following code shows what happens in the Foxhound purge process after old rows in rroad_group_2_property_pivot have been deleted, and it is time to consider deleting old rows in rroad_long_varchar.

The first section applies the deferred increments to the reference_count so the second section won't delete any rows in rroad_long_varchar that are still needed.

Note that rows in the rroad_long_varchar_deferred_increment table are deleted as soon as they are used.

FOR f_fetch_deferred_increment AS c_fetch_deferred_increment INSENSITIVE CURSOR FOR
SELECT rroad_long_varchar_deferred_increment.increment_id    AS @increment_id,
       rroad_long_varchar_deferred_increment.long_varchar_id AS @long_varchar_id
  FROM rroad_long_varchar_deferred_increment
FOR READ ONLY
DO

   -- Apply and delete the deferred increment as an atomic operation.

   BEGIN ATOMIC 

      UPDATE rroad_long_varchar
         SET rroad_long_varchar.reference_count = rroad_long_varchar.reference_count + 1
       WHERE rroad_long_varchar.long_varchar_id = @long_varchar_id;

      DELETE rroad_long_varchar_deferred_increment
       WHERE rroad_long_varchar_deferred_increment.increment_id = @increment_id;

   END; 

   COMMIT;
           
END FOR;

The second section takes care of deleting rroad_long_varchar rows that are no longer referenced.

Note that up to 100 Foxhound sampling process could be inserting data while the purge is running, and any one of them could insert new rows in rroad_long_varchar_deferred_increment; that's the reason for the AND NOT EXISTS predicate in the following FOR loop:

FOR f_long_varchar AS c_long_varchar NO SCROLL CURSOR FOR
SELECT rroad_long_varchar.long_varchar_id   AS @long_varchar_id
  FROM rroad_long_varchar
 WHERE rroad_long_varchar.reference_count <= 0
   AND rroad_long_varchar.long_varchar_id > 0 -- don't delete empty row
   AND NOT EXISTS ( SELECT *                  -- don't delete rows that have deferred increments
                      FROM rroad_long_varchar_deferred_increment
                     WHERE rroad_long_varchar_deferred_increment.long_varchar_id 
                         = rroad_long_varchar.long_varchar_id )
 ORDER BY rroad_long_varchar.long_varchar_id
FOR UPDATE
DO

   DELETE rroad_long_varchar
    WHERE CURRENT OF c_long_varchar;

END FOR;

Final Thoughts: Is This For You?

Before implementing string de-duplication in your application, run a test to remove duplicates to see if the savings are worthwhile.

String de-duplication is worthwhile in Foxhound because a very large number of identical LONG VARCHAR strings are stored in the database. In other applications, where there are fewer duplicates and/or shorter strings, the advantage might not be so great, and alternatives like COMPRESS() might be better.

Your application may already be taking advantage of the "Blob Sharing" feature built in to SQL Anywhere even though it "only occurs when you set values of one column to be equal to those of another column".

In other words, blob sharing only works when your application explicitly creates identical copies (UPDATE t column1 = column2) and it has no effect when your application creates new values separately with no reference to previous identical values.

In Foxhound the code which searches for an existing string value uses a brute-force string comparison "WHERE rroad_long_varchar.long_varchar = @new_long_varchar" supported by an index

CREATE UNIQUE INDEX ux_long_varchar ON dba.rroad_long_varchar ( long_varchar );

but that might not perform well when dealing with giant LONG BINARY values. Although it wasn't the case with Foxhound, you may find an alternative method (e.g., comparing HASH() values) will perform better.

The special exception handling might not be necessary for applications where collisions are unlikely; i.e., in other applications it might be OK to block and wait.

String de-duplication didn't add much runtime overhead to Foxhound, but performing de-duplication on a large existing database can take a very long time; e.g., it can add hours to the process of upgrading a Foxhound 4 database to Foxhound 5.

Dealing With Out-Of-Date Peaks

noreply@blogger.com (Breck Carter) — Fri, 28 Feb 2020 20:21:00 +0000

The Foxhound "Peaks since" line lets you jump directly to the most extreme value of each performance statistic on the History page; e.g., 28 Unsch Req, 79 blocked connections, 100% CPU and so on:

Over time, however, the Peaks line can become out-of-date when the Foxhound purge deletes old samples.

For example, after the Purge deleted the sample with 28 Unsch Req, the Peaks line still showed that value but you could no longer click on it:

Foxhound 5 now lets you refresh the Peaks line by calling the new rroad_recalculate_peaks() stored procedure.

The rroad_recalculate_peaks() procedure updates all the out-of-date peaks with clickable values; e.g.,13 Unsch Req:

Tip: You must use the new shortcut Foxhound5 - 11 Admin Update via ISQL to call rroad_recalculate_peaks(). The default password is SQL, but you can change that; see this topic in the Help.

DIY CREATE VIEW Statements In Foxhound 5

noreply@blogger.com (Breck Carter) — Wed, 26 Feb 2020 08:00:00 +0000

Open access is a hallmark of Foxhound:

Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it.

Foxhound Version 5 extends that to include CREATE VIEW and other DDL statements; here's what you can and cannot do:

      no  CREATE EVENT
      no  CREATE EXISTING TABLE 
 yes      CREATE FUNCTION
      no  CREATE FUNCTION [External call]
 yes      CREATE INDEX
      no  CREATE MATERIALIZED VIEW
 yes      CREATE PROCEDURE
      no  CREATE PROCEDURE [External call]
      no  CREATE SEQUENCE
      no  CREATE SERVICE
 yes      CREATE TABLE
 yes      CREATE TEMPORARY PROCEDURE
      no  CREATE TRIGGER
      no  CREATE USER
 yes      CREATE VARIABLE [Connection-scope]
      no  CREATE VARIABLE [Database-scope] 
 yes      CREATE VIEW 
 yes      DELETE
 yes      INSERT
 yes      SELECT
 yes      TRUNCATE
 yes      UNLOAD
 yes      UPDATE

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

For Example: Active Alerts

There are two definitions for "Active Alert":

An Alert that is currently active, and
an Alert that was active at some earlier point in time.

Adhoc queries for the first kind are easy; there's a column for that:

SELECT *
  FROM alert
 WHERE alert_is_clear_or_cancelled = 'N' 
   AND sampling_id = 3;

Foxhound 5 displays the second kind in the new scroll to Active Alert drop-down shown in this earlier blog post:

Here's prototype query for the same thing, using a proposed view named "active_alert":

SELECT alert_number,
       alert_title
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726  
 ORDER BY alert_occurrence DESC;

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

Do-It-Yourself: CREATE VIEW ADHOC.active_alert

With Foxhound 5 you can now CREATE your own views:

CREATE VIEW ADHOC.active_alert AS
SELECT sample_header.sampling_id,
       sample_header.sample_set_number,
       alert.alert_occurrence,
       alert.alert_number,
       LEFT ( alert_title.alert_title, 25 ) AS alert_title,
       active_alert_range.clear_or_cancelled_locator_number
  FROM sample_header
          LEFT OUTER JOIN active_alert_range
                       ON active_alert_range.sampling_id                       =  sample_header.sampling_id
                      AND active_alert_range.active_locator_number             <= sample_header.sample_set_number
                      AND active_alert_range.clear_or_cancelled_locator_number >  sample_header.sample_set_number 
          LEFT OUTER JOIN alert
                       ON alert.alert_occurrence = active_alert_range.active_locator_number
          LEFT OUTER JOIN alert_title
                       ON alert_title.alert_number = alert.alert_number;

The Foxhound drop-down list box uses a query like this to display first two columns, and it also uses the third column (alert_occurrence) to implement the "scroll to Active Alert" feature:

SELECT alert_number,
       alert_title,
       alert_occurrence
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726 
 ORDER BY alert_occurrence DESC;

alert_number alert_title                   alert_occurrence 
------------ ------------------------- -------------------- 
          13 File fragmentation                        2703 
          16 I/O operations                            2441 
          13 File fragmentation                         155

For example, when the user clicks on "#16 I/O operations" the History page is scrolled to the Alert #16 on line 2441:

When you use active_alert in your own adhoc queries, you have access to several columns including clear_or_cancelled_locator_number which tells you when (and if) each Alert became inactive.

Here's what the whole result set looks like:

SELECT *
  FROM active_alert
 WHERE sampling_id       = 3 
   AND sample_set_number = 2726  
 ORDER BY alert_occurrence DESC;

sampling_id    sample_set_number     alert_occurrence alert_number alert_title               clear_or_cancelled_locator_number 
----------- -------------------- -------------------- ------------ ------------------------- --------------------------------- 
          3                 2726                 2703            1 Database unresponsive                                  2745 
          3                 2726                 2441           16 I/O operations                                         2830 
          3                 2726                  155           13 File fragmentation                                     2829

The active_alert view is based on several views built-in to Foxhound:

active_alert_range   One row per alert containing from and to locator numbers and timestamps for 
                        the range of lines in the History page for which this Alert was active.
alert                One row per alert.
alert_cancelled      One row per alert cancellation; indirectly referenced by active_alert_range.
alert_title          One row for the text title corresponding to each Alert number.
all_clear            One row per alert all-clear; indirectly referenced by active_alert_range.
sample_header        One row for each sample recorded by the Foxhound Database Monitor, 
                        holding various server and database-level properties and computed columns.

For more information about writing and running adhoc SQL in Foxhound 5, see this chapter in the Help.

Scrolling to Active Alerts in Foxhound 5

noreply@blogger.com (Breck Carter) — Fri, 21 Feb 2020 08:00:00 +0000

Alerts have always been a big deal in Foxhound... they're the main reason Foxhound sends emails, and they're the main reason people buy Foxhound.

Alerts draw your immediate attention, but they're also important after-the-fact: "When did it happen, and why?"

Prior to Foxhound Version 5 the only way to find Alerts was to click on the Message links on the History page.

Sadly, that means click click clicking through all the mismatches:

Foxhound 5 fixes that by keeping track of Active Alerts, the zero or more Alerts that were in effect (not yet cleared or cancelled) at any particular point in time.

For each row on the History page, the Active Alerts drop-down list box shows you what was in effect:

Now you can click on a link to go to "When did it happen?"

The Unexpected Benefits Of Conn Flags Count

noreply@blogger.com (Breck Carter) — Wed, 19 Feb 2020 08:00:00 +0000

Some Foxhound 5 connection flags are more important than others, like #9 - Long uncommitted:

Performance Tip: This Flag is useful if you want to be warned when a single connection is sucking all the air out of the room; i.e., when a long-running transaction has a performed a large amount of work that is not yet committed to the database.

Other connection flags might not be so important, especially if they appear for single connections. For example, Connection Flag #15 - Index add rate might just mean one connection is doing a bit more work than normal.

But what if many connections each have one flag set, or worse, what if many connections have many flags?

That's where Conn Flags Count comes in

At the database sample level, Conn Flags Count is the total number of connection flags set across all connections for one sample; large values will draw your attention to where there may be many bottlenecks:

At the connection level, Conn Flags Count is the number of connection flags set for one connection. You can sort the connections to bring the interesting connections to the top:

Conn Flags Count Was An Afterthought

When the Connection Flags feature was first added to Foxhound 5,

the flags_set_count column wasn't displayed,
then it was displayed in the Connections section because "if it's worth calculating, it's worth showing",
then it was made sortable because "if it's worth showing, it's worth sorting",
then the Samples & Messages section got its own Conn Flags Count,
and the Peaks row got one too:

Now Conn Flags Count has become a valuable feature in its own right.

Mini-Backups in Foxhound 5

noreply@blogger.com (Breck Carter) — Mon, 17 Feb 2020 08:00:00 +0000

Quick! When was the last time you backed up your Foxhound database?

If you answered "never" that's OK, you shouldn't have to... a tool like Foxhound should look after its own backups.

And now Foxhound Version 5 does just that:

Every time you change any options and settings, Foxhound creates a "mini-backup" containing just those options and settings.

Mini-backup files don't take up much space because they don't contain any sample data:

To restore from a Mini-Backup file, click on the Foxhound Windows shortcut 9 Mini-Restore:

The Mini-Restore starts by opening the Activation page on an empty copy of the Foxhound database:

Use Option 2 to pick a mini-backup file and then click on Run Mini-Restore.

The next thing you'll see is sampling up and running again:

All the old monitor samples are gone, but all your intellectual property (the options and settings) has been restored.

For more information see Mini-Backup and Mini-Restore.

Read-Only Browser Mode In Foxhound 5

noreply@blogger.com (Breck Carter) — Wed, 12 Feb 2020 08:00:00 +0000

Until now, Foxhound has lacked any form of browser login or authentication...

Anyone who can open Foxhound in a browser can see (and do) everything offered by Foxhound.

The SQL Anywhere Monitor requires a user name and password to login but unless you work hard at changing passwords and creating users, it behaves much the same as Foxhound prior to Version 5...

Anyone can use admin / admin to see (and do) everything offered by the SQL Anywhere Monitor.

Foxhound Version 5 now offers a new and different form of browser login. It's called "Administrator Authentication" and it has these characteristics:

It's optional, you can turn it on and off, and the default is "no login required, for anything".
It implements two modes of operation...
- Read-Only mode allows everyone to see everything but not change anything, and
- Administrator mode requires a username and password to update Foxhound options and settings.
An unlimited number of browser sessions may use Read-only mode, with no user name or password required.
Only one browser session at a time can use Administrator mode.
A browser session will remain logged in to Administrator mode until
- the session or cookie times out (default 12 hours),
- the logged-in user clicks Logout, or
- some other session logs in to Administrator mode, which silently logs out the session already logged in.

For more information see Administrator Authorization in Foxhound 5.

Server Messages: Another Release-Defining Feature in Foxhound Version 5

noreply@blogger.com (Breck Carter) — Mon, 10 Feb 2020 08:00:00 +0000

From time to time the SQL Anywhere server sends important error, warning and diagnostic messages to three separate destinations:

the "server console" window. . . unless that window doesn't exist because the server was started as a Windows service,
the text file named in the -o filename.txt option. . . unless that option was omitted from the server startup command line, and
result sets returned by sa_server_messages(). . . if you call it.

The capture and display of sa_server_messages() data is one of the features that makes Foxhound 5 stand apart from previous versions.

Foxhound merges server messages and monitor samples in descending order by timestamp, making it easy to see "What else was going on?" at any particular point.

Here's what server messages look like for a secondary (mirror) server that became the primary after a failover :

Your applications can write their own server messages, and they will appear in the Foxhound Monitor and History pages like this:

MESSAGE STRING ( 'INFO message' ) TO CONSOLE;
MESSAGE STRING ( 'WARN message' ) TYPE WARNING TO CONSOLE;
MESSAGE STRING ( 'ERR message'  ) TYPE ACTION TO CONSOLE;

1:17:09 PM          -- E. ERR message (2ms) 
1:17:09 PM          -- W. WARN message (11ms) 
1:17:09 PM          -- I. INFO message (1m 53s)

For more information see The Sample History Page - Server Messages.

Connection Flags: A Release-Defining Feature in Foxhound Version 5

noreply@blogger.com (Breck Carter) — Thu, 06 Feb 2020 14:01:00 +0000

Connection Flags are one of the features that make Foxhound 5 stand apart from previous versions. Connection Flags extend the usefulness of database-level Alerts and Peaks down into the nether regions of individual connections by letting you jump directly to connection samples that exhibit suspicious behavior.

Previously, you could sort connection samples in descending order by CPU time to bring high-CPU connections to the top of the list, and then sort on Transaction Time to see long-running connections at the top, but not both at the same time.

With Connection Flags you can sort on Conn Flags Count to see connections with Flag #5 - CPU usage, Flag #3 - Long transaction, and 28 other flags all sorted to the top if the list.

For more information about Connection Flags see section 17. Flag Settings in the Foxhound 5 docs.

SQL Anywhere Is NOT Dead

noreply@blogger.com (Breck Carter) — Tue, 04 Feb 2020 13:38:00 +0000

Is SQL Anywhere dead?
Not according to the SAP Store...

Is SQL Anywhere dead?
Not according to SAP support...

Is SQL Anywhere dead?
Not according Novalsys...

Is SQL Anywhere dead?
Not according to Foxhound...

Top Five Flaws In Foxhound

noreply@blogger.com (Breck Carter) — Sat, 01 Feb 2020 19:06:00 +0000

Do you see anything that looks familiar?

The transaction log is too big!

The database is too big!

The purge doesn't work very well!

There's no Undo for Reset Peaks!

Foxhound emails don't display properly!

1. The transaction log is too big!

"Too big" is too polite a term for a file that fills up the hard drive, and when you run the Purge it grows even bigger!

Foxhound Version 5 solves this problem by adding -m "Truncate Log On Checkpoint" to the dbsrv17 command that starts Foxhound.

Those two little characters "-m" have two huge consequences: First, the transaction log is wiped clean every single time Foxhound takes a checkpoint, and second, you can no longer count on the transaction log being any use at all in a Foxhound database recovery strategy.

If you care about a recovery strategy (and not everyone does) you're left with three choices:

Take the -m back out and go back to however you used to deal with giant Foxhound transaction logs,
rely on full backups and give up on restoring recent changes, or
use the new Mini-Backup and Mini-Restore features to recover Foxhound options and settings... which is (probably) all you need.

Enhancement: The size of the transaction log is automatically limited by the dbsrv17 -m truncate log on checkpoint.
Behavior Change: The transaction log is no longer available for restoring recent changes after restoring a backup.
New Feature: The Mini-Backup process automatically backs up recent changes to Foxhound options and settings.
New Feature: The Mini-Restore process restores your Foxhound options and settings into an empty foxhound5.db.

2. The database is too big!

The Foxhound database file grows at about 100K per monitored connection per hour, which means it gets too big too fast when your database has a lot of connections.

A lot of this space is used by only two columns: Last Statement and Last Plan Text.

Foxhound 5 uses a custom string de-duplication process on these columns to reduce the database size by a lot... sometimes half.

Enhancement: String de-duplication has reduced the size of the Foxhound database file.
Behavior Change: It may take a very long time to upgrade all the data in a very large Foxhound 3 or 4 database.
Behavior Change: The Foxhound database may contain a lot of free space after upgrading a very large database.

3. The purge doesn't work very well!

The original Foxhound Purge process was over-designed, which is a polite way to say it was badly designed.

The Purge process in Foxhound 5 has been completely redesigned and rewritten, with all the ~~stupid useless~~ fancy features removed, and a more aggressive approach taken to its primary purpose... which is to "Delete data!"

You can set up a separate 24x7 schedule for the Purge, and there are special "Hourly" and "Midnight" buttons to make administration easier.

New Feature: The Foxhound purge process is now fast enough to keep up with database growth.
Behavior Change: The "Purge uninteresting connection data" feature has been removed.
Behavior Change: The Purge process no longer forces extra checkpoints while it is running.
Behavior Change: By default the Purge process starts once an hour, on the hour.
Behavior Change: The purge process no longer puts "Older samples purged at" in the History page.
Behavior Change: The Purge process may now delete all samples from a stopped sampling session.
New Feature: The Mini-Backup process automatically backs up recent changes to Foxhound options and settings.
New Feature: The Mini-Restore process restores your Foxhound options and settings into an empty foxhound5.db.

4. There's no Undo for Reset Peaks!

The "Peaks since" line is one of the most useful features in Foxhound. It lets you jump directly to the most extreme value of each performance statistic on the History page; e.g., to the highest CPU usage, largest Unscheduled Requests, highest Disk Reads and so on.

When the Peaks line is no longer meaningful (say, after a server upgrade) the "Reset Peaks" button clears all the values and starts the peak calculation process over again.

But... if you have ever pressed Reset Peaks by accident you know what "regret" means.

What you want is an "Undo" button; what Foxhound 5 gives you is a "Recalculate Peaks" procedure.

Recalculate Peaks is actually better than Undo: If the old Peaks line contained some values that are now obsolete because the corresponding samples have been deleted, Recalculate Peaks refreshes those values by only looking at samples which still exist.

Enhancement: A procedure to recalculate the "Peaks since" row can be run via ISQL.

5. Foxhound emails don't display properly!

Over the years Foxhound has been plagued by random glitches in the way HTML emails are displayed in Google Mail and other browser-based email clients.

Fixing individual symptoms didn't help because new and different glitches kept cropping up.

To solve this problem once and for all, HTML emails in Foxhound 5 are vastly simplified and the details are delivered as attachments:

New Feature: The Attach files to emails: checkbox has been added to the Monitor Options page.
Enhancement: AutoDrop Notice emails have been enhanced to show connection-level sample data.
Usability Improvement: AutoDropped connection messages now contain links to Connection History pages.
Bug Fix: Alert and AutoDrop emails have been changed to display properly in HTML browsers.

For more information see Foxhound Version 5.

Foxhound 5 Smoke Testing Underway

noreply@blogger.com (Breck Carter) — Sat, 25 Jan 2020 19:43:00 +0000

So far, so good . . .

Smoke Test Alert Email Setup.txt
Smoke Test Alerts  1 Database unresponsive.txt
Smoke Test CPU High CPU runaway queries.txt
Smoke Test Foxhound As Service.txt
Smoke Test GUI all the installed shortcuts.txt
Smoke Test HA using HASETUP2-V17.txt
Smoke Test HTTPS for Foxhound on browser.txt
Smoke Test Mini-Backup and Mini-Restore.txt
Smoke Test OFSS on remote HA.txt
Smoke Test Target V5 on V6.txt
Smoke Test Targets V6 through V17.txt
Smoke Test Volume 1 Foxhound x 1 target x 1000 conns.txt

\\\\\ DONE TO HERE -- 2020 01 24 - 5.0.5432a - built at 2020 01 24 09:45 in a SQL Anywhere 17.0.9.4882 database created at 2020 01 24 09:45

RC49 Smoke Test Activate Upgrade Foxhound3 4386 2G SANITIZED.txt

RC51 Smoke Test Activate Upgrade Foxhound4 4740 unpatched 12G.txt

Regression Testing

noreply@blogger.com (Breck Carter) — Mon, 06 Jan 2020 13:35:00 +0000

Enhanced User Testing

noreply@blogger.com (Breck Carter) — Thu, 02 Jan 2020 17:51:00 +0000

Let's take that one step further

noreply@blogger.com (Breck Carter) — Mon, 30 Dec 2019 08:00:00 +0000

Highcharts Demos	highcharts.com/demo
Highcharts Documentation	highcharts.com/docs/index
Highcharts JS API Reference	api.highcharts.com
Working with Highcharts data.js	highcharts.com/docs/working-with-data/data-module
Highcharts CSV sample	jsfiddle.net/gh/get/library/pure/highcharts/highcharts/tree/master/samples/highcharts/data/csv
XMLHttpRequest Tutorial	javascript.info/xmlhttprequest
Web Services in SQL Anywhere	help.sap.com/viewer/98ad9ec940e2465695685d98e308dff5/17.0/en-US/3bd432396c5f10148782bc977a956885.html

	Demo, Chart!	Static, Chart!	Hello, Chart!
Where does it run?	In this blog post	In a website (no CORS)	In a SQL Anywhere 17 database
HTML location:	blogspot.com	bcarter.com	SQL Anywhere 17 database
Highcharts JavaScript:	code.highcharts.com	code.highcharts.com	SQL Anywhere 17 database
Data location:	CSV files in static website bcarter.com	CSV files in static website bcarter.com	SQL Anywhere 17 database
Data retrieval:	Static file via XMLHttpRequest()	Static file via XMLHttpRequest()	Web service via XMLHttpRequest()
CORS headers:	github.com/Rob--W/cors-anywhere	Not required	Not required