A Little DB2 SQL Adventure

I don't usually blog about this sort of technical stuff, though I deal with it daily. That probably explains right there why I avoid it as if it is tainted by the plague. But this particular problem was interesting enough that I thought it might be worth conveying it.

The challenge for my associate came in the form of a bit of SQL that needed to pull apart a string (unknown until runtime) before it was inserted into a data store. A stored procedure wasn't possible, and the parsing requirement was unavoidable. It also had to be done with inline SQL.

It was known that this particular string could have several forms:
  1. A string containing alphanumeric elements separated by a dash;
  2. A string containing alphanumeric elements without a dash; and
  3. A zero-length string containing nothing (that could not be converted to a NULL on the incoming side for reasons not relevant here, but such that COALESCE was suddenly not a functional solution).
The SQL causing them grief had two variations in the INSERT, but to isolate the data elements I'll stay with simple SELECT statements from the system dummy table. And we'll call the input containing the string {Landmark} for the sake of abstraction.

The first SELECT would look thus:

In essence the data element expected for return would see a dash in the Landmark string and return everything left of that dash. (For those wondering why not use LEFT in this context, it ultimately creates the same basic problem in as much as it will fail to return correctly if the Landmark value has no dash. So, it would have been a different approach with the same sticking point.)

The second SELECT would look thus:

SELECT SUBSTR('{Landmark}',1,LOCATE('-','{Landmark}')) FROM SYSIBM.SYSDUMMY1;
A simpler view of the same issue, which is easily illustrated by plugging in a few actual values that landmark might pass in.

The First Form

To focus now we can strip the SELECT and FROM SYSIBM.SYSDUMMY1 parts of the exemplar. It leaves us with the inline SQL reading:


And the SQL works perfectly if the data coming in contains a dash and is not zero-length. In other words, '12345-12345-666' parses perfectly:

SUBSTR('12345-12345-666',1,LOCATE('-','12345-12345-666' )-1)

...returns a LOCATE value of 6, and when you subtract 1 it works out to return the substring starting at the first character and returning the first 5 characters.

The problem is that if the incoming string contains no dashes the LOCATE returns 0, and when you subtract the 1 from 0 the SUBSTR function rightfully chokes on the parameter. For example:

SUBSTR('1234512345666',1,LOCATE('-','1234512345666' )-1)

...is really:


And SUBSTR doesn't quite know hat to do with that.

An even more complex problem arises when the string coming in is zero-length, because the resultant SUBSTR not only faults on -1 for is third parameter, it chokes on the first one having no length.

Using a stored procedure here would be glorious because it could check the length and do all that work in an orderly way, but my associate was stumped by how to get it to work inline and handle the three possibilities of proper dashes, no dashes and a zero-length condition.

What does work though is the inline use of a dual CASE that can handle the three possibilities (yes, it is not the only possibility, but it turned out to be the easiest to convey, even if it is ugly as sin). The statement then would have looked thus:

CASE WHEN LENGTH('{Landmark}')=0 THEN ''
ELSE SUBSTR('{Landmark}',1,LOCATE('-','{Landmark}')
- (CASE WHEN LOCATE('-','{Landmark}') > 0 THEN 1 ELSE 0 END))

It parses by checking Landmark length and returning a zero-length safely if it is thus; and otherwise it runs the SUBSTR with an inner CASE block that tests the LOCATE return value and returns 1 if it is >0 and 0 otherwise. (This return is subtracted from the LOCATE value, so returning 1 subtracts 1, else we subtract 0.) With an example in place:


The benefit is that this protects you from the three possible data conditions.

The Second Form

For the stripped form of the second:

...we can modify it with a simpler inline CASE conditional. We end up with:

SUBSTR((CASE WHEN LENGTH('{Landmark}')= 0 THEN ' ' ELSE '{Landmark}' END),1,LOCATE('-','{Landmark}'))

Or in the actual SELECT form:

SELECT SUBSTR((CASE WHEN LENGTH('{Landmark}')= 0 THEN ' ' ELSE '{Landmark}' END),1,LOCATE('-','{Landmark}')) FROM SYSIBM.SYSDUMMY1;

This works because SUBSTR accepts a 0 as its third parameter, and all we need to do is ensure that Landmark (first parameter) is never a zero-length string. The specific CASE part is:

CASE WHEN LENGTH('{Landmark}')= 0 THEN ' '
ELSE '{Landmark}'

In other words, return a single space if the LENGTH of the actual field data is zero.

Final Thoughts

No one would ever claim that SQL was pretty to look at, but it illustrates how powerful SQL actually can be, and how it is possible to make the SQL layer of a solution (even without aid of a stored procedure) impose some order on incoming data elements.


Getting Paid

This is one of those posts of frustration, so take it for what it is worth.

I have been doing development work since the very early eighties, and a trend has been becoming more notable in those many, many years. Part of it is probably that the industry has devalued itself with the pretence that software is an entertainment-focused industry. You flop Facebook into place, for example, and it becomes the superficial representation of the industry. That it is a poorly designed consumers-as-products farce is irrelevant. Businesses see it, become distracted by it, and suddenly everything must connect, or be like it, or, worse, become some extension of it. This changes expectations in a way that makes it harder to sell the idea of functional software as a business helper, or at the very least changes how people value what is built to support their operational requirements.

The actual trend though is purely financial. A vast number of businesses seem to consider good, focused development of software as a nickel-and-dime extra. Getting a fair contract price for required software development is tough, and even when possible getting paid is sometimes nearly impossible. It means that custom (and consequently effective) solutions are fast becoming less common. It isn't lack of need , or even lack of recognized need, but that businesses just don't feel like paying for a proper solution when they can get something "close enough" without paying. And I'm not talking about open-source here, either, but the far more problematic application of poorly-fit tools to clearly defined problems. 

A recent client (who shall remain nameless, but is an enormous distributor of entertainment wares) proves the nature of the end-result of devaluing the idea of proper-fit software solutions. They essentially managed a multimillion dollar warehouse system via CSV files, and spreadsheets. Now, I have nothing against either, but the idea that this is a suitable platform for big enterprise management and operations is nonsensical. It creates latency, reporting issues, and data quality concerns when you have to manage across such transport forms. Of course, now the problem is that to change is costly, and scary, so more putty gets lodged into more cracks -- and there is no apparent cohesive approach to solving the real problem, which is the lack of responsive integrative systems on the operational front. 

