Monday, July 30, 2012

Latest SQL Anywhere EBFs: 8 Out Of 14... Olympic!

The three asterisks "***" show which Express Bug Fixes (EBFs) have been released since the previous version of this page.

  • Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

  • Just because an older version or different platform isn't "fully supported" any more 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.

Current builds for the active platforms...

HP-UX Itanium   12.0.1.3757 EBF *** 23 Jul 2012 ***
                11.0.1.2753 EBF     13 Feb 2012

IBM AIX         12.0.1.3757 EBF *** 23 Jul 2012 ***
                11.0.1.2753 EBF     13 Feb 2012

Linux x86/x64   12.0.1.3759 EBF *** 23 Jul 2012 ***
                11.0.1.2843 EBF *** 27 Jul 2012 ***

Mac OS          12.0.1.3744 EBF     16 Jul 2012
                11.0.1.2449 EBF     29 Jun 2010

Solaris SPARC   12.0.1.3757 EBF *** 23 Jul 2012 ***
                11.0.1.2811 EBF     14 May 2012

Solaris x64     12.0.1.3757 EBF *** 23 Jul 2012 ***
                11.0.1.2753 EBF     13 Feb 2012

Windows x86/x64 12.0.1.3757 EBF *** 25 Jul 2012 ***
                11.0.1.2825 EBF *** 20 Jul 2012 ***

Other Stuff...

Older EBFs

Free support! Q&A forum
...or, call Tech Support

SQL Anywhere home page 

Buy SQL Anywhere 

Download the Developer Edition... 
  [12.0.1] [11.0.1]

Download the Educational Edition 
Download the Web Edition 

Supported Platforms...
  [SQL Anywhere] [Linux] [OnDemand]

Recommended...
  ODBC Drivers for MobiLink

Dilbert.com


Friday, July 27, 2012

Example: TOP 1, WHERE and ORDER BY

Question: How do I turn a user's guess at a value in a ordered sequence, into a value that actually exists?

For example, one of the new features in Foxhound Version 2 is the ability to "go to" a particular sample in the vast number of performance samples recorded in the database. The "go to" scroll value can be a datetime or a sample number, and it can be accurate or just a wild guess.

This question deals with a wild guess at the sample number. Here's a simplified example:

actual sample numbers in the database, in the same order that samples are displayed 
|
|    "go to" guess at the sample number
|     |
V     V

90
80
70
      65
60
50
Since sample number 65 doesn't exist, Foxhound scrolls to sample 70.

Why 70 and not 60? Because the action of "go to" is to scroll the chosen sample to the top of the display, and when the display scrolls to 70, samples 60 and 50 are visible below it. If Foxhound scrolled to sample 60 then 70 wouldn't be visible.

That's how the decision was made. There is no truth to the rumors about dice and coin tosses :)

Answer: The TOP 1, WHERE and ORDER BY clauses of the SELECT statement make this kind of query easy to code, and quite often very efficient as well.

Here's the actual code:
SELECT TOP 1 rroad_sample_set.sample_set_number
  INTO @history_goto_sample_set_number
  FROM rroad_sample_set
 WHERE rroad_sample_set.sampling_id       = @sampling_id
   AND rroad_sample_set.sample_set_number >= @candidate_history_goto_sample_set_number
 ORDER BY rroad_sample_set.sample_set_number ASC;
Here's how the TOP 1, WHERE and ORDER BY clauses work together to answer the question:
  • The WHERE ... AND predicate on line 5 limits the result set to sample numbers 70, 80 and 90... all of them are >= 65.

  • The ORDER BY ... ASC on line 6 sorts the result set so that 70 comes first.

  • The TOP 1 clause picks 70 as the final answer.

  • The input to this query is the local variable @candidate_history_goto_sample_set_number = 65, and the output is @history_goto_sample_set_number = 70.

  • The other predicate (the WHERE ... = @sampling_id on line 4) deals with the fact that Foxhound samples are partitioned by the target database being monitored.

It's perfect! What could possible go wrong?

Getting the ORDER BY wrong is a common mistake, but it's easy to fix: "ooops, that should be ASC... I think... let's try it and see."

Getting the WHERE predicate wrong is another common mistake: "should that be >= or just > ? ...or should it be < ?" ...not so easy to "just try it and see", better to think about it a bit.

In these cases "common mistake" is not an exaggeration, "almost always" is closer to the truth... if you're not an Alpha.

But, it's a valuable technique, useful in a whole variety of situations, and they all have TOP 1, WHERE and ORDER BY... you just have to pick the right WHERE operator and the right ORDER BY direction.

