Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

if the web service finishes normally, by definition the COMMIT will have been executed, which means all the updates performed during the database transaction will have been successfully written to the database, and

Some of the exceptions are expected: SQLCODE = -299 happens when the service is cancelled by the browser. The second column (365, 366, etc) shows the connection number so you can see how the executions overlap.

The other exceptions are not expected: the SQLCODE = -193 primary key violations mean the DELETE / INSERT logic doesn't work properly when the Save button is given a vigorous workout.

Here's another interesting observation: the "SQL error" doesn't reach the browser until after the primary key exception has been raised several times. How do we know that? Because once the browser displays "SQL error" there's no Save button any more, and that clearly wasn't the case: the repeated "s started..." messages prove there was a Save button on screen long after the first primary key exception.

Locking, blocking, the atomic nature of transactions and the reentrant nature of SQL Anywhere executable code... none of that helps this application avoid a showstopper exception.

Also: Just because an older version isn't "fully supported" doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new EBFs released. For a complete list of everything available for download see Downloads - EBFs / Maintenance.

Before tackling the "top 10" question, let's confirm that the CROSS APPLY answer to the previous question "How do I select the first row in each partition of a table?" works just as well on this table:

SELECT first_t.*
FROM ( SELECT DISTINCT
t.partition_id
FROM t
ORDER BY t.partition_id ) AS unique_partition
CROSS APPLY
( SELECT FIRST *
FROM t
WHERE t.partition_id = unique_partition.partition_id
ORDER BY t.entry_id ) AS first_t
ORDER BY first_t.partition_id;

Yes, it works:

partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'

Plus, it is clear from the SQL what its purpose is:

When the "( SELECT DISTINCT ... )" query is translated into English you get "select each distinct partition_id,"

when the "( SELECT FIRST ... )" query is translated into English you get "select the first row by entry_id for each row in the other result set," and then

when the combined CROSS APPLY query is translated by combining the other two translations, you get "select the first row by entry_id for each distinct partition_id"

which is pretty much a restatement of the original question: "How do I select the first row in each partition?"

It's not perfect; in a perfect world, every single query problem would be solved by a single SQL SELECT statement, not a combination of two queries. But, this is real life... and the original question did refer to two queries: "first row" and "each partition", and the CROSS APPLY contains exactly those two queries...

Not like the Old School solution which does funky things with MIN and GROUP BY, neither of which are even indirectly mentioned in the original question,

SELECT t.*
FROM t INNER JOIN ( SELECT partition_id AS partition_id,
MIN ( entry_id ) AS min_entry_id
FROM t
GROUP BY partition_id
) AS first_row
ON t.partition_id = first_row.partition_id
AND t.entry_id = first_row.min_entry_id
ORDER BY t.partition_id;

or the OLAP solution which introduced the OVER, WINDOW, PARTITION BY and nested ORDER BY clauses plus the RANK function and a WHERE clause to deal with the RANK result.

Go ahead, translate either of those queries into English, see what you get!

Back on topic...

Question: How do I select the top 10 rows in each partition of a table?"

It turns out the CROSS APPLY solution scales just as well as the OLAP solution. With the OLAP solution, the "WHERE entry_rank = 1" predicate was simply changed to "entry_rank <= 10", and with the CROSS APPLY the "FIRST" simply needs to be changed to "TOP 10".

Answer: Use a WHERE clause to pick the partition, then FIRST and ORDER BY to select the first row.

Once again, it's clear from reading the code what the question was:

SELECT FIRST *
FROM t
WHERE t.partition_id = 10
ORDER BY t.entry_id;
partition_id,entry_id,data
10,1,'aaa'

What now?

Now comes the hard part: Combining those two result sets to get the answer to the original question.

Let's try this:

SELECT first_t.*
FROM ( SELECT DISTINCT
t.partition_id
FROM t
ORDER BY t.partition_id ) AS unique_partition
INNER JOIN
( SELECT FIRST *
FROM t
WHERE t.partition_id = unique_partition.partition_id
ORDER BY t.entry_id ) AS first_t
ORDER BY first_t.partition_id;

You knew that wouldn't work, didn't you? INNER JOIN needs an ON clause:

Could not execute statement.
There is no way to join 'unique_partition' to 'first_t'
SQLCODE=-146, ODBC 3 State="42000"
Line 1, column 1

Let's try the old-fashioned "comma join operator", it doesn't need an ON clause:

SELECT first_t.*
FROM ( SELECT DISTINCT
t.partition_id
FROM t
ORDER BY t.partition_id ) AS unique_partition,
( SELECT FIRST *
FROM t
WHERE t.partition_id = unique_partition.partition_id
ORDER BY t.entry_id ) AS first_t
ORDER BY first_t.partition_id;

