Friday, September 30, 2011

The fRiDaY File - Quiz Week!

Dilbert.com


Let's play "Gotcha!" - Round Three

Welcome to the third and final round of "Gotcha!", the new self-testing quiz game for developers who work with SQL Anywhere.

Let's get started right away with a warm-up question:

What does SELECT DATEADD ( DAY, 1, '2011-09-30' ) return?


The answer is 2011-10-01!

Here's proof using SQL Anywhere 12.0.1:
SELECT DATEADD ( DAY, 1, '2011-09-30' );

DATEADD(day,1,'2011-09-30')
'2011-10-01 00:00:00.000'

The rules for Gotcha!


You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

No computers!


You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Here we go...


Question One


What does this query return?
SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ), 
                 '2011-09-28 23:59:58' );

Listen to this while working on your answer, then scroll down to see if you got it right...



Here's the answer, it's 2011-09-28 23:59:59, the same as the second date in the nested DATEDIFF call!
SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ), 
                 '2011-09-28 23:59:58' );

DATEADD( second,DATEDIFF(second,'2011-09-28 23:59:58','2011-09-28 23:59:59'), '2011-09-28 23:59:58')
'2011-09-28 23:59:59.000'


Question Two


What does this query return?
SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ), 
                 '7910-12-31 23:59:58' );

It's not a trick question; remember back to what we learned from Round Two of Gotcha!: the true useful range for the TIMESTAMP data type is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.



Once again, the answer is the same as the second date in the nested DATEDIFF call: 7910-12-31 23:59:59!
SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ), 
                 '7910-12-31 23:59:58' );

DATEADD( second,DATEDIFF(second,'7910-12-31 23:59:58','7910-12-31 23:59:59'), '7910-12-31 23:59:58')
'7910-12-31 23:59:59.000'

Congratulations to everyone who has two out of three right so far... only one more to go!


Question Three


What does this query return?
SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' ), 
                 '6910-12-31 23:59:58' );

Remember what Round Two of Gotcha! said: the true useful range for the TIMESTAMP data type is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.



Did you say 7910-12-31 23:59:59? Are you sure? Want a hint?

Let's ask Microsoft Excel 2010:





But once again, this is a question about SQL Anywhere, not Excel...

...so don't rush, take your time.



Here's the answer...

Gotcha!


SELECT DATEADD ( SECOND, 
                 DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' ), 
                 '6910-12-31 23:59:58' );

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Value 31556908801 out of range for destination
SQLCODE=-158, ODBC 3 State="22003"

Congratulations to all you winners out there!

For the rest of you, don't let the fact that DATEDIFF has been enhanced to return BIGINT values...
SELECT DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' );

DATEDIFF(second,'6910-12-31 23:59:58','7910-12-31 23:59:59')
31556908801

...lead you to assume that DATEADD will accept a BIGINT... it won't; you're stuck using INTEGER values.

But don't feel bad, Microsoft SQL Server 2008 doesn't even get that far, it chokes on the DATEDIFF:
1> SELECT DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' )
2> GO
Msg 535, Level 16, State 1, Server ENVY, Line 1
The datediff function resulted in an overflow. The number of dateparts
separating two date/time instances is too large. Try to use datediff with a
less precise datepart.


Thursday, September 29, 2011

The Thursday Quote - Jason Hong


"Given the sharp contrast between traditional methods in HCI [Human-Computer Interaction] and the methods used at Apple, and given the clear success of Apple's products, do HCI methods actually matter?"
Why is Great Design so Hard (Part Two)? by Jason Hong, from BLOG@CACM August 23, 2010

Whatever the reasons are that have kept Apple computers off the desktops of so many millions of people over the past thirty years, it is certainly not the quality of interface design. In fact, if it wasn't for the superior interface design, the insufferable arrogance of its founders and its supporters alone would have been sufficient to drive Apple into oblivion, never mind other factors like a lack of support for outside hardware and software developers... but that's a rant for another day.

No, it's like the folks at Amdahl used to say back in The Day, if Amdahl mainframes weren't better than IBM's then nobody would buy an Amdahl: if Apple's interfaces weren't better than other people's then nobody would buy an Apple... certainly not after 1984...



So it's interesting to read Jason Hong's blog post about how Apple does, and especially doesn't, do interface design... as they say, food for thought.

Here's an excerpt to show the wider context:

Another surprise was how different Apple's design methods were from "standard" best practices in human-computer interaction (HCI). For example, a typical method we teach in HCI is to start with ethnographic field studies to gain deep insights into what people do, how they do it, and why they do it. Another best practice is to do iterative user testing with benchmarks, to ensure that people find products useful and usable.

From what I can tell, Apple doesn't use any of these methods.

Instead, people described three different methods used at Apple. The first is that Apple preferred having subject matter experts who have many years of experience in the field be part of their teams.
. . .
The second is that people at Apple think really long and hard about problems. From that perspective, certain solutions will pop out as being obviously better ways of doing things. Thus, part of Apple's strategy is to guide people toward that way of thinking as well. If you see problems the same way that Apple does, then the structure and organization of an interface will make sense.

The third is that Apple tends to design by principle rather than from data. In classes in human-computer interaction, we emphasize making design decisions based on evidence as much as possible, for example, from past user studies on previous iterations of the interface, or from the ethnographic field studies. In contrast, at Apple, more weight is given to design decisions made from first principles.

So what does this all mean?

I have two closing thoughts here. First, should we just throw away existing HCI methods for design? Given the sharp contrast between traditional methods in HCI and the methods used at Apple, and given the clear success of Apple's products, do HCI methods actually matter?
After having seen so much of what traditional methods have given us, surely perhaps the answer is "No!"


Next week: Srikanth Nadhamuni, Vince Beiser


Wednesday, September 28, 2011

Let's play "Gotcha!" - Round Two

Welcome to the second round of "Gotcha!", the new self-testing quiz game for developers who work with SQL Anywhere.

Let's get started right away with a warm-up question:

