Month: February 2012

I‘ve been unemployed since January 6th. My former employer sold themselves to their largest competitor, who use SQL Server in a server room based in Seattle, Washington. On either the count of RDBMS or location, I was very definitely redundant.

It was nice they kept me on (the sale was announced in May last year) to do the transfer of data over to Seattle (in November). Then they kept me on for a bit more, for no apparent reason, until January 6th. But eventually, there comes the time when you really are redundant… and no matter how long you’ve had to get used to the idea, it’s still a wrenching experience.

I can’t say I’ve found it easy: I spent an awful lot of time back at the old (empty) offices taking photos, as though letting go were hard. It doesn’t help that I wrote some of my best code for them (which might not mean much, I suppose!), so there’s a sense of that being lost forever. Then, too, you lose the people: best team of people I’ve worked with in about 20 years… just gone.

Eventually you have to move on, though (or else the wallabies are forced to find their own food!). So I’m very excited to report one of Australia’s largest telcos has agreed to take me on from March 5th. Their technology is prettysexy (including Exadata!) and their numbers are silly (3000 databases??!), so I know I shall be busy.

It does come with a cost, though: I feel I’ve been reasonably productive on the blog recently, but that’s only because I’ve had weeks of free time. Come next Monday, I suspect the posting rate here will drop off quite a bit. Just a heads up, then: in this case, it will very definitely be a case of ‘no news means good news’. At least for me!!

So there are now 17 million records in that table, all of which have a VARCHAR2 column filled with data which is actually, if you looked carefully at it, purely numeric data. But now I now do this:

update hjr set col1 = '186298342kjdfvf9' where col1=327642;
commit;

So, now one row has some non-numeric data in the COL1 column: what’s the quickest way of getting Oracle to find which row has non-numeric data? Well, just to make sure I wasn’t re-inventing the wheel, I did a Google (actually a quackquackgo… but that just sounds a bit weird!) and these are the top two searches that came up:

Both sites suggest using Oracle’s TRANSLATE function to swap blank space for any known numeric values found in the input string. If a string were truly numeric, therefore, it would end up containing nothing, and its length would be zero. The second site is a bit tricky to get to these days for some reason -I had to check the Google Cache of it, and the page’s source code to be sure.

But Technonthenet, in particular, is a site I’ve used many times and the quality of whose writings I have no qualms about. So to see it suggesting the ‘translate’ route is interesting and reassuring -because that’s exactly the way of doing things I was going to suggest using anyway!

The translate function swaps found characters for something else. In this case, I’m asking to swap the numbers 1, 2, 3 and so on for a space (‘ ‘). I then use the TRIM function to strip out spaces. I then measure the length of anything that’s left. So, the number ’667′ would have been translated to ‘ ‘ (3 spaces); trimmed to nothing, and thus caused a ’1′ to return (1 means “it’s a number”). But if the input being tested was ’667xf’, then there are no instructions as to what to replace the letters ‘x’ and ‘f’ with, so that would have been translated to ‘ xf’, trimmed to just ‘xf’ and that would have caused a 0 return (0 meaning ‘it’s NOT a number’).

As I say, this was the way I thought I’d go independently, before finding out that respectable sources had been there a long time before. It’s good to know you’re not alone!!

This one’s much cruder. It creates a numeric variable (v_number) and then attempts to assign the input string to it. It’s therefore relying on Oracle’s ability to implicitly convert between data types -and, in this case specifically, on its ability to cast between number and string data types. Should the implicit cast work, the function returns a ’1′: it can only have worked if the input string really did contain only-numeric numbers. Should the implicit cast fail, the function will error -but we have an error handler to deal with that. The error handler simply returns 0, indicating that something in the input string could not be implicitly converted to a number.

I think there’s no doubt that this is a much riskier proposition than before. You are, after all, relying on Oracle’s implicit (and not extensively documented) behaviour, which might change between database versions. You’re also relying on a ‘when others’ error handler -so you’ll be told something’s non-numeric if the code ever errors out for completely spurious reasons.

That said, I know this code has worked on every Oracle version from 8.0.6 through to 11.2.0.3, so if we’re worrying about Oracle changing its product’s behaviour, we’ve been worrying for a long time! Moreover, this code doesn’t seem especially complex, so the chances of triggering the ‘when others’ exception unreasonably seems pretty remote. On both grounds, therefore, maybe we can ‘get away with it’?