Different problem this time; you can't refer to a column in the left-hand side of a join in the right-hand side:

One last try; let's move the WHERE clause to an ON clause so INNER JOIN will work:

SELECT first_t.*
FROM ( SELECT DISTINCT
t.partition_id
FROM t
ORDER BY t.partition_id ) AS unique_partition
INNER JOIN
( SELECT FIRST *
FROM t
ORDER BY t.entry_id ) AS first_t
ON first_t.partition_id = unique_partition.partition_id
ORDER BY first_t.partition_id;

Yeah, it "works" all right, but it only returns one row instead of three; the predicate really needs to be inside the inner SELECT, not the outer one:

partition_id,entry_id,data
10,1,'aaa'

You've seen that before, haven't you?

Let's be honest, you've seen those error messages and wrong results before, haven't you? Like when you were trying to solve a problem that should have been easy (like this one) but you had to fiddle around with complex subqueries or temporary tables or even (gasp!) cursor fetch loops, and you ended up with a solution that looked like it was designed for a whole different problem.

It turns out there is a join operator that makes both of those error messages go away:

it doesn't need an ON clause, and

it does allow references from the right-hand side back to the left-hand side.

It's called CROSS APPLY...

...and it looks like this:

SELECT first_t.*
FROM ( SELECT DISTINCT
t.partition_id
FROM t
ORDER BY t.partition_id ) AS unique_partition
CROSS APPLY
( SELECT FIRST *
FROM t
WHERE t.partition_id = unique_partition.partition_id
ORDER BY t.entry_id ) AS first_t
ORDER BY first_t.partition_id;
partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'

CROSS APPLY is like the comma-join and CROSS JOIN operators in that it doesn't need (and cannot have) an ON clause. It is different from other join operators in that predicates on the right-hand side can refer to columns on the left-hand side.

So, like comma-join and CROSS JOIN, the CROSS APPLY joins every single row on the left-hand side with every single row on the right-hand side...

but...

...exactly what constitutes "every single row on the right-hand side" depends on each row on the left-hand side.

In this case, for each row on the left-hand side, the set consisting of "every single row on the right-hand side" is boiled down to one single row, the first row with matching partition_id.

As SQL Anywhere performance tuning knobs go, the PCTFREE setting is one of most obscure and least used. It's been around forever, but nobody ever uses it, probably because nobody can figure out how to use it properly.

PCTFREE clause - Specifies the percentage of free space you want to reserve for each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation.

The value percent-free-space is an integer between 0 and 100. The former value specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, or is later dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB (and up) page size). The value for PCTFREE is stored in the ISYSTAB system table.

Just what should PCTFREE be set to? Is 50% good? Is 40% better, or is 60%?

It doesn't help that PCTFREE is a percentage but the default is expressed in bytes... I think you can convert, but who cares? Is 200 bytes good? Or 100? Or 400? ...the answer is the same, "I don't know."

The problem is this: When designing a new cargo ship / warehouse / database, folks generally know how many tonnes / cubic feet / bytes it should store; i.e., the capacity, not the free space.

Taken down to the database page level, folks know (or can figure out) how big the average row is when it's inserted, and how big it will be after it's been updated. What they don't know is how to convert that to an efficient amount of free space to leave after inserting.

Why should we care?

It is a fairly common practice to insert empty rows and then update them later when actual data become available... easy is good.

Once inserted, a row can never be moved to a different page, so when a page becomes full of empty rows the first UPDATE that expands a column will split that row... splits are bad, hence PCTFREE.

Here's a thought...

...instead of (or in addition to) PCTFREE why not give the users a knob called MINROWSIZE which they can calculate as expected average row size after the row has been updated, and is to be used by SQL Anywhere as the minimum amount of table data space to be reserved for each row when it is inserted?

In this case, MINROWSIZE 25 means reserve 4 bytes for the integer and about 20 bytes for the string even though the INSERT is providing an empty row:

Currently, when a new row is inserted the PCTFREE amount is compared to the current free space in the page when deciding if a new row will fit. The size of each existing row and whether or not it is expected to grow in size is not taken into account, just the total space used and the PCTFREE amount, so depending of the current state of affairs inside the page the ideal PCTFREE could be larger or smaller than the actual value.

With MINROWSIZE, SQL Anywhere would take into account existing rows that have still not used up their MINROWSIZE allocation, as well as the MINROWSIZE allocation for the new row, when making the decision.

With some effort, the user could provide a MINROWSIZE value that is meaningful and accurate... as opposed to picking the number for Lotto PCTFREE.

Even worse?

FOXHOUND1 is the all-important "Foxhound drive-and-path" used by all the Foxhound shortcuts... it's Foxhound's version of %SQLANY12%, if you lose it you'll probably want to set it up again.