What does SELECT DATEDIFF ( DAY, '2011-09-28', '2011-09-29' ) return?


The answer is 1!

Here's proof using SQL Anywhere 12.0.1:
SELECT DATEDIFF ( DAY, '2011-09-28', '2011-09-29' );

DATEDIFF(day,'2011-09-28','2011-09-29')
1

The rules for Gotcha!


You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

No computers!


You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Here we go...


Question One


What does SELECT DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ) return?

Listen to this while working on your answer, then scroll down to see if you got it right...



Here's the answer, it's 1!
SELECT DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' );

DATEDIFF(second,'2011-09-28 23:59:58','2011-09-28 23:59:59')
1


Question Two


What does SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ) return?

It's not a trick question; according to the Help, "the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00."



Once again, the correct answer is 1!
SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' );

DATEDIFF(second,'7910-12-31 23:59:58','7910-12-31 23:59:59')
1

Congratulations to everyone who has two out of three right so far... only one more to go!


Question Three


What does SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' ) return?

Remember what the Help said, "the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00."



Did you say 1? Are you sure? Want a hint?

Let's ask Microsoft Excel 2010:





How about another hint; let's ask Microsoft SQL Server 2008:
1> SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' )
2> GO

 -----------
           1

But once again remember, this is a question about SQL Anywhere, not Excel or SQL Server...

...so don't rush, take your time.



Here's the answer...

Gotcha!


The answer, according to SQL Anywhere 12.0.1, is 176,422,921!

SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' );

DATEDIFF(second,'7910-12-31 23:59:59','7911-01-01 00:00:00')
176422921

For you winners, see you all back here in two days for Round Three of "Gotcha!"

For the rest of you, don't let your SQL code stray outside the true useful range for the TIMESTAMP data type which is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.

After that, it's all one big Y7.911K party!

Dilbert.com


Monday, September 26, 2011

Let's play "Gotcha!" - Round One

Welcome to the first round of "Gotcha!", the new self-testing quiz game for developers who work with SQL Anywhere.

Let's get started right away with a warm-up question:

What does SELECT 1 + 1 return?


The answer of course is 2!

Here's proof using SQL Anywhere 12.0.1:
SELECT 1 + 1;

1+1
2

OK, what are the rules?


You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

No computers!


You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Here we go...


Question One


What does SELECT 255 + 1 return?

Listen to this while working on your answer, then scroll down to see if you got it right...



Here's the answer, it's 256!
SELECT 255 + 1;

255+1
256


Question Two


What does SELECT 32766 + 1 return?



The answer is 32767!
SELECT 32766 + 1;

32766+1
32767

Congratulations to everyone who has two out of three right so far... only one more to go!


Question Three


What does SELECT 32767 + 1 return?



Did you say 32768? Are you sure? Want a hint?

Let's ask Microsoft SQL Server 2008:
1> SELECT 32767 + 1
2> GO

 -----------
       32768

But remember, this is a question about SQL Anywhere, not SQL Server...

...so don't rush, take your time.



Here's the answer...

Gotcha!


SELECT 32767 + 1;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Value 32767 + 1 out of range for destination
SQLCODE=-158, ODBC 3 State="22003"

For you winners, see you all back here in two days for Round Two of "Gotcha!"

For the rest of you, check out the CAST() function in the docs:
SELECT CAST ( 32767 AS BIGINT ) + 1;

cast(32767 as bigint)+1
32768


Friday, September 23, 2011

The fRiDaY File - "Fuji - that's in Japan, right?"

Searching dilbert.com on "Fuji" didn't get any hits, but "Japan" turned up this classic from 1991:

Dilbert.com


Fuji Watch

Question: How much of "Fuji" is already in SQL Anywhere 12.0.1?

Answer: Maybe not much, but more than "nothing" if the docs are any indication... search on "cloud" and you come up with several hits, one of them rather interesting:



It's the word "not" that makes this new SQLCODE interesting: "Server name must not be specified..."

And what's a "cloud node" anyway?


Thursday, September 22, 2011

The Thursday Quote - Brian Dorn


"...the designers we interviewed consistently preferred learning from code examples to more general resources like books."
Reaching Learners Beyond our Hallowed Halls by Brian Dorn, from Communications of the ACM May 2011

Sounds like he's talking about us, doesn't it?

Except that would imply we actually have "more general resources like books" when in fact there are no books on SQL Anywhere, no courses, no certification programs, no nothing... well, not anything newer than what? five years old?

...six? seven?

Other than that, he could be talking about us... he's not, but he could be; here's an excerpt from the full article to show the wider context:

An Example in Graphic and Web Design

Over the course of the last five years, my colleagues and I have conducted a series of studies to better understand how to support the educational needs of a group of informally trained programmers. We have focused our attention on professional graphic and Web designers who actively write code in some aspect of their job.
. . .

Example-Driven Learning.

The main driver for our participants to learn something new about programming derives directly from the needs of the designer's current project. Within that context, the designer actively seeks out examples related to the end goal in a somewhat piecemeal fashion. Ideally, the designer learns as he or she sees examples that make use of new programming features. Unfortunately, this doesn't always happen due to the unavailability of relevant examples, differences between the current goal and that of the example, and the lack of explanation accompanying examples. The explanations found rarely draw out the computer science ideas used, instead favoring specific practical details. Despite the inherent problems and inefficiencies in learning this way, the designers we interviewed consistently preferred learning from code examples to more general resources like books. Of utmost importance, then, is the instructive quality of the examples being used (that is, with good explanations) and how obviously relevant the examples are.
Yup, forget the books, forget the courses, that's how we learn, by looking at examples... the docs are great but if there's anything they need more of it's examples... you can never have too many examples!


Next week: Jason Hong


Wednesday, September 21, 2011

Latest SQL Anywhere EBF: 12.0.1.3436 Windows

The three asterisks "***" indicate what's new since August 26.

Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds...