And if you start by scribbling down an example with data values, you can get the code right first time.

Sometimes :)


Wednesday, July 25, 2012

Webcast: SQL Anywhere On-Demand Edition (a.k.a. Fuji)

One week from today...

One hour, on Wednesday, August 1, 2012 - register here.




Monday, July 23, 2012

IS_NUMERIC()

Question: How do I check if a string is numeric?

Answer: Call ISNUMERIC().

Question: No, that checks to see if the string is a number, it accepts all sorts of crap like '-1.5E+10'. I want to check if a string is numeric... just digits.

Answer: You mean, will the string work as an UNSIGNED BIGINT?

Question: Yeah, that'll work... actually, that's exactly what I want.

Answer: Then just call CAST() and if that doesn't throw an EXCEPTION then you're good to go.

Question: I don't want to accept the empty string or all spaces or NULL.

Answer: So you want it to work just like ISNUMERIC() but without all the crap?

Question: Yeah.

Answer: OK, here you go...

CREATE FUNCTION IS_NUMERIC
   ( IN @string  LONG VARCHAR )
   RETURNS INTEGER 
BEGIN

DECLARE @unsigned_bigint  UNSIGNED BIGINT;

IF TRIM ( COALESCE ( @string, '' ) ) = '' THEN
   RETURN 0;
END IF;

SET @unsigned_bigint = CAST ( @string AS UNSIGNED BIGINT );

RETURN 1;

EXCEPTION WHEN OTHERS THEN
   RETURN 0;

END;
Here are some test cases:
SELECT CAST ( NULL AS LONG VARCHAR ) as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '' as x,     ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT ' ' as x,    ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '    9    ' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '-1' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '-1.5E+10' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '00018446744073709551615' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1' as x,   ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1.0' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '1.1' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '18446744073709551615' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '18446744073709551616' as x, ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '9223372036854775807' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT '9223372036854775808' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
UNION
SELECT 'abc' as x,  ISNUMERIC ( x ), IS_NUMERIC ( x )
ORDER BY 1;

                         ISNUMERIC(x)
                         |   IS_NUMERIC(x)
                         |   |
x                        V   V
----------------------- --- --- 
(NULL)                   0   0
                         0   0
                         0   0
    9                    1   1
-1                       1   0
-1.5E+10                 1   0
00018446744073709551615  1   1
1                        1   1
1.0                      1   0
1.1                      1   0
18446744073709551615     1   1
18446744073709551616     1   0
9223372036854775807      1   1
9223372036854775808      1   1
abc                      0   0


Friday, July 20, 2012

The fRiDaY File - What if?

xkcd has a new feature: "What if? Answering your hypothetical questions with physics, every Tuesday."

So far three entries have been published, with the first one being the best by far:

What would happen if you tried to hit a baseball pitched at 90% the speed of light?



Latest SQL Anywhere EBF: 12.0.1.3744 for Mac OS

The three asterisks "***" show which Express Bug Fixes (EBFs) have been released since the previous version of this page.

  • Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

  • Just because an older version or different platform isn't "fully supported" any more 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.

Current builds for the active platforms...

HP-UX Itanium   12.0.1.3740 EBF     15 Jun 2012
                11.0.1.2753 EBF     13 Feb 2012

IBM AIX         12.0.1.3740 EBF     15 Jun 2012
                11.0.1.2753 EBF     13 Feb 2012

Linux x86/x64   12.0.1.3750 EBF     13 Jul 2012
                11.0.1.2811 EBF     17 May 2012

Mac OS          12.0.1.3744 EBF *** 16 Jul 2012 ***
                11.0.1.2449 EBF     29 Jun 2010

Solaris SPARC   12.0.1.3740 EBF     15 Jun 2012
                11.0.1.2811 EBF     14 May 2012

Solaris x64     12.0.1.3740 EBF     15 Jun 2012
                11.0.1.2753 EBF     13 Feb 2012

Windows x86/x64 12.0.1.3750 EBF     03 Jul 2012
                11.0.1.2808 EBF     22 May 2012

Other Stuff...

Older EBFs

Free support! Q&A forum
...or, call Tech Support

SQL Anywhere home page 

Buy SQL Anywhere 

Download the Developer Edition... 
  [12.0.1] [11.0.1]

Download the Educational Edition 
Download the Web Edition 

Supported Platforms...
  [SQL Anywhere] [Linux] [OnDemand]

Recommended...
  ODBC Drivers for MobiLink

Wednesday, July 18, 2012

Product Suggestion: WITH AUTO NAME ON MISSING