FOXHOUND1BIN is the esoteric variable you can set to force Foxhound to start up using the 32-bit version of SQL Anywhere if you have both versions installed... "esoteric" because you almost never need it, Foxhound runs just fine using the 64-bit version. But, if you need it, here's how to use FOXHOUND1BIN (no, you don't need it if only the 32-bit version of SQL Anywhere is installed, Foxhound will use that one automatically).

FOXHOUND1UPGRADE refers to the variable that controls how much data the "post-setup" process will copy from your old Foxhound database when you are upgrading to a new version. The environment variable version of FOXHOUND1UPGRADE sets the initial value, but it defaults to "ALL" and is easy to change during the installation process, so it is rarely used.... the environment variable is rarely used, that is, but the underlying value is very important.

Ooops...

...wait a minute, that wasn't a FAQ, that was three paragraphs about a FAQ. To experience the full measure of dullness you have to follow the links, this article is just a travelogue.

There's a lot of logic behind upgrading... it's a big deal if you have a big database, especially if you don't need all the data any more. You can let the purge process slowly delete it over time, or you can let the upgrade process quickly delete-it-by-not-copying it, so "upgrading" to the same version of Foxhound suddenly makes sense: How do I shrink the size of the Foxhound database?

The FAQ alias feature is most useful in the alphanumeric Messages section where messages containing multiple phrases and numeric codes appear repeatedly in the list. Quite a few messages have two or three entries pointing to the same explanation; this one's the winner with 4 appearances in the sorted list:

Some folks like the context-sensitive Help frame on the right side of the Foxhound display, some folks hate it, but one thing everyone hated was the "Hide Help / Show Help" buttons when they didn't work.

Now it's a matter of setting up a text file with all the connection strings, then clicking on one button to start them all, another to stop them all, with nothing stopping you from starting and stopping them individually like before.

The new Force Default Settings On All Targets button on the Monitor Options page lets you "push" the default settings to all the targets, all at once. Previously, you could only "pull" the settings to each target one at a time with the Restore Default Settings button.

Except for LockRowID, all the new columns contain values that Foxhound used to calculate only when the data was displayed on the Monitor and History pages; now these values are calculated once and stored permanently.

The default is FOXHOUND1UPGRADE = ALL to copy all the data from your old Foxhound database to the new one.

If you choose FOXHOUND1UPGRADE = OPTIONS during the installation process, Foxhound will copy everything except the Monitor samples. When you start Foxhound again, all the old sample data will be gone but sampling will start again right away (assuming it was running before you installed the new version).

If you want to save the Monitor samples recorded since a particular date, specify FOXHOUND1UPGRADE = yyyymmmdd.

To save the samples recorded during the past n days, use FOXHOUND1UPGRADE = n.

What's really cool about the new upgrade process is you can use it to purge, shrink and reorganize the Foxhound database: just reinstall the same version of Foxhound with FOXHOUND1UPGRADE = yyyymmmdd or FOXHOUND1UPGRADE = n.

You can force Foxhound to use the 32-bit version of SQL Anywhere by setting the FOXHOUND1BIN environment variable to Bin32... sometimes, it's just easier to do that than to try to figure out what's going wrong with ODBC and a legacy target database.

Bad news: Foxhound requires SQL Anywhere 12.0.1.3298 or later to run. And it still only runs on Windows. But none of that affects your target database: as long as Foxhound can establish an ODBC connection to your database, it can monitor the performance and display the schema.

Texting!

Recalculating!

Texting!

Meet Mayhem, prerecorded...

At some point, every piece of software has to "go out the door", into the hands of unsuspecting eager customers.

The question is, how do you know when that time has come?

When all the bugs are gone!

Yeah, sure, and if that was the case there wouldn't be any new software, ever, because the bugs are never all gone.

Fortunately, there are three simple criteria to be met before a software product is ready to ship:

1. All the new bugs are going into the "Future Fix" category.

Once upon a time, you were determined to fix each and every flaw that turned up, no matter how minor.

Now, it's not just a matter of "When will this bug get fixed?" (Not Now) but "Will this bug get fixed?"... that's when a Future Fix bug gets flagged with "Wait For Reproducible" which is the same as "No, Never Mind".

2. You're writing more FAQs than fixes.

Sometimes it's easier to cover an electrical outlet than to document why it's not a good idea to stick a fork into it... until you consider the fact that docs don't need to be tested but if you change the code you have to go back and run all . . . those . . . endless . . . regression tests again.

Plus, it's really easy to say "It's not a bug, it's a FAQ!" with a straight face, a lot easier than "It's not a bug, it's a feature!" even though the two statements are not that different.