HP-UX Itanium    11.0.1.2664 EBF                 24 Aug 2011    
                 10.0.1.3777 EBF                 14 Oct 2008

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

IBM AIX          12.0.1 GA Upgrade from 12.0.0   15 Mar 2011
                 11.0.1.2637 EBF                 26 Jul 2011

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

Linux x86/x64    12.0.1.3311 EBF                 01 Apr 2011
                 11.0.1.2645 EBF                 29 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Mac OS           12.0.1.3352 EBF                 26 May 2011
                 11.0.1.2449 EBF                 29 Jun 2010
                 10.0.1.4042 EBF                 01 Oct 2010

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris SPARC    12.0.1 GA Upgrade from 12.0.0   15 Mar 2011
                 11.0.1.2632 EBF                 07 Jul 2011
                 10.0.1.4239 EBF                 17 Jun 2011

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

Windows x86/x64  12.0.1.3436 EBF ***             14 Sep 2011 ***
                 11.0.1.2661 EBF                 22 Aug 2011    
                 10.0.1.4239 EBF                 17 Jun 2011


Monday, September 19, 2011

Smithsonian Anywhere

Every successful software product that's been around for more than a few years has its own "Museum Of Forgotten Features", and SQL Anywhere is no exception.

What's a "Forgotten Feature"?

It's a big important feature that was maybe even "Release Defining" in its day but hasn't been improved or expanded in any significant or obvious fashion for several releases.
  • Case in point: Application Profiling and Database Tracing

  • Case in point: Index Consultant

  • Case in point: Procedure Profiling

Let's talk about Procedure Profiling

Not to be confused with the Application Profiler, Procedure Profiling has been around forever in it's original form, and it still doesn't have Jump To Next Peak:
Make it easier to find the peaks in Profiling Results
sybase.public.sqlanywhere.product_futures_discussion
18 Aug 2007

A claim: Nobody cares about the zeroes, only the peaks.

A simple suggestion: In the ms. and % columns of the Profiling Results
display, do not show zero entries as 0. Show them as dash - or empty
spaces, so that non-zero values will stand out when scrolling down
through very large blocks of text.

A better suggestion: Provide some mechanism to quickly find the peaks,
better than scrolling down through large blocks of text trying to keep
an eye out for big percentages. I suggest that ALL lines should be
initially hidden as "-- show xx hidden lines --" EXCEPT for the lines
with non-zero % values. The user could then click on the "-- show ..."
lines to see the surrounding text.

Right now, finding peaks in the Profiling Results display is quite
time consuming. It is also very frustrating when you scroll wayyy down
through a huge procedure, only to find that 100% of the time is
showing against a CALL to another procedure... the time spent looking
at the calling procedure was a complete waste since the called
procedure is the one that should be examined.


Why are features forgotten?

  • Possible reason: Nobody's available to work on them, they're all busy on other things.
    "Well, of COURSE that's true, the alternative is ridiculous: You think someone's been busy on Application Profiling for years and years but not getting anywhere? Get serious! Better yet, get helpful!"

  • Possible reason: They're all RFP checkpoints. Everyone else has them, and now so do we, so we can move on to something else.
    "Oh, now you're depressing me... none of these are anything like Java In The Database!"

  • Possible reason: Engineering doesn't care.
    "When did iAnywhere Solutions become an autonomous research lab? They didn't, did they? So is Engineering running things, or not? They aren't, are they?"

  • Possible reason: Marketing doesn't care.
    "Now we're getting somewhere."

  • Possible reason: Finance doesn't care.
    "We're doomed!"
A new feature has a real Return On Investment... folks can make sales presentations and write Top 10 lists. Enhancements, not so much... especially if the enhancements are really fixes. Nobody's ever going to write a press release to announce "SQL Anywhere's Application Profiling feature is now easy to use!"

In fact, nobody is ever going to acknowledge that Application Profiling has any problems at all. The glass is half full: Marketing and Sales are genetically incapable of talking about bad stuff. That's not a criticism; developers are incapable of seeing anything except problems and challenges; when they fix one bug, get move on to the next, and if they finish a program they toss it aside and move on to the next. The world needs both; a marketer couldn't debug a spreadsheet formula, and an engineer couldn't sell beer at a ballpark. Finance? Neither.

The Exceptions

Here are some major features which have escaped becoming museum exhibits...
  • Spatial... maybe... I think... only the Alphas know for sure what Spatial is all about.

  • MobiLink... evolving, expanding, exciting!

  • Graphical Plan... getting more useful with every release, almost within grasp by the Betas, maybe soon us Gammas!
The fact that exceptions exist throws the museum exhibits into sharp relief... if "sharp relief" can be said about something dim, dusty and forgotten.

Dilbert.com


Friday, September 16, 2011

The fRiDaY File - Life Before "Fuji"

Dilbert.com




"Fuji" is the SQL Anywhere OnDemand Edition

Fuji might not be "just for Independent Software Vendors" like the promotional material seems to suggest... if it works for ISVs, it will work for anyone with a sackload of separate SQL Anywhere databases to administer.

...but no, it's got nothing to do with replication or synchronization... there are other reasons that folks create separate databases, and by its very nature SQL Anywhere makes it easy to do that (create separate databases).

...and so, it seems natural to build a Fuji on top of that ability.

Want more?



Thursday, September 15, 2011

The Thursday Quote - Kevin Benedict


"Sybase still needs to learn more about supporting end user customers, rather than ISV partners."
What Kevin Benedict is Learning about Enterprise Mobility this Month by Kevin Benedict, from Mobile Enterprise Strategies July 29, 2011

That was written wayyyy back in July, this post was put together on the Sunday before Techwave, and today (Thursday) is the penultimate day of TechEd and Techwave... I wonder if Kevin's point has been addressed?

Here's a bit of the wider context from Kevin's article:

SAP/Sybase is investing a lot of thought and resources into the concept of enterprise mobile app stores. Their strategy, as I understand it, is to develop the frameworks and infrastructure that companies will need to manage, trust and secure hundreds of different mobile solutions from many different vendors.

