Friday, September 30, 2011
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
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.
"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
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:
After having seen so much of what traditional methods have given us,
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?
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!
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:
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
Sounds like he's talking about us, doesn't it?
"...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
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:
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!
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.
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!"
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!
Friday, September 16, 2011
"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?
- Read the overview,
- read the press release,
- watch the five-minute video,
- listen to the 18-minute MP3 (lean in, listen closely, it's kinda faint),
- read about Fuji Versus NoSQL, and
- sign up for the beta (well, pre-register now, get it in October): http://response.sybase.com/forms/fujibeta
Thursday, September 15, 2011
The Thursday Quote - Kevin Benedict
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?
"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
Here's a bit of the wider context from Kevin's article:
So that covers end user customers, what does he say about ISV partners?
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.
Next week: Brian Dorn
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.
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?
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:
- Aggregate functions,
- Window aggregate functions and
- Mathematical formulas for the aggregate functions,
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 asmart-aleck commentpoint 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...
...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.
"...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
Apparently, not if we care about the improvements coming to us courtesy of the Alphas!
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.....
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:
It's not clear from that description but
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.
"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.
Thursday, September 8, 2011
The Thursday Quote - Rudy Rucker
What can anyone say about a line like that?
"Harry wants me to help him reproduce as a slime mold."
My Office Mate by Rudy Rucker, from Communications of the ACM July 2011
Other than to provide a wider context? OK, if you think it'll help...
It didn't help, did it? Well, you'll just have to read the whole thing.
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."
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!
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:
"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.
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.
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:- Personal computers: "There is no reason for any individual to have a computer in his home."
- 32-bit computers: "640K ought to be enough for anybody."
- Row-level locking: "... if you think you need row level locking, you probably aren't thinking set based ..."
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!
Friday, September 2, 2011
The fRiDaY File - "Drinks for everyone!"
...what WILL SAP do with all that money?
Raises for everyone?
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 rowOf 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...
Thursday, September 1, 2011
The Thursday Quote - Kinshuman Kinshumann
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.
"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 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!
Next week: Rudy Rucker
Subscribe to:
Posts (Atom)