Automated schema upgrades are often a requirement for embedded databases, requiring miles and miles of custom code... either that, or you don't allow any schema changes after Version 1.

Here's what makes embedded databases different from central databases:

  • Hundreds or thousands of copies of the database exist, rather than just a few (or one).

  • Embedded databases don't have database administrators... they have doctors, lawyers, truckers, accountants, salesfolk, but no DBAs.

  • Embedded databases often don't even have an IT department... not when the database is buried in a trench or hurtling down the highway.
So, when the time comes to
  • add a column,

  • or a table,

  • or an index,

  • or split a table into two,

  • or combine two tables into one,

  • or . . . re . . . design . . . every . . . single . . . thing,
what's a developer to do?

Say no?


Well, that sometimes works, and if synchronization is involved that's always the first choice.

Seriously...


Let's not talk about synchronization, just a regular embedded database: maybe one user, maybe more than one, but no MobiLink.

There are two fundamentally different approaches for an automated schema upgrade:
  1. Upgrade the old database in-place using a variety of custom ALTER statements and other code to deal with the differences, versus

  2. Deliver a new database and copy the data with custom code to deal with the differences.
Both approaches are difficult to implement and very difficult to maintain as the variety of old versions in the field multiplies and grows... with success (more sales, more databases, more upgrades, more old versions) making the situation worse.

Frankly, I don't know how Intuit does it, and I bow down before their vast superiority... I am not worthy!
It doesn't matter which approach you take, or even if you use a combination of both approaches, eventually you're going to want to use INSERT SELECT to copy data from one location to another.

...and the INSERT WITH AUTO NAME SELECT feature makes it easy(ier) to deal with schema differences between the INSERT thing and the SELECT thing.

Let's call it a "Featurette"


Not having to code long lists of column names makes INSERT SELECT easier to write and maintain: SELECT * helps, and so does INSERT ( without, the, long, list, of, column, names ).

Right now, DBAs in the audience are cringing in horror at the mere suggestion of SELECT *... that's OK, the world needs SELECT * more than it needs DBAs :)

In other words, INSERT new_table SELECT * FROM old_table is a wonderful thing.

Here'e the problem, though: if the new table has even one single additional column you can no longer use INSERT new_table SELECT * FROM old_table.
CREATE TABLE old_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT old_table VALUES ( 1, 2, 3 );
COMMIT;

CREATE TABLE new_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   x     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT new_table SELECT * FROM old_table;

Could not execute statement.
Wrong number of values for INSERT
SQLCODE=-207, ODBC 3 State="21S01"
Line 15, column 1
What you have to do, even if you're OK with the DEFAULT value being assigned to the new column, is list all the column names being copied from the old table:
INSERT new_table ( pkey, a, b ) SELECT * FROM old_table;
Sure, that's easy when there are only three columns, but when there are 50 columns increasing to 51, and it's happening with dozens of tables, and code has to deal with several different old versions each having its own set of changes, it's not just hard, it's boring, and that means it's error prone.

Let's face it: database upgrades, file conversions, whatever they've been called over the years, have never been assigned much importance by management, never been given much of the budget or much time in the schedule, or... dare it be said... never been assigned to the Alpha Engineers.

Alphas can juggle dozens of differences in their head at one time...

...the rest of us need help


The WITH AUTO NAME clause lets you code INSERT new_table SELECT * FROM old_table without the column name list:
CREATE TABLE old_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT old_table VALUES ( 1, 2, 3 );
COMMIT;

CREATE TABLE new_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   x     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT new_table WITH AUTO NAME SELECT * FROM old_table;

SELECT * FROM new_table ORDER BY pkey;

pkey,a,x,b
1,2,0,3
So far, so good, but what if you are dropping a column? The WITH AUTO NAME clause doesn't help:
CREATE TABLE old_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT old_table VALUES ( 1, 2, 3 );
COMMIT;

CREATE TABLE new_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT new_table WITH AUTO NAME SELECT * FROM old_table;

Could not execute statement.
Column 'a' not found
SQLCODE=-143, ODBC 3 State="42S22"
Line 13, column 1
OK, you could decide to never drop a column, but what if it's a big fat blob that's moving to another table (and separate code deals with that process)?

What if you really hate having unused columns hanging around? WITH AUTO NAME won't let you do a rename:
CREATE TABLE old_table (
   pkey  INTEGER NOT NULL PRIMARY KEY,
   a     INTEGER NOT NULL DEFAULT 0,
   b     INTEGER NOT NULL DEFAULT 0 );

INSERT old_table VALUES ( 1, 2, 3 );
COMMIT;