Sybase still needs to learn more about supporting end user customers, rather than ISV partners. They must limit their blanket marketing statements, and get specific with what they support today, and what they will support over time. For example, don't say you support mobile devices that use the Microsoft OS, if you don't yet support Windows Phone 7 with Afaria. This is important to the company that is ordering devices today. Don't say you support Android OS, if you are supporting only a subset of Android with specific device manufacturers. Again this is very important to the company ordering devices today. Be specific about today's supported devices. Provide specific guidance and details to the people purchasing devices.

I am seeing that it is extremely hard for a corporate IT organization to figure out an enterprise wide mobile middleware strategy. There are so many decision points and variables that this discussion just sinks into the mire. It is far easier to choose the best mobile app for a business process. I think once there are enough mobile apps that use SUP, then the mobile middleware discussion and strategy will be far easier.
So that covers end user customers, what does he say about ISV partners?

I am seeing third party mobility vendors turning away from the SAP mobile app market, because they don't have visibility into where there are opportunities to make money. They hear SAP/Sybase talking about developing hundreds of mobility apps, so this market seems to be risky, shrinking and not worth their future investment. Yet, I hear SAP/Sybase saying they need and want third party mobility vendors to develop mobile applications. There is some work needed here on SAP/Sybase's part to give better guidance and incentives to third party mobility vendors.
Next week: Brian Dorn


Wednesday, September 14, 2011

The Morning After

Didya see the SQL Anywhere Fuji demos at Techwave yesterday?

Huh? Didya? Tell me all about it...

Some of you may have noticed I'm not at Techwave this year so I haven't seen it working.

In fact, as you read this I'm driving through The Great State Of Memphitucky, dodging Irene and Lee and Maria and Nate and the Joplin tornados and The Great Earthquake of DC... travelling by car is ever so much more interesting than flying... plus you get to keep your clothes on and you don't get groped (that's good, right?)

Back to Fuji...

...is it cool?

Has it gone Beta?

Didya get a copy?

Can I have one?

Puhleeease?


Dilbert.com


Monday, September 12, 2011

OLAP Counseling

In his great novel Brave New World Aldous Huxley divided The World State into five heavily conditioned castes: the fully developed Alphas, and the arrested development Beta, Gamma, Delta and Epsilon castes. I know I'm not an Epsilon because I know what an Epsilon is and they don't.

But I am just as surely not an Alpha, and that means I'm not an Alpha Plus, the higher caste-within-a-caste, the leaders and intellectual elites.

Let's say I'm a Beta. OK, Gamma, but let's pretend for a moment, shall we? (Hey! Who shouted "Delta"? :)


"Alpha children wear grey. They work much harder than we do, because they're so frightfully clever. I'm really awfully glad I'm a Beta, because I don't work so hard. And then we are much better than the Gammas and Deltas. Gammas are stupid. They all wear green, and Delta children wear khaki. Oh no, I don't want to play with Delta children. And Epsilons are still worse. They're too stupid to be able ..." - Wikiquote


Here's what I'm afraid of:

Only Alphas understand OLAP


The WINDOW clause and the associated window functions were invented by Alpha Pluses for use by the Alpha caste, that's what I'm afraid of. (Either that, or I'm a Delta... that's always possible :)

Of course, The World State is fictional, this is the real world, and in the real world it is possible for the upper castes to teach the rest of us about new stuff. Here's Glenn Paulley responding to my earlier complaint about the WINDOW clause not answering the question "How do I select the first row in each partition of a table?":

It's helpful to think of WINDOW as augmenting the result set that is generated by the FROM/WHERE clause - WINDOW doesn't *just* return the aggregate values for groups (if PARTITION is used), it adds to the projection of the result set.

Consequently, if what you want is *the* row that is the "lowest" of a partition, you have to eliminate all of the other rows from result set.

FIRST_VALUE does indeed return the first value in the partition. However, you need to restrict the result set to eliminate those rows from t where the row is not the first value for that partition - and that's a bit harder to do because it's possible to have duplicate values for either entry_id or data within each partition.

A better way of getting what you want is to use the RANK function, which will return 1 for the lowest value in each partition (and will label ties similarly). The query is
SELECT partition_id,
       entry_id,
       data 
  FROM ( select partition_id, 
                entry_id, 
                data,
                rank ( ) OVER partition_id_window AS entry_rank
           FROM t
         WINDOW partition_id_window AS (
                   PARTITION BY partition_id
                   ORDER BY entry_id
                ) ) DT
 WHERE entry_rank = 1
 ORDER BY partition_id;


Glenn's efforts to educate are most appreciated, especially since he knows it's an uphill task:

"Unfortunately, WINDOW functions remain beyond the experience of the majority of application developers, who typically have a working knowledge of the considerably simpler SQL:1992 standard."


So...

Let's Compare and Contrast


Here's the original problem: "How do I select the first row in each partition of a table?"
CREATE TABLE t ( 
   partition_id     INTEGER NOT NULL,
   entry_id         INTEGER NOT NULL,
   data             VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( partition_id, entry_id ) );

INSERT t VALUES ( 10, 1, 'aaa' );
INSERT t VALUES ( 10, 2, 'bbb' );
INSERT t VALUES ( 10, 3, 'ccc' );

INSERT t VALUES ( 20, 1, 'ppp' );
INSERT t VALUES ( 20, 2, 'qqq' );
INSERT t VALUES ( 20, 3, 'rrr' );

INSERT t VALUES ( 30, 1, 'xxx' );
INSERT t VALUES ( 30, 2, 'yyy' );
INSERT t VALUES ( 30, 3, 'zzz' );

SELECT [the first row in each partition of t];

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

Here's the OLAP solution using WINDOW and RANK():
SELECT partition_id,
       entry_id,
       data 
  FROM ( select partition_id, 
                entry_id, 
                data,
                rank ( ) OVER partition_id_window AS entry_rank
           FROM t
         WINDOW partition_id_window AS (
                   PARTITION BY partition_id
                   ORDER BY entry_id
                ) ) DT
 WHERE entry_rank = 1
 ORDER BY partition_id;

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

Here's the Old School solution using GROUP BY and MIN():
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;

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

Here's the comparison:
OLAP Solution                   Old School Solution
=============                   ===================

Built upon a WINDOW clause.     Built upon a GROUP BY clause.

Uses RANK() to find the first   Uses MIN() to find the
row in each PARTITION.          first row in each GROUP.

A derived table is required     A derived table is required
because RANK() can't appear     because t.data can't appear
in a WHERE clause in the        in a SELECT based on
same SELECT.                    GROUP BY partition_id.

Requires a WHERE clause to      No WHERE clause is required.
make use of the RANK() result.   
The part about "a derived table is required" makes the two solutions pretty much the same when it comes to understandability: all other things being equal, any query that requires a derived table is going to be harder to write and understand than a query that doesn't need one.

Which query is best is not the point here, the point is that the WINDOW clause can be made to work using the RANK() function...

...which only Alphas know about?


When Betas (OK, Deltas) use OLAP they use the Help, and they learn what they can do by looking at topics like this:
all of which list MIN(), some of which list FIRST_VALUE(), and none of which list RANK().

Memo to the Doc Team: Please accept my apologies for dragging you into this! Stuff happens, and in normal circumstances a simple suggestion in DocCommentXchange would suffice, but the opportunity to make a smart-aleck comment point here was impossible to forgo!


HERE'S the scary part...

...and it's not about the Help or which method works or is easier to write and understand, it's this:
Is it important to performance to use OLAP instead of Old School solutions?
Here's a hint...

"...window functions have the potential to lead to better query execution plans by eliminating joins and nested queries, yet retaining the simplicity of a single SQL statement."
Glenn Paulley in The Window operator and window aggregate functions, March 31st, 2009

...maybe not in this case, but just because the Old School solution worked it doesn't mean we can give up on learning about OLAP and retreat to the familiar.

Apparently, not if we care about the improvements coming to us courtesy of the Alphas!

Dilbert.com


Friday, September 9, 2011

The fRiDaY File - Impostor Syndrome

Verbatim chat whilst preparing for a meeting of the SQL Anywhere Customer Advisory Board...


Margaret
Why do you call it WATCOM SQL instead of SQL Anywhere?

me
WATCOM SQL is one of the two dialects of SQL supported by SQL Anywhere. The other is TRANSACT SQL.

Your audience will understand :)