So: we have an ISNUMERIC function, which is well-behaved and accords with what others have written; and there’s an ISNUMBER function, which depends on implicit datatype conversions and some other assumptions about Oracle behaviour.

We obviously need some speed and efficiency comparisons. So, first up, here’s the average runtime for ISNUMBER, and the main statistics associated with it:

That’s quite a difference! The method involving implicit casts and error handlers copes in about 27 seconds instead of 129… it is, in other words, about five times as fast in returning a result -and the statistics don’t seem to indicate anything particularly different about either method (same consistent gets, same SQL round trips and so on).

So this is a vote for doing things the dirty way?

Well, let’s alter the experiement a little before we agree to that. Let’s make rather more non-numerics than before:

delete from hjr where isnumber(col1)=0;
update hjr set col1=col||'u8#' where mod(col1,2)=0;
commit;

Thanks to a bit of help from the MOD(2) function, half the rows now contain non-numeric data in the COL1 column. Let’s now re-run our tests. First, the ISNUMBER function, relying on implicit data type casting and error handling:

Interesting that the number of physical reads has reduced so dramatically now that half the data is non-numeric! Note, too, that this now completes in about 55 seconds, which is about twice as long than the 27 seconds it took to find one bad row out of 17 million good ones.

But anyway: here’s the ISNUMERIC version, relying on translating away any numeric characters and counting the length of what’s left:

These are the same sorts of statistics as with the other function, including a big reduction in physical reads-but the elapsed time is still just over 2 minutes… which is almost exactly the same time as it took to find just one non-numeric row first time around. The ‘translate away’ technique is slow, therefore, but at least it’s consistently slow!

The technique that relies on implicit data conversions and error handling remains much faster -but the more and more exceptions it has to throw, the slower it gets.

If you are looking for the proverbial needle-in-a-haystack, then, I reckon the implicit-cast-and-catch might be the way to go. But if you are unsure of your data -specifically, if you are not sure if a significant part of it might be ‘bad’- then the ‘trim(translate))’ function might be more reliable.

It can be tough running an RCSL distro as your main desktop: it might be nicely stable -and thank heavens it’s Gnome 2, not Gnome 3! But it can be just a tad boring.

In particular, I miss having a nice, big, glossy clock to tell me the time (the textual one in the Gnome 2 top panel is tiny!). I also quite like to know whether it’s going to rain over the next couple of days …so some sort of weather forecast display would be good, too. If you could then wrap it all up in HTC Desire-like sexiness, that would be just perfect! A bit like that graphic you see on the left, then?! Indeed.

Of course, if you install any recent version of the Opera browser, even on Linux and even on RCSL distros, it comes with a ‘widget engine’ that allows desktop applications -like clocks and calendars- to run independently (i.e., although Opera has to be installed, it doesn’t have to be running for these widgets to run). You can get some quite glossy weather, clock and calendar widgets that bling things up nicely. However, there’s no way to get them to autostart each time you reboot your box (which is a big omission as far as I am concerned). Additionally, they create an entry in Gnome 2′s bottom panel -which starts getting a bit cluttered very early on in the piece as a result. So Opera widgets are not, for me, the way to go.

No, the way to go is… (drum roll, please): Conky. It’s been around for ages (and I’ve documented getting it to work before, many moons ago), but it’s a tad tricky to get it working on an RCSL distro. Therefore, I’ve done what I always do when something is tricky: I script it within an inch of its life so that with one command, everything gets done automatically.

1. Getting your Weather Service sorted

Before you do anything else, if you’re going to display weather forecasts, you need online access to a weather service. The Weather Channel seems to be the standard way of doing that these days and, despite appearances and the language used (“subscription”!!), it seems to be an entirely zero cost option (provided you don’t make more than 100 enquiries of the service per day). So visit http://www.weather.com/services/xmloap.html, click on the Subscribe button and prepare to part with your personal details, but zero cash, to create a Weather Channel API account.

You’ll be sent an email to activate your new account: click on the link it provides. Some minutes after activation, you’ll be sent another email containing a ‘magic key’. You will need this long, random string of hex characters shortly, as you’ll see.