But again the real issue isn't the poor thinking so much as that even with it there is an apparent sense that there's no need to actually that the provision of software services with any respect. The same client base who will carp about solutions being late (not a problem I've made any of them face) are not so conscientious as to make their payments to their service providers on time. It is as if there is some new level of self-centred entering the world of business, and it seems irrelevant whether the provider is good or indifferent. It seems like there is a new rule that reads, "pay as late as possible, and well past due if possible; screw whether we need this service again." And it is that last part that confuses me.

My clients have universally praised me for my focus, my solutions, and my concerns about their businesses. And yet, of late, I spend an inordinate amount of time chasing them for payments. That providing quality service costs something seems, to many, a foreign concept. Maybe they believe I have a money tree, and it will support the family so well that I will always be present when needed?

I know I'm not the only person in this industry who is experiencing this trend toward indifference about quality of service, quality of solution, and general quality. I talk to many each week who say the same thing, which is that many of their clients are well behind the curve in paying for what they received. And while some lag is always forgiven, it reaches a point where all of them have said the words, "I can't continue in an industry where the quality is irrelevant, and the pay is unreliable." For the world this represents a serious problem, because while the young dogs may provide the next new iPhone app, it is the seasoned developers who provide the applications no one sees, but everyone uses. This squeezing of that talent pool is a critical mass issue the industry will eventually face in a bad way.

Now, I will return to fishing for cash, since my money tree has not bloomed this year...yet.


Life Lessons

As I sit here with my coffee this morning I contemplate life lessons learned over the last 6 months. Primarily, I'm thinking about trust; but I'm also thinking about the method whereby we self-induce our experiences.

Trust is the principle focus of my thoughts today, though, specifically how perception can alter our awareness of relationships. If you take any two people who have a trust deficit you need to consider the views of both to reach an appreciation of truth. After all, nothing is black or white in this world. The sole exception of this is when trust is broken by intent, of course, since this represents a decision by one participant to use deception as a means to some end. Once that happens, they have abandoned their view as truthful. Any truth they had is washed away because they are choosing to abandon their integrity, and integrity of viewpoint is a key factor in associating it with truth. 

In the Star Wars films there is a line, in The Empire Strikes Back, where Obi-wan Kenobi remarks that his lies are true from a certain point of view. He is commenting upon his view that Anakin Skywalker was killed by Darth Vader. His view is that Anakin's acceptance of the title alone was an expression of choice, and therefore essentially an abandonment of the righteous path, and a form of spiritual death. Unlike Master Yoda, whose convictions about matters are, in Episodes 1 to 3, founded on dogma, Obi-wan is actually striding along a far more subtle path, and his weariness is a recognition of the price of holding a stringent view. When one harkens back to the fight at the end of Episode 3, the issue is further clarified, because there is another critical formative line. Obi-wan says at one point, just before Anakin's choice to finally attack, that he has the high ground. He is not only speaking tactically there, but from a spiritual perspective he is commenting upon the fact, without dismissing Anakin's views categorically, that there is a purity in his path that his brother lacks. This purity is why Obi-wan manages to sustain his view that purity of truth is less important than purity of intent.

In real life, obviously, there is never such a linear proof of the idea of truth being mutable. The "he-said-she-said" syndrome comes about precisely because of this, and it deeply affects all future communications between parties who suffer moments of mistrust. That said, the last 6 months of my life have pivoted around the ideas of trust, trust misplaced, and viewpoints.

About 13 years ago I became attached to a development project that involved 2 parties, neither of who, behaved entirely respectably. When they came to a falling out point, I had to make a choice whose products my effort was producing. I had to decide who owned the idea. Following logic, I decided that IP had to accrue to the people who seeded the idea, even if their original seed was pale by comparison to end results. This decision cost me financially at the time, leaving me holding a massive related debt, and drove me to sustain the development effort when, in lieu of being able to pay necessary rates, I was offered shares. To shorten the path, in retrospect I mistook my options then. I ought to have walked away, taken the loss, and attenuated my pain, because from the point I chose the other path I was essentially doomed to end that journey as a castaway. Again in retrospect, all the signs of manipulation were present, but despite my own con-man tendencies, I decided trust was fundamental to the process, and even when I mistrusted actions I didn't mistrust intent. That was probably the worst single decision I ever made, because I suspect I was used from the outset and thrown away when the risks of exposure of that use outweighed the value. That said, I would wager the principals of the company would hold I was the problem child, though it doesn't explain that they eliminated my share position by way of bankruptcy, before they did some deal with a company that had been courting them for months. In the end actions speak volumes, and the fact they are using the product I built under the guise of a new company is complimentary, I suppose.

The point I'm aiming at, though, is not a sour grapes moment. It is an observation about basic trust contracts, and how they are valueless if either party betrays the terms. Revisionist viewpoints cannot overcome intent, because the intent to gain is inherently selfish. There is no truth in business, because trust is sacrificial. It is the mechanical way of business. Contacts are, as they say, rather cheap. 

Truth cannot exist without trust, because only trust imparts a foundation for truthful exchanges. And trust that is one sided is a suicidal behaviour. I know in my case it has destroyed me financially, and is probably going to land my family on the streets, not because I can't work onward so much as because I'm still faced with having committed fully to a cause for 13 years, and I left with nothing to show for it. I have the pleasure to watch my efforts enrich others, without even a Merry Christmas. And yet I feel less bitter than weary, because the real violation happened so long ago it is not in the forefront of my mind. I caused my current distress by misplacing my trust early, and was repaid in full and then some by folks whose trust was never really founded. 

The life lesson I take from this is not that one should never trust. To the contrary, one has to. The lesson is more that we need to be conscious of the warning bells. My stress led to a mild heart attack (a pair of them 2 weeks apart), and my life is worth more than any creation my work generates. And to dwell upon this sort of thing is less important than to rise up and move on. No matter what happens in life, one can cling to Obi-wan's statement, with the caveat that to sustain truth you need to have a foundation of trust. Faith, I suppose, is an adequate word. Faith in self, if not in some higher power.

Ultimately the choices we make dish us, and we make those same choices today, even in moments of bleak struggle. To allow past mistakes to force one to repeat mistakes is counterproductive. And, really, as I can attest you can only run from your bad choices so long. I expect that is true for everyone. 


What Comes From The Mind...

Over the last week or so I've been thinking a lot about what comes from the mind, about trust, and about the human condition. My conclusions are both amusing and disturbing to me, but oddly not so much surprising.

As much as folks pay lip service to the value of ideas, honest assessment of the value of ideas is not really a human approach. If an idea excites it is held up as vital, and if it bores it is degraded as being tired; but if you historically analyze ideas you often find the most mundane ones are the ones that have the most lasting impact on the world. What comes from the mind then is valued lightly, and that is probably why so many people will co-opt ideas without ever honouring their source. We all do it, to some extent, and it is where commonplace idea-theft, like we see with plagiarism, arises. The fact is this idea is probably one that has been expressed before, so the only reason I can even partially claim it is that I don't know it has been expressed. And that brings me to the perception factor....

A large part of what we view as our reality is founded in selfish perception. It is how we survive, ultimately, because if we fail to internalize like that I suspect we would be driven mad. Can you imagine, for example, that on a Sunday afternoon you have an amazing idea and before you transcribe it you spend all day searching the Internet to assure it is unique? People would never get a damned thing done! More to the point, it destroys the basic principle of development of ideas into expressions of new ideas. Every idea that exists now is, in truth, a homage to whomever walked a similar path before.

When you express something from your mind you are bound by a fundamental expectation of social trust. You have to trust, for example, that if your idea is truly unique then those who hear it will at least have the sense of honour to try to reference your contributions. This attribution is a basic tenant of social intercourse. It is how we judge our interactions, after all. And dishonesty in this sort of basic interaction, a violation of the fundamental expectation of social trust, is a truer form of dishonesty than most, because it cripples the openness of communication.

Modern businesses based upon Intellectual Property (IP) are often founded on disputed ideas, and while it is occasionally because people genuinely don't know they stood upon the shoulders of giants, it is more often apparent that the theft of ideas is the source of those business aspirations. And this happens because even when ideas have inherent value, people convince themselves that the ideas are not as practically valuable as their implementation. And while this was true in the past of such things as hammers (how you use it creates value beyond the actual physical hammer), it is clearly not the case with software, works of art, or other virtual expressions of ideas.

I think there is something in the human condition, in the structure of our minds, that makes violating social trust easier when you can convince yourself an idea is without value inherently. And yet, the irony is that few people violate social trust when they have no measure of gain. What that says about people is probably not kind, but also not universal. There are, after all, altruists who are honest, just as there are people who claim such a title without acting in accordance of the claims.

I wish that we had the courage as a species to become what Gene Roddenberry imagined for his Star Trek universe, a species who values all contributions, however small, and respects that all living things have claim to some talent. If we could value ideas, and those who express them, and those who expressed what came before, then I believe we would be a stronger species. Maybe in daylight I will even believe it possible that we are.


Windows 8: The Good, The Bad, The Truly Ugly...

It's been a while that I've been using Windows 8, and thanks to Classic Shell I don't often have to look at the funky, chunky, ugly new start menu. Overall, in the last year (give or take a bit), I have formed some impressions about the O/S I feel like plunking down.

First, to defenders of the new look and feel...you're wrong. It isn't that the new UI elements are all bad, but they were not designed with desktop computing in mind. They were designed for some specific form factors (tablets; maybe phones, though I doubt some of those choices), and all else was damned. I don't know whether Windows 8.1 will rectify these issues, but the problems with the new UI are ingrained in whatever principle of design was applied. When you dumb down interfaces, you ensure the reduction of utility of devices over time. Sure, grandma can see the big button, and my big thumb can hit it reliably on a phone, but it is a choice that eschews improvement of the user over meeting the lowest common denominator. And while that might seem to some to equate with "making it easier" the long-term impact is the opposite. And I have an example to prove this point: the Settings charm and the Control Panel. Yes, Control Panel is daunting, but it has power -- and the whole point of accessing the 'control' elements of a system is to control it. Half-control is not the purpose. Yet the PC Settings charm is under-powered to a degree that, if you really need control, you have to go back to the Control Panel anyhow. So, all this new UI element did was fracture the points of access to the underlying control interfaces. Some is available in one location, most is available in the other. Now whether that is eventually rectified isn't the point. The point is that a proper UI redesign would not have caused further access fracturing. It's foolishness to suggest otherwise. And that fracturing means that when a user needs to really access the guts of the PC settings, they still need to step to the Control Panel, making the uncertain user even less capable over time. It makes no sense unless the purpose of the new UI is to actually create this sort of confusion, and reduce user knowledge requirements permanently. And that would be fine if we were talking about limited-value devices, like purpose-focused tablets, but given the incredible reliance of business processes on IT, reducing the utility of the devices is reducing the efficiency of those processes. A new UI for the sake of itself is not proper design.

Second, at an O/S feature level...Windows 8 is an incremental improvement. Network file copying is, for example, much more friendly from a feedback perspective. BUT...the new UI does get in the way of those improvements at times, and, frankly, I have never managed to really get networking on a Windows 8 box in a mixed network client environment to behave. The fact almost all of the quirks are UI-related is telling, and sadly obscures some really interesting tweaks beneath all the presentation junk. And this somewhat renders the feature-improvements moot. What is the point of a better O/S if the better parts are defeated by the surface? (No pun intended.)

Third, and this is a concern I didn't expect to be expressing about an O/S that is essentially a fractional version above Windows 7: there is something inherently unstable in this O/S. I bought a laptop made for Windows 8, touch-screen and all (a waste, that), and in the last year I have noted strange problems that seem integrally bound to updates. Every time there is an update, for example, one or more aspects of the network rigging seems to be altered arbitrarily. (The latest was the addition of a Homegroup icon on the desktop, which seems to be impossible to remove.) Five times, after an "update Tuesday", a network that has functioned prior to the update just fell apart in terms of shares and file sharing. In two cases the configuration actually was altered by the updates, and in three others I have never discovered the cause of the problems. This is just one of the more obvious issues, and not even as aggravating as a few. (The most singularly odd and annoying aspect is that the file system is non-responsive to certain events. Moving a folder or deleting a file ought to remove it from the UI, without requiring the user to hit F5 to refresh -- but when they do hit F5...it ought to refresh instantly. Granted, this oddity has existed for years on certain systems, but that it still remains on Windows 8 is just unforgivable laziness.)

So after about a year of using Windows 8, on the cusp of a new 8.1 rendition, what is the sum positive impact of Windows 8? Nothing, I suspect. It isn't that it lacks for improvements, but that none of them are significant in context of its oddities, its incomplete design, or its farcical lack of address to standing problems from previous versions of Windows. MS needs to either focus on the fundamentals soon, or the problems that they have failed to address for years will ultimately undermine their market share...because as other options improve incrementally, stagnation will doom them.