Margaret
OK. :)

Yes well my huge fear is that someone won't and will ask me and I will mumble and stumble and go all red in the face and Dave Neudoerffer and Mike Paola will jump up and scream "Impostor!" in front of everybody and everybody else will join in and I will be totally disgraced forever and won't even be able to get a job as a meter maid and will die a lonely death hiding in poverty living in a lean-to in the Don Valley with two rotted teeth left in my head and 17 cats.

But not today.....


Dilbert.com


Next Week is Fuji Week at Techwave

If you're going to Techwave next week and you're thinking "I can sleep in the first day, the technical sessions don't start until the afternoon" here's a reason to get out of bed at a reasonable hour on Tuesday:


Tuesday, September 13, 10:15am – 11:30am

Plenary Session: Sybase Steps Further into the Cloud — Scaling Data Management in SaaS Applications

The adoption of cloud computing is demonstrating it has a highly disruptive and positive impact for many companies and is expected to greatly affect application delivery architectures over the next decade. To realize the benefits of the cloud, application developers and IT departments need to rethink how information is accessed, deployed and managed. Sybase is taking critical steps to enable application providers to manage information in the cloud. Learn about trends among Sybase customers and ISVs, key Sybase cloud initiatives across the database portfolio, and the unveiling of Sybase's newest offering. Code named 'Fuji', this new cloud data platform addresses the challenge of giving SaaS providers a scalable and manageable solution that also meets data governance demands of their business customers.

It's not clear from that description but

"Fuji" = "SQL Anywhere"

not ASE, but SQL Anywhere. You know, the self-managing RDBMS.

But wait, there's more; here's a reason to skip the after-lunch nap:

Tuesday, September 13, 1:00pm – 3:00pm

Plenary Session: Innovating the Future with Sybase Data Management

At last year's TechWave, SAP had just completed the acquisition of Sybase. At the time, customers were cautiously optimistic, but had questions about Sybase's future. Join Sybase's product and technology leaders a year later as we report on what has turned out to be one of the most innovative and productive years EVER in Sybase's 25-year history! In this plenary, we highlight our innovations designed to invest in the future of your company and your career, including the introduction of the newest release of our flagship RDBMS, ASE 15.7, which features capabilities that will benefit developers and DBAs alike. This ASE release is also the first to be certified to run SAP's Business Suite applications — an event that is renewing an eco-system on which many of you depend. Discover the latest innovations in data movement technologies with Replication Server as well as the latest advancements of SQL Anywhere 12, our leading Mobile and Embedded RDBMS. Finally, we will introduce Sybase's newest offering, a cloud data platform code named 'Fuji', addresses the challenge of giving SaaS providers a scalable and manageable solution that also meets data governance demands of their business customers. Come, celebrate with us, and see first-hand why engineers who have been with Sybase since the early days are so energized about our future.


Dilbert.com


Thursday, September 8, 2011

The Thursday Quote - Rudy Rucker


"Harry wants me to help him reproduce as a slime mold."
My Office Mate by Rudy Rucker, from Communications of the ACM July 2011

What can anyone say about a line like that?

Other than to provide a wider context? OK, if you think it'll help...

Sure enough, when I came into the office on Monday, I found Harry's desk encumbered with a small biological laboratory. Harry and his woman friend Velma were leaning over it, fitting a data cable into a socket in the side of a Petri dish that sat beneath a bell jar.

