Tuesday, November 27, 2007

Secret Squirrel on the move

I've finished, for now at least, my voyage into Teradata, and am half back in the Oracle world.
I say half because I am also using SQL Server (for the first time).

I'd recommend any Oracle developer try to familiarise themselves with some of Oracle's ANSI-standard constructs, like COALESCE instead of NVL, CASE instead of DECODE and the ANSI OUTER JOIN syntax. It gives you a bit of a headstart when attacking these alien worlds. Despite Larry's intentions, at some point you WILL come across a non-Oracle database.
Even ANSI isn't perfect though. Simply updating one table from another is a major switch between Oracle and SQL Server and there's no universal syntax for it. Plus I have to keep reminding myself that Oracle is practically unique in not differentiating nulls and empty strings. Even EnterpriseDB doesn't go down that prickly path. Also collation and ordering has been an interesting challenge.

I'll also recommend Secret Squirrel. Okay it is actually SQuirreL SQL Client rather than the undercover cartoon rodent, but it was a secret to me. It is a Java-based cross-database query tool. While I've only used it for Oracle and SQL Server, it looks like it will cope with pretty much anything you'll come across, as long as you can find a JDBC driver for it. I should have tried it for Teradata.

Squirrel is more compact (at about 25Mb) than SQL Developer, and also only needs the Java Runtime, not the full JDK. It is also better at handling multiple sessions against the same DB server. [Note: I'm trying to use the term 'DB server' because 'database', in SQL Server and Teradata, is closer to what Oracle bods consider a schema/user.]

Squirrel isn't an IDE to the extent SQL Developer is, but then I never really used that for coding PL/SQL anyway, preferring to stick to SQL*Plus and my favorite PSPad editor (which I've got working under Wine in Ubuntu). It also doesn't have the level of Oracle specific support (eg built-in reports). The Explain Plan seems basic (not using some of the new columns), but I prefer DBMS_XPLAN.DISPLAY anyway.

It is also cluttered. That may be because I'm still working out what I use and don't use, and what I can turn off or hide.

Finally, as you'd guess from the SourceForge location, it is Open Source (Lesser GPL if that is important to you), and seems pretty active. The latest version has a nice built-in for copying data from one database to another (like the old SQL*Plus COPY).

Monday, November 12, 2007

Importance, Urgency and 'Noise'

About a month back, Tom posted that "It's all about the data", saying that "Applications come, applications go. They are secondary."

My own take is slightly different, because there's a difference between Importance and Urgency. I'll agree with Tom that in the vast majority of cases (and more so for database applications), the data is far more important than the application.

But urgency is a different matter. Urgency is about doing things. Getting a lottery ticket for tonights draw is urgent, because it has to be done today. However it is also pretty unimportant. It won't make a difference if I miss it, I'm not going to win anyway. But deciding on who to marry is important, although it is generally far less urgent. [If it has become urgent, there's probably nothing left to decide.]

For a business, it is all about continuity. They need to be able to keep on doing business. When the 'system' is down, management really doesn't care if the problem is in the data files, the Database Server, a mid-tier node or the end-user application. It just needs the problem fixed as quickly as possible. The data and the application are equally urgent, and, as links in a chain, they are equally important in those circumstances. A week, or even a day or hour, without the data or the application would both be catastrophic.

Generally speaking though, an application should be simpler to recover. You need the hardware and software, but it is less dynamic. It mostly doesn't really matter if it is yesterday's software or last month's. The data is constantly changing, so while having application and data backups are equally urgent when it comes to recovery, having a TIMELY backup is more important for data.

And finally, there is noise. The amount of discussion, meetings and general 'buzz' about a topic. Noise is all about emotion. People get emotional about the applications they use, THEIR applications. Apple Fan Boys, MS-haters and flame wars. You've seen it before. A clunky application generates lots of bad feeling, a smooth one gets praised. Business users generally aren't dealing with their own data, but that of the customers or suppliers. Incorrect data may make the job harder sometimes, but a bad application can make it harder all the time. People wll love, loathe and occasionally be indifferent to their application. People blogged about 'creating passionate users'. People don't get excited by data. Well not the sort of data I see anyway.

Noise has NOTHING to do with importance or urgency. We recently passed the anniversary of a young girl being abducted from her bed where she was sleeping with her two younger siblings. Not, in this case, Maddie McCann, but Rahma el-Dennaoui , who disappeared two years ago in Sydney. Since she was at home, there were no big media inquisitions about the competancy of the foreign police . Because she is dark-haired, there's been no hunting parties to far off countries sparked by mere sightings of blonde girls . No soccer stars , tycoons or famous authors throwing around money and publicising it world wide. Different noise levels. Same level of importance.

So there may be more noise about applications rather than data. That doesn't reflect their relative importance. The whole data-warehousing/data mart/business intelligence movement reflects the fact that businesses are seeing the value in their data and putting money into realising that value. if we can just get them to pay the same level of attention to securing that data...

Now I'll let you get on and read the next dozen posts from OpenWorld

Thursday, November 01, 2007

Happy Halloween

Okay, it was yesterday here in Oz, but you guys in the US are just warming up to it, so I thought a halloween post was in order.

One of my namesakes happens to be a writer of horror stories (see Gary Myers) in the H.P.Lovecraft / Dunsany style. Thats sort of a fantasy style where any barbarian hero is likely to end up a gibbering wreck. I'd like to point out I didn't discover him by ego-surfing, but rather when my wife bought me a collection of short stories which included one of his (The Last Night of the Earth ). The book was the 'Azathoth Cycle - the Blind Idiot God' and its contents are listed here. The subsequent story, the Daemon Sultan, was written by Donald Burleson, a name familiar to many in the Oracle field. Okay, to spoil the story, the story's author was actually Donald R. Burleson and the Oracle personality has the middle name of Keith, according to www.dba-oracle.com/resume_don.htm.

The moral of this story is that, if you have a sufficiently large data set, coincidences will happen.

PS. One thing I don't get about Halloween is, we spend all year telling our kids not to talk to strangers, let alone accept sweets from them. Then they go off to strangers' houses DEMANDING them. I only had one visit last night, and I didn't recognise them (though perhaps my wife would have). They got a small chocolate bar though.
Then I took my two out, with a rubber mask each, to 'Nana next-door' who had a biscuit for them. But they like to visit her anyway.