CREATE TABLE new_table (
   pkey    INTEGER NOT NULL PRIMARY KEY,
   unused  INTEGER NOT NULL DEFAULT 0,
   b       INTEGER NOT NULL DEFAULT 0 );

INSERT new_table WITH AUTO NAME SELECT * FROM old_table;

Could not execute statement.
Column 'a' not found
SQLCODE=-143, ODBC 3 State="42S22"
Line 14, column 1

Here's the Featurette Suggestion...


Allow the ON MISSING IGNORE clause on WITH AUTO NAME so that any column names in the SELECT * list that don't match column names in the INSERT table are ignored:
INSERT new_table 
   WITH AUTO NAME ON MISSING IGNORE 
   SELECT * FROM old_table;
Optional, of course, and the default would be ON MISSING ERROR just like it is now.

Give that task to one of the Alphas and it'll take less time to implement than this request took to write :)


Monday, July 16, 2012

Latest SQL Anywhere EBF: 12.0.1.3750 for Linux

The three asterisks "***" show what's new since the previous list.

  • Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

  • Note: Just because an older version or different platform isn't "fully supported" any more 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.

Current builds for the active platforms...

HP-UX Itanium     12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

IBM AIX           12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

Linux x86/x64     12.0.1.3750 EBF ***   13 Jul 2012 ***
                  11.0.1.2811 EBF       17 May 2012

Mac OS            12.0.1.3577 EBF       24 Feb 2012
                  11.0.1.2449 EBF       29 Jun 2010

Solaris SPARC     12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2811 EBF       14 May 2012

Solaris x64       12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

Windows x86/x64   12.0.1.3750 EBF       03 Jul 2012
                  11.0.1.2808 EBF       22 May 2012

To see earlier versions you'll have to go to the 
Downloads - EBFs / Maintenance page and work
your way through the menus... sorry!

Other Stuff




Friday, July 13, 2012

The fRiDaY File - "Did I remember to . . . ?"

Did I remember to turn off the stove?

. . . the iron?

. . . lock the front door?

Did I remember to...

. . . take a backup?


. . . check to see that the backups have been running?

. . . test the backup files to make sure they work?

Subversive!

It's questions like those that have branded this blog as...

Enemy of the State!


What other reason could there be, to explain why this blog has been...

Blocked in China!




From now on...

I promise to be good!


Like these folks:



No more subversive questions about backups, and certainly no pictures of this guy:



Please help!

You can help re-educate this blog by not watching this video.

Please, don't watch it now!



Google Groups Q&A

Back in 2009, Version 1.0 of the web-based SQL Anywhere question-and-answer forum was created using the Stack Exchange service as it existed then.

That was then, this is now: If I had to do it again, knowing what Stack Exchange has become, I might use the new Google Groups Q&A instead:


That won't happen, of course,

...because that was then, and this is now, and Version 2.0 of the SQL Anywhere forum exists, and it's just fine, no need to switch platforms.

But...

If I, or you, wanted to create a whole new Q&A website today, here's why the new-and-improved Google Groups might be the way to go:
  • It gives you all the basic goodies you want when building a modern Q&A website,

  • for free,

  • plus it gives you a Google-style interface, with Google search built right in,

  • plus your website automatically runs on the Google cloud, you don't even have to ask:


Plus... no tyrants!

Unlike Google Groups, which is open to everyone, the current Stack Exchange service is built upon two Big Lies (a Big Lie is a falsehood so bold, so ridiculous, so easily disproved, that folks think it must be true because nobody would tell such a whopper, would they?):

Big Lie Number One: "A good Q&A site needs a lot of people to support it."
The truth: What a good Q&A site needs is at least one person to support it. More are better, but most successful online communities depend on a very small number of extremely active users.
Big Lie Number Two: "Stack Exchange is a product for the Internet community at large, not for corporations or individuals."
The truth: The internet community consists entirely of corporations and individuals... what else is there?

Robots? Aliens?

Soldiers?

From these lies springs the rule that you can't have a Stack Exchange Q&A site if you don't agree with the tyrant "We":