"Hi Fletch," said Velma brightly. She was a terminally cheerful genomics professor with curly hair. "Harry wants me to help him reproduce as a slime mold."

"How romantic," I said. "Do you think it'll work?"

"Biocomputation has blossomed this year," said Velma. "The Durban-Krush mitochondrial protocols have solved our input/output problems."

"A cell's as much a universal computer as any of our department's junk-boxes," put in Harry. "And just look at this! My entire wetware database is flowing into every one of these slime-mold cells. They like reverse Polish. I'm overwriting their junk DNA."
It didn't help, did it? Well, you'll just have to read the whole thing.


Next week: Kevin Benedict


Wednesday, September 7, 2011

Unrequited OLAP

OLAP is so attractive from afar, it solves so many Big Important Problems, how can anyone not love it?

Well, it's easy not to love OLAP: just try something simple, something that should be easy given all the expressive power built in to the WINDOW clause and all the related window functions like, say, FIRST_VALUE.


Question: How do I select the first row in each partition of a table?

Here's an example:
CREATE TABLE t ( 
   partition_id     INTEGER NOT NULL,
   entry_id         INTEGER NOT NULL,
   data             VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( partition_id, entry_id ) );

INSERT t VALUES ( 10, 1, 'aaa' );
INSERT t VALUES ( 10, 2, 'bbb' );
INSERT t VALUES ( 10, 3, 'ccc' );

INSERT t VALUES ( 20, 1, 'ppp' );
INSERT t VALUES ( 20, 2, 'qqq' );
INSERT t VALUES ( 20, 3, 'rrr' );

INSERT t VALUES ( 30, 1, 'xxx' );
INSERT t VALUES ( 30, 2, 'yyy' );
INSERT t VALUES ( 30, 3, 'zzz' );

SELECT [the first row in each partition of t];

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

Answer: OLAP to the rescue! It's got the PARTITION keyword, and ORDER BY, and RANGE, and FIRST_VALUE, everything you need!

Dilbert.com

Hubba hubba, here we go! ...
SELECT partition_id,
       FIRST_VALUE ( entry_id ) OVER partition_id_window AS first_entry_id,
       FIRST_VALUE ( data     ) OVER partition_id_window AS first_data
  FROM t