Independent of all that, you also need to find out your home town’s Weather Channel code: visit www.weather.com and use the search panel to locate somewhere suitable. If you find the right place, you should be taken to a URL that reads something like: http://www.weather.com/weather/today/ASXX0022 …and your ‘town code’ is that last bit. So, in my case, Campbelltown, Australia is code ASXX0022. Remember that information, because you’ll need that shortly, too!

2. The Automated Bit

That downloads a very small shell script, makes it executable and then runs it. Note that when you run it, you have to say which user is to be configured for this desktop bling makeover. So you can run this script as, say, user “hjr” and yet configure the desktop for user “dizwell”.

Since I’m configuring my own desktop, my last command above would simply be:

./dzconky.sh hjr

At this point, you’ll be asked to supply the root password -and then about 15MB of stuff will get downloaded, installed, unpacked, copied and otherwise configured automatically.

3. Final Touches

You now have just one file to edit: in the home directory of the user for whom you’ve just configured all this stuff, there will be a hidden file (so switch on hidden file viewing in Nautilus if you want to browse to it) called .conkyForecast.config. You need to edit that file, so in my case I’d issue the command (as myself, not root):

gedit /home/hjr/.conkyForecast.config

The last line of this file is a long-winded URL. In that URL, you’ll see the place-holders <LOCATION>, <XOAP_PARTNER> and <XOAP_LICENCE_KEY>.

Your job is to replace the LOCATION place-holder with the location code you discovered earlier -in my case, ASXX0022 for Campbelltown. Then replace the XOAP_PARTNER place-holder with the username you used to subscribe to the Weather Channel API. Finally, replace XOAP_LICENCE_KEY with the magic key you were sent in the second email from the Weather Channel.

As you do these replacements, make sure you don’t introduce any extra spaces or line breaks.

For those of you still stuck in pre-history and using Fahrenheit as your temperature measure (I don’t know why we don’t all just switch to Kelvin and have done with it!), change the very last bit of the URL from unit=m to unit=i (the ‘i’ means ‘imperial’ and thus Fahrenheit; the ‘m’ means ‘metric’, or Celsius).

4. All done

At this point, you can (as yourself, not as root) type the command conky to see what happens. If you’ve done everything right, you should see this sort of thing appear in the top right-hand corner of your desktop:

(And can’t you just tell we’re having a wonderful Summer here in Campbelltown!)

You now just need to make sure the ‘widget’ appears each time you log on: that’s a simple matter of clicking Startup Applications → Add. In the dialog that appears, the Name can be something like Conky HTC and for the command, click [Browse], switch on hidden files in Nautilus once more and then select /home/<whoever>/.conky_start.sh.

Hopefully, that’s enough desktop bling to be going on with for now!

5. Acknowledgements

If I hadn’t read Altis at Crackerspot, I wouldn’t have known how to do this. Similarly, FergyTech‘s explanation of configuring conkyForecast was useful: not all the instructions contained there are relevant for this particular exercise, but the bit about where to store the files was key.

So, if you don’t like what my shell script does (it really is intended to be run right after a Portland clean install and doesn’t mind too much what it over-writes!), use their articles to do the job by hand. It’s not difficult, but you’ll never get that hour (or two!) back again!

If I eat some bacon, eggs, and toast …do you not think that the toast looks and sounds as if it might have been a bit of an afterthought? I do, and the effect is caused by that last comma, just before the “and”. It’s called a ‘serial comma’, though because the Oxford University Press Style Book commends it, it’s frequently referred to as the “Oxford Comma”.

Commas generally indicate a slight pause for breath, so that written form of the list makes me want to whistle a bit and put the kettle on before we get around to mentioning the toast. It’s therefore wrong. What I actually had for breakfast was some bacon, eggs and toast. No final comma before ‘and’, you see. No pause. Just a nice, fluid finish to a three-item list. Breakfast over in a jiffy, too.

Now, some people will claim this means I ate two things, not three, but I think it takes a peculiar kind of literalist to get confused on this matter. Eggs and toast remain two physical things even if the ‘and’ is taken to conjoin them grammatically, after all.