"We'll let you know whenever the site reaches the next milestone. A site moves to the "Commitment" phase when its definition is complete and it has a show of support from enough potential users. If a proposal doesn't have followers, the site will not get created."
By the tyrant We's standards, the original sqla.stackexchange.com would not have existed in the first place, nor would SQLAnywhere-forum.sybase.com exist today, simply because because they didn't (and don't) have enough traffic to qualify.

Not to mention that the former was created and operated by an individual, and the latter by a corporation made up entirely of individuals, as opposed to...

...what?

The "community"?

Will Google Groups Q&A crush Stack Exchange?


It's hard to say, Google hasn't been successful [cough] [Google+] at every single thing it tries.

But maybe it doesn't matter... Stack Exchange seems to be morphing into yet another job hunting website:




Wednesday, July 11, 2012

SQL Anywhere Version 1.0

Question: SQL Anywhere 12 is the current version number, what's the next number going to be?

Answer: Fifty bucks says it's going to be SQL Anywhere 16!

13 and 14 are bad luck here and there.

15 is already hopelessly stale as Sybase, er, SAP database version numbers go.

What's left?

12.5?

Are you kidding me? It's gotta be 16.

Gotcha!


You shoulda taken the bet!

Apparently the next version number is 1.0.





Monday, July 9, 2012

Latest SQL Anywhere EBF: 12.0.1.3750 for Windows (plus "Other Stuff")

The three asterisks "***" show what's new since the previous list.

  • Only EBFs for the latest fully-supported versions of SQL Anywhere are shown here: 11.0.1 and 12.0.1.

  • Note: Just because an older version or different platform isn't "fully supported" any more 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.

Current builds for the active platforms...

HP-UX Itanium     12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

IBM AIX           12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

Linux x86/x64     12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2811 EBF       17 May 2012

Mac OS            12.0.1.3577 EBF       24 Feb 2012
                  11.0.1.2449 EBF       29 Jun 2010

Solaris SPARC     12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2811 EBF       14 May 2012

Solaris x64       12.0.1.3740 EBF       15 Jun 2012
                  11.0.1.2753 EBF       13 Feb 2012

Windows x86/x64   12.0.1.3750 EBF ***   03 Jul 2012 ***
                  11.0.1.2808 EBF       22 May 2012

To see earlier versions you'll have to go to the 
Downloads - EBFs / Maintenance page and work
your way through the menus... sorry!

Other Stuff

Contact Sybase Technical Support
Purchase SQL Anywhere via Sybase eShop
Download SQL Anywhere 12.0.1 Developer Edition
Download SQL Anywhere 11.0.1 Developer Edition
Download SQL Anywhere 12 Educational Edition
Download SQL Anywhere Web Edition
SQL Anywhere and MobiLink Supported Platforms and Engineering Support Status
Recommended ODBC Drivers For MobiLink 7.0.2 to 12.0.1


Friday, July 6, 2012

The fRiDaY File - Stack Ranking

Scott Adams comes closest to the truth when Dilbert is the most unbelievable.

Proof lies in the fact the world is filled with managers who believe in stuff like "stack ranking".

What's stack ranking? Well, I didn't know either, until...


The Terrible Management Technique That Cost Microsoft Its Creativity

"... a management system known as "stack ranking" — a program that forces every unit to declare a certain percentage of employees as top performers, good performers, average, and poor — effectively crippled Microsoft’s ability to innovate. "Every current and former Microsoft employee I interviewed — every one — cited stack ranking as the most destructive process inside of Microsoft, something that drove out untold numbers of employees," Eichenwald writes. "If you were on a team of 10 people, you walked in the first day knowing that, no matter how good everyone was, 2 people were going to get a great review, 7 were going to get mediocre reviews, and 1 was going to get a terrible review," says a former software developer. "It leads to employees focusing on competing with each other rather than competing with other companies."
- Forbes, 7/03/2012

But wait, there's more...

Here's a earlier analysis of stack ranking at Microsoft...

The stack rank system creates an environment that is not conducive to high employee moral and team performance. By ranking employees there becomes a sub-optimization in terms of team unity because it singles-out the performance of some at the expense of those who provide the daily support that is necessary for the team effort.
- Human Resource Management, Stephen Gall, November 23, 2005

That article contains numerous quotes from Microsoft's 3.0 (or, How I Learned to Stop Worrying and Love The Curve):

AAAHHH!@!! I could not agree more:

"Why bother? I'll get the same review no matter what I do."

I killed myself one year. I mean the whole year; just one giant death-march Ho-Chi-Minh nightmare. Shipped. Accolades from customers.

...drumroll...

3.5; because "You went dark". Yeah, no s*** Sherlock, I was working not preening

And I know it wasn't that I "went dark"; I really felt for my manager at the time, because he just wasn't a forceful enough person to push for a better ranking. He just flat fell down against a much more experienced manager in the peer team.

Anyway I left. The phenomenon you describe is known in psychology as "learned helplessness", and I did not want to become one of those depressed rats...now I'm a happy rat employed where there's no curve ranking.
- comment on Microsoft's 3.0 (or, How I Learned to Stop Worrying and Love The Curve), Monday, June 20, 2005

It's always good to go to the original sources where you can find stuff like this:

My first rating: 4.0. (Shipped a product) My second: 3.5 (Shipped, but didn't do anything extraordinary beyond what I did for the 4.0) My third: 3.0 ("I really fought for you to get a 3.5, and I personally appreciate your efforts...) After that point, what was the point? "You did well - but we have this number box, and you didn't fit in. Sorry. Enjoy the free soda, though."
- comment on Microsoft's 3.0 (or, How I Learned to Stop Worrying and Love The Curve), Sunday, June 19, 2005

You can't make this stuff up, it has to be real...
Dilbert.com


JOIN to a text file without a proxy table

Question: I want to check a text file against a table... can I do that without messing around with CREATE SERVER statements and proxy tables and ODBC drivers?

Answer: Well, sure, you could write a loop in Java and use JDBC instead... oh, you don't want to do that either?

I guess unearthing an ORM solution is out of the question, too... you just want to code a SELECT and be done with it, right?

Acceptable Answer: Use OpenString() in the FROM clause.

But first, here's a table and a text file.

The table is used to generate the SQL Anywhere Technical Documents web page, and the text file is sent to Google for the corresponding Custom Search Engine:

CREATE TABLE DBA.article ( -- 526 rows, 284k total = 248k table + 28k ext + 8k index, 553 bytes per row
   artificial_id    /* PK        */ BIGINT NOT NULL DEFAULT autoincrement,
   article_number                   DECIMAL ( 30, 6 ) NOT NULL DEFAULT 0,
   eyecatcher                       LONG VARCHAR NOT NULL DEFAULT '',
   display_date                     LONG VARCHAR NOT NULL DEFAULT '',
   order_by_date                    DATE NOT NULL DEFAULT '1901-01-01',
   versions                         LONG VARCHAR NOT NULL DEFAULT '',
   url                              LONG VARCHAR NOT NULL DEFAULT '',
   title                            LONG VARCHAR NOT NULL DEFAULT '',
   description                      LONG VARCHAR NOT NULL DEFAULT '',
   added_on_date                    DATE NOT NULL DEFAULT '1901-01-01',
   authors                          LONG VARCHAR INLINE 256 PREFIX 8 NOT NULL DEFAULT '',
   updated_at                       TIMESTAMP NOT NULL DEFAULT timestamp,
   CONSTRAINT ASA81 PRIMARY KEY ( -- 8k
      artificial_id )
 );

-- Text file...

http://www.sybase.com/detail?id=1098527
http://www.sybase.com/detail?id=1067224
http://www.sybase.com/detail?id=1098128
http://www.sybase.com/files/White_Papers/wp-ias-MobiLink12Performance.pdf
http://www.sybase.com/detail?id=1096289
http://www.sybase.com/detail?id=1095051
http://www.sybase.com/detail?id=1096331
http://www.sybase.com/detail?id=1095775
http://www.sybase.com/detail?id=1095123
http://www.sybase.com/detail?id=1094921
http://www.sybase.com/detail?id=1094907
http://download.sybase.com/presentation/TW2011/DATSQL16.pdf
http://download.sybase.com/presentation/TW2011/DATSQL12_SQLAnywhereOData.pdf
http://download.sybase.com/presentation/TW2011/DATSQL11.pdf
...
The question now becomes "How do I join article.url with the data in the text file?"

Voila! A single SELECT!

That's all there is to it...
SELECT article.artificial_id,
       article.url, 
       text_file.url 
  FROM article INNER JOIN OpenString ( FILE 'temp_include_sites_in_bulk.txt' )
                                WITH ( url LONG VARCHAR )
                          AS text_file
       ON text_file.url = article.url
 ORDER BY article.artificial_id;

1    http://www.sybase.com/detail?id=1095775  http://www.sybase.com/detail?id=1095775
2    http://www.sybase.com/detail?id=1095123  http://www.sybase.com/detail?id=1095123
3    http://www.sybase.com/detail?id=1094491  http://www.sybase.com/detail?id=1094491
...
550  http://download.sybase.com/presentat...  http://download.sybase.com/presentat...
551  http://download.sybase.com/presentat...  http://download.sybase.com/presentat...
552  http://download.sybase.com/presentat...  http://download.sybase.com/presentat... 
The OpenString() clause on lines 4 to 6 works like this: the FILE clause points to the text file, the WITH clause specifies the layout of the text file (one single string column), and the AS clause gives the OpenString() a table name to be used in the rest of the SELECT.

After that, it's just an ordinary SELECT: the ON clause on line 7 refers to the url column in the two tables (one real table, article, and one not-so-real table, text_file).

The SELECT list, the ORDER BY clause, all business as usual, and the result set (lines 10 to 16) looks like it should.



The REAL question was this: How do I make sure that all the urls in the table also exist in the text file, and vice versa?

Stuff happens when you have the same data stored in two different places: Maybe the table's more up to date than the text file, or maybe the the most recent changes got rolled back, or maybe it got restored from a backup.

Or maybe we're looking at the wrong copy of the text file... a girl can't be too careful these days.

The solution: Change the INNER JOIN to a FULL OUTER JOIN.

Not a LEFT OUTER JOIN, that only tells you about data that's in the table but missing from the text file... a FULL OUTER JOIN tells you about the vice versa too:
SELECT article.artificial_id,
       article.url, 
       text_file.url 
  FROM article FULL OUTER JOIN OpenString ( FILE 'temp_include_sites_in_bulk.txt' )
                                     WITH ( url LONG VARCHAR )
                               AS text_file
       ON text_file.url = article.url
 WHERE text_file.url IS NULL
    OR article.url   IS NULL
 ORDER BY article.artificial_id;

(NULL)  (NULL)         http://www.sybase.com/detail?id=109166
327     not available  (NULL)
337     not available  (NULL)
338     not available  (NULL)
339     not available  (NULL)
344     not available  (NULL)
349     not available  (NULL)
362     too large      (NULL)
366     too large      (NULL)
368     too large      (NULL)
379     empty          (NULL)
502     not available  (NULL)
505     not available  (NULL)
506     not available  (NULL)
507     not available  (NULL)
510     not available  (NULL)
511     not available  (NULL)
530     not available  (NULL)
531     not available  (NULL)
532     not available  (NULL)
533     not available  (NULL)
Line 12 shows an url that exists in the file but not the table: that's a real problem, and it makes writing this query a worthwhile endeavour.

The other rows (lines 13 to 32) show entries in the table that don't have urls at all, so they're not going to show up in the text file sent to Google: that's not a problem.

Ooops, almost forgot... the WHERE clause on lines 8 and 9 cuts the output down to just the Possibly Bad Stuff: if the query returns no rows there's nothing to look at, let alone worry about.

Where did "OpenString" come from?

Here's the question as it was asked on sqlanywhere.forum.com:

Lo these many years, based on nothing more than the prefix "Open", I have assumed that the OpenString function clause was based on some pre-existing feature in common use by some (unknown to me) portion of the computing community.

However, two possibilities now come to mind: first, that the "Open" in OpenString has nothing to do with any utopian philosophy but that it might as well have been called "UnloadString"; and second, that OpenString might be an invention of Watcom, er, iAnywhere Solutions, er, Sybase, er, SAP.

Is it possible?
Here's the answer from Glenn Paulley:

As far as I know that is true - OPENSTRING is Anil's invention, I haven't seen anything like it with any other product.
That being Anil Goel... and here's some more information from John Smirnios:

It was an excellent evolution of LOAD TABLE and, not surprisingly, they both share the same code. Many people needed the ability to massage the data coming from a LOAD TABLE so they would insert the data into a temporary table then massage the data in the temp table. That was wasteful and cumbersome. OpenString allowed it to happen in one step. The rewrite of the execution engine (many versions ago) made it much more feasible to do. LOAD TABLE is now very little other than an INSERT (with special properties and special logging) from OpenString.
If the Wikipedia entry for "innovation" had a photo (it doesn't), it would be Anil's.

If I was Anil's boss (I'm not)... or John Smirnios, I'm not his boss either... but if I was, this would not happen...

Dilbert.com


Wednesday, July 4, 2012

Example: HTTP LogFormat (LF) protocol option

Sometimes, when you're testing a web service stored procedure that returns an HTML page to the browser, you run into strange symptoms.

For example: Internet Explorer 8 displays an empty page, and when
you click on "View Page Source" all you see is <HTML></HTML>.
That's when you really want to see what's going on at the boundary between the SQL Anywhere and the browser; i.e., you want to look at the actual HTTP traffic.

SQL Anywhere lets you record the HTTP traffic in a text file, and it gives you four options to set it up:
The Help's pretty good at explaining what those options mean and what they look like, but not how to code them inside an actual dbsrv12.exe -xs option string.

That's what this blog post is for...

...to show an actual working example, complete with the "double quotes" around the LogFormat (lf) option the Help doesn't talk about:
"%SQLANY12%\%BIN%\dbspawn.exe"^
  -f^
  "%SQLANY12%\%BIN%\dbsrv12.exe"^
  -c 25p^
  -ch 50p^
  -cr-^
  -gk all^
  -gn 120^
  -gna 0^
  -n foxhound2^
  -o foxhound2_debug.txt^
  -oe foxhound2_debug_startup.txt^
  -on 1M^
  -qn^
  -sb 0^
  -x tcpip^
  -xd^
  -xs http(port=80;maxsize=0;to=600;kto=600;lopt=ALL,HEADERS;lf="@T - @I - @U - @R - @L - @W - @E";lsize=1M;log=foxhound2_http_log.txt)^
  foxhound2.db^
  -n f
Here's a snippet from foxhound2_http_log.txt:
07/01 15:46:47.986 - 127.0.0.1 - /foxhound - 200 OK - 594 - 0.756 - 
--> Keep-Alive: 115 
--> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.22) Gecko/20110902 Firefox/3.6.22 (.NET CLR 3.5.30729) 
--> Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7 
--> @HttpMethod: GET 
--> Host: localhost 
--> Connection: keep-alive 
--> Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 
--> Accept-Language: en-us,en;q=0.5 
--> @HttpVersion: HTTP/1.1 
--> Accept-Encoding: gzip,deflate 
--> @HttpURI: /foxhound 
<-- Date: Sun, 01 Jul 2012 19:46:47 GMT 
<-- Transfer-Encoding: chunked 
<-- Connection: Keep-Alive 
<-- Expires: Sun, 01 Jul 2012 19:46:47 GMT 
<-- Content-Type: text/html 
<-- Server: SQLAnywhere/12.0.1.3298 
07/01 15:46:48.062 - 127.0.0.1 - /rroad_help_frame?f=foxhound_menu - 200 OK - 18543 - 0.048 - 
--> Keep-Alive: 115 
--> @HttpQueryString: f=foxhound_menu 
--> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.22) Gecko/20110902 Firefox/3.6.22 (.NET CLR 3.5.30729) 
--> Accept-Charset: ISO-8859-1,utf-8;q=0.7,*;q=0.7 
--> @HttpMethod: GET 
--> Host: localhost 
--> Connection: keep-alive 
--> Referer: http://localhost/foxhound 
--> Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8 
--> Accept-Language: en-us,en;q=0.5 
--> @HttpVersion: HTTP/1.1 
--> Accept-Encoding: gzip,deflate 
--> @HttpURI: /rroad_help_frame?f=foxhound_menu 
<-- Date: Sun, 01 Jul 2012 19:46:48 GMT 
<-- Transfer-Encoding: chunked 
<-- Connection: Keep-Alive 
<-- Expires: Sun, 01 Jul 2012 19:46:48 GMT 
<-- Content-Type: text/html 
<-- Server: SQLAnywhere/12.0.1.3298 
...
In my case (not shown here) it was a METHOD="POST" request that was mistakenly sent via METHOD="GET", and the resulting giant-fat-bloated-URL was too long for Internet Explorer 8.