WINDOW partition_id_window AS (
         PARTITION BY partition_id
         ORDER BY entry_id
         RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
 ORDER BY partition_id;

partition_id,first_entry_id,first_data
10,1,'aaa'
10,1,'aaa'
10,1,'aaa'
20,1,'ppp'
20,1,'ppp'
20,1,'ppp'
30,1,'xxx'
30,1,'xxx'
30,1,'xxx'

Well, that sucks!

That's not even close!

You can't code RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING, that throws a syntax error. In fact, the RANGE clause seems to be singularly useless, it has so few options.

It's almost like the WINDOW clause was designed by a standards committee.

Old School

Remember GROUP BY? Yeah, we used to think THAT was complicated before the WINDOW clause came along.
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;

partition_id,entry_id,data
10,1,'aaa'
20,1,'ppp'
30,1,'xxx'

Tell me it ain't so!

Please, someone tell me the WINDOW clause isn't so hard to get.

That may be wishful thinking, however...

"Window operations don’t add expressive power to the SQL language because other constructions, which may require multiple statements, can provide the same functionality as window functions."
Glenn Paulley in The Window operator and window aggregate functions, March 31st, 2009

Monday, September 5, 2011

What a great idea!


"How to log all database server errors like primary key violation error, column not found...into separate table on the same database with error message, user id, date and time. Is there any system event to log the errors?"
How to log all database server errors? by Karuppasamy, from SQL Anywhere Forum September 3, 2011



Who is Karuppasamy? He (let's go with "he" for the moment) has been a "Registered user" of the SQL Anywhere Forum since August 26 when he asked this question: SA12 server is crashing abruptly.

A week later there are still no answers to that question, and nobody's voted the question up, so there are no more reputation points for Karuppasamy beyond the single point doled out to newcomers.

Nevertheless, he hasn't lost faith in the usefulness of the forum because he's back with another question.

And judging from that new question "How to log all database server errors?" he's still working on the "crashing abruptly" problem... or maybe a new problem... whatever the case, he has just asked...

The Best Question To Come Along In A Very Long Time

That's a bold statement, and maybe you don't agree that it's even a good question since the answer is a blunt and useless "You can't."

Maybe your opinion will change if the question was edited:

Product Suggestion: SQL exception table

Please implement a table to automatically store information about all SQL exceptions; e.g. primary key violations, column not found, etc.

The table should contain the error message, user id, date and time.

"The table" should probably contain a few more things, and there should probably be discussions about turning the feature on and off, maybe even about filtering... but the bottom line is this: It would be a huge benefit to anyone developing a serious application using SQL Anywhere, and... it might not get implemented.

Why not?

Because of the UNECDIC, that's why

As you read this, a meeting of the ultra-secret United Nations Emergency Can't Do It Committee (UNECDIC) is being convened to discuss why an exception table cannot be implemented. Members of this committee include industry experts with experience in related emergencies; for example:

The good news is...

The UNECDIC has never once succeeded in keeping a good idea down, so maybe we will see an exception table in SQL Anywhere!

Dilbert.com


Friday, September 2, 2011

The fRiDaY File - "Drinks for everyone!"


...what WILL SAP do with all that money?


Raises for everyone?

Dilbert.com


Wishing for a WER

Some mornings are better than others.

On some mornings, the sky is sunny and clear, the winds calm and the fish biting. On other mornings, the outer bands of Irene are whipping up waterspouts.

On some mornings, the email inbox is empty.

On other mornings, it contains three thousand diagnostic messages recorded by a single copy of Foxhound over a period of seven months...

1	2011-02-24 15:57:38.102 Full Build ----- 703 410c9(410eh1) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p004': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user [---]
2	2011-02-24 15:57:38.645 Full Build ----- 703 400a1(400eh1) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p004': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user
3	2011-02-25 00:18:14.182 Full Build 3832a 1000000036 202a3a(202eh1) Connection timeout for target DSN xxx after 18.4s (timeout threshold is 15.0s; see Foxhound Options) - 1000000073
4	2011-02-25 13:03:45.312 Full Build 3832a 1000033533 204.f7(204eh-c7) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Database server not found [1-xxx]
...
3108	2011-08-31 12:08:35.935 Full Build 3832a 12 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3109	2011-08-31 12:08:35.937 Full Build 3832a 12 180e3(180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3110	2011-08-31 12:09:49.714 Full Build 3832a 35 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3111	2011-08-31 12:09:49.715 Full Build 3832a 35 180e3(180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3112	2011-08-31 12:12:40.881 Full Build 3832a 90 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3113	2011-08-31 12:12:40.882 Full Build 3832a 90 180e3(180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3114	2011-08-31 12:13:53.590 Full Build 3832a 106 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3115	2011-08-31 12:13:53.592 Full Build 3832a 106 180e3(180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3116	2011-08-31 12:16:21.543 Full Build 3832a 160 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3117	2011-08-31 12:23:22.251 Full Build 3832a 279 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3118	2011-08-31 12:29:06.292 Full Build 3832a 423 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3119	2011-08-31 12:56:07.854 Full Build 3832a 786 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3120	2011-08-31 13:13:00.040 Full Build 3832a 9 182a3(182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3121	2011-08-31 13:13:00.041 Full Build 3832a 9 180e3(180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3122	2011-08-31 14:28:40.743 Full Build 3832a 1000000022 202a3a(202eh1) Connection timeout for target DSN xxx after 21.5s (timeout threshold is 15.0s; see Foxhound Options) - 1000000023
3123	2011-08-31 14:39:40.291 Full Build 3832a 1000003243 204.j5(204eh-d3) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase][ODBC Driver][SQL Anywhere]Connection was terminated [1-xxx]
3124	2011-08-31 14:40:09.599 Full Build 3832a 1000003648 750d9d(750eh1) SQLCODE = -185, SQLSTATE = 21000, ERRORMSG() = SELECT returns more than one row
Of course that would arrive one day after writing about Microsoft's Windows Error Reporting (WER) service and its jaw-dropping ability to receive and analyze 100 billion error reports per day.

Maybe it's punishment for unfairly associating iAnywhere Solution's Crash Report service with Dogbert-as-Tech-Support.

[sigh]


What I really need is my own WER thingie, and an army of programmers.

What I've got, is Wordpad and a to-do list that has grown longer yet again, wiping out the gains made over the past few days...
-- Investigate Build 3832a: Pair of errors, multiple occurrences: Value 46116860184273880000 out of range for destination
3108  2011-08-31 12:08:35.935 Full Build 3832a 12  182a3 (182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination
3109  2011-08-31 12:08:35.937 Full Build 3832a 12  180e3 (180eh2) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination

-- Investigate Build 3832a: Single error, multiple occurrences: Value 46116860184273880000 out of range for destination
3116  2011-08-31 12:16:21.543 Full Build 3832a 160 182a3 (182eh1) SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value 46116860184273880000 out of range for destination

-- Investigate Build 3832a: Statement interrupted by user
1     2011-02-24 15:57:38.102 Full Build ----- 703        410c9    (410eh1)   SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p004': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user [---]
2     2011-02-24 15:57:38.645 Full Build ----- 703        400a1    (400eh1)   SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p004': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user
1018  2011-07-06 11:56:52.457 Full Build ----- 1000073088 204.h6b  (204eh-c9) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user [---]
2941  2011-08-25 21:49:20.188 Full Build ----- 1000725360 204.i8   (204eh-d1) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p002': [Sybase][ODBC Driver][SQL Anywhere]Statement interrupted by user [---]

-- Investigate Build 3832a: The Famous "WTF?" Non-Error Error: SQLCODE = 0
12    2011-04-21 14:31:17.944 Full Build ----- 39717      050a2    (050eh1)   SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]
111   2011-06-19 01:33:10.519 Full Build ----- 1000313855 211a1    (211eh1)   SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]
132   2011-06-20 18:02:57.524 Full Build ----- 1000397718 211a1    (211eh1)   SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]
951   2011-07-05 15:12:06.645 Full Build ----- 2430       310a3    (310eh1)   SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]
1014  2011-07-06 11:24:37.258 Full Build ----- 1000000106          (211eh1)   SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]
2042  2011-07-29 07:56:44.863 Full Build ----- 1000397677 204.i8   (204eh-d1) SQLCODE = 0, SQLSTATE = 00000, ERRORMSG() =  [---]

-- Investigate Build 3832a: Many occurrences: SELECT returns more than one row
19    2011-04-26 11:00:14.647 Full Build 3832a 1000764625 750d9d   (750eh1)   SQLCODE = -185, SQLSTATE = 21000, ERRORMSG() = SELECT returns more than one row

-- Investigate Build 3832a: Multiple occurrences of most examples: Remote server does not have the ability to support this statement
83    2011-05-29 15:55:56.206 Full Build 3832a 1000000028 204.e4c2 (204eh-c6) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
1110  2011-07-07 02:28:32.580 Full Build 3832a 1000110600 204.e4d1 (204eh-c6) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
1973  2011-07-27 17:06:29.237 Full Build 3832a 1000310067 204.e4e1 (204eh-c6) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
2215  2011-08-01 13:37:14.092 Full Build 3832a 1000561341 204.e4f1 (204eh-c6) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
2250  2011-08-03 04:50:08.216 Full Build 3832a 1000650389 204.e4g1 (204eh-c6) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
1483  2011-07-10 10:20:23.304 Full Build 3832a 1000280647 204.e7   (204eh-c7) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
1706  2011-07-13 11:42:11.235 Full Build 3832a 1000419934 204.e9   (204eh-c7) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
1458  2011-07-10 06:29:22.895 Full Build 3832a 1000272095 204.f7   (204eh-c7) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [1-xxx]
89    2011-06-17 12:33:33.958 Full Build 3832a 1000000027 204.g9   (204eh-d4) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [2-Leia data-warehouse]
1065  2011-07-06 19:38:50.877 Full Build ----- 1000096009 204.h6b  (204eh-c9) SQLCODE = -706, SQLSTATE = WO012, ERRORMSG() = Remote server does not have the ability to support this statement [---]

-- Investigate Build 3832a: Multiple occurrences: Unexpected database number ""
1139  2011-07-07 06:45:32.420 Full Build 3832a 1000120133 204.c4   (204eh-b3) Unexpected database number "" [1-xxx]

-- Investigate Build 3832a: Multiple occurrences: Unexpected ConnCount ""
1462  2011-07-10 07:02:23.225 Full Build 3832a 1000272095 204c5a1a (204eh-b4) Unexpected ConnCount "" [1-xxx]

-- Investigate Build 3832a: Single occurrence: (fatal) @string_target_current_timestamp = ""
2987  2011-08-27 08:01:54.809 Full Build 3832a 1000792423 204.c6   (204eh-b5) (fatal) @string_target_current_timestamp = "" [1-xxx]

-- Investigate Build 3832a: Pair of errors, single occurrence: CREATE PROCEDURE rroad_database_properties failed. followed by SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [empty]
1781  2011-07-16 12:51:43.072 Full Build 3832a 1000592808 702.a2   (702eh4) CREATE PROCEDURE rroad_database_properties failed. SQLSTATE = WO005, ERRORMSG() = Server 'p001':  [1-xxx]
1782  2011-07-16 12:51:43.074 Full Build 3832a 1000592808 204.d6b  (204eh-d7) SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001':  [1-xxx]
All of these exceptions occurred in code that's 122 builds out of date, but that doesn't matter: they all have to be investigated in case they are new.

Starting with the "SELECT returns more than one row"... that's gotta be something boneheaded, how could it get past Quality Assurance?

Some of them are going to remain mysteries, like "SQLCODE = 0, SQLSTATE = 00000". That one's been haunting me for years... nobody has an answer to the question What does an EXCEPTION for SQLCODE = 0 mean?

The "Value 46116860184273880000 out of range for destination" may already be solved by code changes to ignore bogus property values returned by SQL Anywhere. Or maybe not... it has been a challenge retrofitting edits into an application originally written under the assumption that calls to PROPERTY(), DB_PROPERTY() and CONNECTION_PROPERTY() never ever returned garbage, not once...

not ever...


Some mornings, I feel like a coffee.

Other mornings, I feel like the mug...

Dilbert.com


Thursday, September 1, 2011

The Thursday Quote - Kinshuman Kinshumann


"The (Windows Error Reporting) service employs approximately 60 servers provisioned to process well over 100 million error reports per day. "
Debugging in the (Very) Large: Ten Years of Implementation and Experience by Kinshuman Kinshumann, Kirk Glerum, Steve Greenberg, Gabriel Aul, Vince Orgovan, Greg Nichols, David Grant, Gretchen Loihle, Galen Hunt from Communications of the ACM July 2011

What Kinshuman is talking about are those "E.T. call home" moments when Windows calls the Mother Ship to report that something has gone wrong. No, there's nobody sitting at Microsoft waiting to handle your error report, but you knew that already.

What you probably didn't know, and I sure didn't know, is that Microsoft does do something with those error reports, and it does get a lot of them. Not 100 million a day, that's the planned capacity, but a lot nonetheless; here are two excerpts from the article:

WER is the first system to provide automatic error diagnosis, the first to use progressive data collection to reduce overheads, and the first to automatically direct users to available fixes based on automated error diagnosis. WER remains unique in four aspects:

1. WER is the largest automated error-reporting system in existence. Approximately one billion computers run WER client code: every Windows system since Windows XP.

2. WER automates the collection of additional client-side data for hard-to-debug problems. When initial error reports provide insufficient data to debug a problem, programmers can request that WER collect more data in future error reports including: broader memory dumps, environment data, log files, and program settings.

3. WER automatically directs users to solutions for corrected errors. For example, 47% of kernel crash reports result in a direction to an appropriate software update or work around.

4. WER is general purpose. It is used for operating systems and applications, by Microsoft and non-Microsoft programmers. WER collects error reports for crashes, non-fatal assertion failures, hangs, setup failures, abnormal executions, and hardware failures.

. . .

WER collected its first million error reports within 8 months of its deployment in 1999. Since then, WER has collected billions more. The WER service employs approximately 60 servers provisioned to process well over 100 million error reports per day. From January 2003 to January 2009, the number of error reports processed by WER grew by a factor of 30.

The WER service is over provisioned to accommodate globally correlated events. For example, in February 2007, users of Windows Vista were attacked by the Renos Malware. If installed on a client, Renos caused the Windows GUI shell, explorer.exe, to crash when it tried to draw the desktop. A user's experience of a Renos infection was a continuous loop in which the shell started, crashed, and restarted. While a Renos-infected system was useless to a user, the system booted far enough to allow reporting the error to WER—on computers where automatic error reporting was enabled—and to receive updates from Windows Update (WU).

As Figure 2 shows, the number of error reports from systems infected with Renos rapidly climbed from 0 to almost 1.2 million per day. On February 27, shown in black in the graph, Microsoft released a Windows Defender signature for the Renos infection via WU. Within 3 days enough systems had received the new signature to drop reports to under 100,000 per day. Reports for the original Renos variant became insignificant by the end of March. The number of computers reporting errors was relatively small: a single computer (somehow) reported 27,000 errors, but stopped after being automatically updated.


Here in our own little corner of the computing world called "SQL Anywhere" we see what appears to be a similar error reporting service...





... I wonder what happens to those error reports?

Surely not this!


Dilbert.com

Next week: Rudy Rucker