Oxfordians will also cite the old man who left his money to “Jill, Joe and Mary” and thus incited a family feud between Joe and Mary (who got half the cash between them) and Jill, who’s busy enjoying her half of the money all on her own. If only, they argue, the will had said “I leave my money to Jill, Joe, and Mary”. The final comma makes his intentions unambiguous! My advice to the old man is rather different: get a new lawyer. One that can write without ambiguity in a document where such a thing is important after all. “I leave my money in three equal shares to Jill, Joe and Mary” resolves the matter without recourse to extraneous commas.

Hilariously, Oxfordians point to this lovely example (allegedly written in The Times): “highlights of his global tour include encounters with Nelson Mandela, an 800-year-old demigod and a dildo collector”. Apparently, we all need an extra comma to persuade us that Nelson Mandela is neither an 800 year-old demigod nor a dildo collector. I disagree: anyone with the vaguest knowledge of Nelson Mandela knows this interpretation is wrong, so we don’t need the extra comma to clarify things for us. Furthermore, if the sentence had been written as “…encounters with a dildo collector, an 800 year-old demigod and Nelson Mandela”, would anyone be complaining? I think not… which goes to show that the Times writer’s intention was deliberately contrived ambiguity and humour.

Oxfordians also claim the avoidance of the comma is an unnecessary complication of the rules: you stick it between other items in the list. Why not use it at the end, then? To which the answer is two-fold: you don’t use it because the word “and” (or “or”) is acting as a list separator anyway. So it’s redundant. Moreover, there is no complication of rules taking place in any case because the very, very simple rule actually being applied is: “never stick a comma before a conjunction”. (Like all good rules, I’ll allow an occasional exception now and again -I’m generous like that- but the very strong emphasis is on “occasional”).

Rogers’ Corollary to that rule is: if you ever find yourself feeling that on this occasion it’s necessary to break the rule, re-write your sentence. You will be tempted to break it because the sentence reads as if there is some ambiguity or other that the additional comma will resolve. But this simply means you’ve been ambiguous in your writing and it’s your writing that needs to improve, not the rule of punctuation that needs to be broken.

Unfortunately, the serial comma is (apparently) taught as standard in the United States, so I’ve now just annoyed at least half my readership, such as it is; but there you go. Sometimes, these things need to be said!

If my readers could also absorb one other rule of grammar as well before they depart these pages in high dudgeon, I should be most grateful: the words “could”, “should” and “would” (see what I didn’t do there??!) are never, ever, ever followed by the word “of”. How we’ve managed to produce an entire cohort who think transcribing the sounds they make counts as writing, I have no idea, but “he could of done that if he’d wanted to” is an abomination that says to me, via megaphone, “I am either as thick as two short planks or can’t be bothered reading over what I’ve written to see if it makes sense before sharing it with you”. In either case, it’s a dumb thing to admit to.

Irregardless isn’t a word, either.

And if you are going to write up and say “It doesn’t matter, because language is organic and an ever-changing thing and you shouldn’t get too uptight about it; learn to love the evolution”, all I would say in advance is: it is only ever those who are incapable of sticking to rules that seem to think the rules don’t matter. I’ve never heard a legally-sober driver complaining that the drink/driving laws are a bit too strict or inflexible, for example. I have, however, watched plenty of busted drivers saying, ‘but I was only half-a-drink over!’, amounting to a plea to have the limit bent a little in their case because ‘it’s only fair, innit, guv’.

Ah, automation! Where would we be without it? (It is a little-known fact that the total mileage of railway line in England did not exceed the total mileage of canals in England until 1861 -some 80 years later than is commonly supposed, and proof that the Industrial Revolution never really happened -except in Germany, funnily enough. Britain, though, had an Industrial Evolution).

Anyway, I digress. Automation is good, and it helps produce nicely-standardized things which are therefore more predictable and more stable than their chaotic hand-built equivalents.

This goes for operating system installs, too.

Having got Gladstone doing his thing to hand-built servers, ensuring a level playing field when it comes time to install Oracle; having then got Palmerston working overtime to do the same thing for a Kickstart-automated server build; it is now time for me to introduce Portland: the script you run to standardize and automate your RCSL builds when you don’t want to install Oracle.

Perhaps being a little more positive, I should say that Portland is the script to run when you want the Gimp, VLC, Eclipse, Java, Apache Directory Studio, Opera, Rhythmbox, Handbrake …and all those other good productivity programs (like Solitaire and Chess)… added to an otherwise standard and minimal RCSL server installation. Preferably without all the hassle and confusion that the bazillion-and-one ‘how to do a perfect desktop’ articles out there seem to want to inflict on you!