Not too long for Chrome or Firefox or IE9, though, which was driving me craaaaazy :)...

...without the HTTP trace I never would have figured it out; I was this close to saying "Foxhound does not support IE8."


Monday, July 2, 2012

Quick! How do I truncate the transaction log?

Question: How do I truncate the transaction log without stopping the server or making a backup?

I just want to free up all the disk space it's using...

  • there's no MobiLink or SQL Remote replication involved,

  • I don't care about recovery,

  • I don't even care about the data,

  • but...
and it's a big but... I can't stop the server.

Answer: The dbbackup -xo parameter is what you want.

It tells dbbackup to do something out of the ordinary: just delete and restart the transaction log file, don't bother making a backup.

Think of it like telling the Queen to skip the crown and throne stuff today, we just need you to clean the toilets.


Here's the story in pictures...

Before...


during...

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=inventory;DBN=inventory;UID=dba;PWD=sql"^
  -o c:\temp\dbbackup_log.txt^
  -xo

PAUSE

and after...




Question from the audience: Why use a transaction log at all if you don't care about the recovery?

Answer: Because databases run faster with a transaction log. If there's no transaction log (dbinit -n), every COMMIT turns into a CHECKPOINT.

Question: Why use a database at all if you don't care about the data?

Answer:: Gosh, maybe you should get out more... Databases are used for many purposes, including high-throughput applications where it's far easier to re-read or recreate the data from scratch, or just start over, than to use formal database backup and recovery techniques.

It's like saying "Never use SELECT * !" because people get burned when they use it inappropriately... dbbackup -xo is the same, if you use it when you shouldn't, life will be hard.

Dilbert.com