Portland turns RCSL servers into usable desktops, in other words (including switching from OpenOffice to LibreOffice).

I particularly like it because it makes continued and almost-exclusive reference to mirror.aarnet.edu.au (and, indeed, changes the standard yum repositories so that they point there too). That mirror happens to be (a) close to Australians and (b) completely unmetered in use for us poor Telstra customers shmucks. For me, it means I can install RCSL servers until the cows come home without running up huge ISP bills or excess download consequences. For others, it will be a disaster, of course, since it will be using perhaps the remotest, non-free repositories in (their) existence. I’ll work on that for a future update! Meanwhile, you could do a global search-and-replace of the mirror URL before running it.

Provided you have a functioning Internet connection, you log on as root and invoke that script (a simple sh /portland.sh will do it).

20 minutes later, your server build will complete. including a nice set of top-panel application launchers for all the apps I consider to be vital (you might disagree with my choice of launchers, but that’s life!)

There are two interactive prompts throughout the entire process: Dropbox asks you if it’s OK to close all Nautilus windows (the answer is ‘yes’), and Calibre says it’s going to install to /opt (press [Enter] to accept). If I can get those two working in a non-interactive mode, I will.

There are still a couple of things I definitely won’t automate, the most significant of which is the installation of proprietary graphics drivers: I can’t find a reliable way of working out what graphics card is installed in a PC, so can’t automate the installation of the right binary blob. Install the wrong one, however, and the PC may well refuse to display anything at its next reboot. I’ve therefore decided to leave well alone for now!

But for the most part, I can now roll out a bundle of RCSL servers in double-quick time and have them all look as I want them to, without really having to do anything. And that’s the sort of automation I can live with!

Footnote 1: because I know literally tens of thousands of people will be asking, William Henry Cavendish-Bentinck was the 3rd Duke of Portland and served as British Prime Minister twice (in 1783 and 1807-1809, which is the longest gap between ‘turns’ of any British Prime Minister. If Margaret Thatcher wanted to achieve the same thing, she’d be making a fresh run for office right about now). The Portland Vase (see left, being peered at in the British Museum by yours truly, circa 2010) gets its name from the Duke’s family, since his mother bought it from the British Ambassador in Naples. Don’t ask how he came to be selling in the first place, though!

Footnote 2: Portland installs an, er, “eclectic” mix of software that suits my needs -which may not suit everyone or anyone else’s. I’ll take comments/suggestions on what ought/ought not to get included, if it helps make the script as a whole more useful. Suggestions need to be in the repositories (and thus a yum install away), or easily obtainable with wget (which rules out Oracle and VMware Workstation, for example).

This is what happens when you start up a server with a Scientific Linux 6.2 32-bit netinstall disk and expect it to be able to continue with a 64-bit network installation:

The clue is buried in the error text: Exec format error means “you’re trying to execute a 64-bit executable when I was expecting 32-bit ones, so that format/bit-architecture is wrong”.

The references to “anaconda failed” and “backtracing /lib/lib.s.6″ are just red herrings, really, potentially diverting your attention from the real issue: You can’t mix your architectures. If your Kickstart Server is dishing up 64-bit distros, you need to boot with a 64-bit netinstall boot disk. Obvious, really, but I’d never accidentally mixed-and-matched before, so I never quite knew what to expect.

Am happy to report that Scientific Linux 6.2 is properly and finally out, thus completing the ‘set’ of RHEL 6.2-compatible freebie distros. (CentOS 6.2 was released just before Christmas).

As a content user of the Scientific Linux 6.1 release on my desktop since around the start of December 2011, the new release means either I have to sit there pretending I don’t care about having the latest and greatest, or I can just upgrade and be done with it. I rather suspect the latter will be the course I take!

We are not aware of any other large males around here suffering similar amounts of ripped-out fur, scratches and blood, so maybe he had a run-in with something else (for example, a fox). Of course, there’s a reason the old boy stands up for himself… and here’s the latest such reason:

Welcome to Graham, still in Franklin’s pouch, though with a good deal of curiosity about the outside world, and barely a hair on his head!