Installing Oracle 10g on Ubuntu Hardy Heron

I'm now working in a client site. One of the differences is that each development desktop bears a smug "Ubuntu - Linux for human beings" sticker. This is the first time I have really tangled with Linux. I'm not going to risk the wrath of Verity Stob by detailing my journey into the heart of the penguin. But I thought it would be worthwhile documenting my experiences with installing Oracle 10gR2 on Ubuntu 8.04 (AKA Hardy Heron).

Ubuntu is touted as a user-friendly flavour of Linux, and certainly the graphical desktop is welcoming to people used to Windows. However things get pretty gnarly pretty quickly as soon as you want to do anything off piste. And Oracle is not supported on Ubuntu ( the supported distros are Suse, RHEL, Asialux and Oracle's own Unbreakable Linux) things go very off piste indeed. In the absence of official documentation we're thrown on the resources of the internet. There is lots of information out there - the sort of people who love Linux are the sort of people who love the web - but it is often written with a presumption of familiarity in Linux. In amongst the shedloads of helpful advice there are opaque sentences such as this): "to install lesstif2 you will need to use 'Adept' to enable the universe repository or edit /etc/apt/source.list" (unfairly quoting Todd Trichler out of context). I know I should have devoted myself to studying the Linux architecture before I started but I really needed to install Oracle now.

The first guide Google turned up was Luca Mearelli's Installazione di Oracle 10g su Ubuntu Linux which as you might have guessed is written in Italian. I found this quite distracting as anything in that language sounds like opera.

Madame Butterfly. Act 2. Scene 1.Whilst she awaits the return of Lt Pinkerton Koko-chan amuses herself by installing Oracle on different Linux distros. She sings the aria Impostazione dei parametri del kernel.

It was the setting of kernel parameters which drew me up short. There was no explanation (and even if there had been, my Italian would not have been up to translating it). Should I be changing kernel values on the say-so of a random Google hit?

So I surfed a bit more and found Installing 11g on Ubuntu Hardy Heron by Pythian's Augusto Bott. This article is so good that it has been ripped off by plagiarising sites. Augusto's guide included the same changes to the same kernel parameters as Luca's guide but it included explanations (through a link to his earlier article about installing Oracle 11g on Ubuntu Feisty Fawn). Although he wrote his guide for 11g it works just as well for 10g. There were still a few things which caused me some puzzlement and I will discuss those below.

I wasted a lot of time trying to install 64-bit Oracle (because we have 64-bit desktops). I could download it and run the Installer but I couldn't get OUI to link the database packages. The problem is:

  INFO: /usr/bin/ld: skipping incompatible 
/u01/app/oracle/product/10.2.0/db_2/lib/libsql10.a
 when searching for -lsql10

After a couple of hours fruitlessly downloading further packages I gave up and tried 32-bit, which worked first time without a hitch.

Annotations for Augusto Bott's guide

Step 3 applied because I was installing on the Ubuntu desktop. The gotcha is in the innocuous statement "You will have to restart your Xserver for this change to take effect." The Ubuntu desktop environment is X so the simplest way of doing this is to logout and login again. However I only discovered this fact after I issued the following command in a terminal window:

sudo /etc/init.d/gdm stop

Theoretically this should have just dumped me out to a text-mode command prompt. It didn't quite work that way so I had to resort to a hard reboot. Incidentally, another way to get out of the graphical environment is ALT+CTRL+F1 while ALT+CTRL+F7 gets you back again.

In Step 8 the wise man will take a backup of these files just in case. The suggested values seem to be common across all the install guides I read, so I trusted them :) Given that I was only installing locally I didn't bother setting the network parameters.

Step 9 is the actual install of the software. Oracle's Universal Installer is a Java applet, which is why we need to change the X Windows settings. If you have trouble with this step first make sure you have done Step 3 properly. However I still got the OUI-10025 message. More Googling threw up this piece of voodoo, which solved the problem:

clarkea@clarkea-desktop:~$ export DISPLAY=:0.0
clarkea@clarkea-desktop:~$ sudo su - oracle
Your account has expired; please contact your system administrator
su: User account has expired
(Ignored)
oracle@clarkea-desktop:~$ export DISPLAY=:0.0
oracle@clarkea-desktop:~$ xhost +
access control disabled, clients can connect from any host
oracle@clarkea-desktop:~$

If xhost or xclock works then you can run OUI.

The OUI wizard is slightly different in 10g. In particular it doesn't prompt for ORACLE_BASE and it defaults the paths to hang off your $HOME directory. You may want to change the location to /u01/app.

For me the actual install and linking process did not take nearly as long as Augusto suggests it will. Obviously our machines are a lot more powerful than his :)

Finally, if you apply the scripts Augusto suggests you will need to change the paths to point to 10.2.0 instead of 11.1.0.

To apply the 10.2.0.4 patch you just need to repeat the process.

Further References

Augusto's earlier article on installing on Ubuntu Feisty Fawn (which has some additional explanation of the parameter tweaking)

Oracle Release for 10gR2 on Linux note

Oracle Install Guide for RHEL4 and SLES9

Sun push the thread envelope

According to The Register the next iteration of Sun's Niagara chip will have 16-cores and 16 threads per core . Apart from the mind-boggling number of threads which will become available in an eight socket 1U rack, the licensing implications are a bit of a facer for Oracle. Soon a server with a single chip in it could incur a sixteen CPU license. At least at the moment if customers don't want to pay Oracle's multi-core fees they have the option to tear out some chips. But that's not an option with Niagara 3. Can Oracle seriously maintain a policy of selling licenses in bundles of sixteen?

UKOUG DE SIG 10-JUL-2008: the Agenda agenda

A while back I was discussing the difficulty of assembling a SIG agenda with a fellow chair. He had recently taken over his SIG and had been expecting to find a long list of volunteer speakers. He was disappointed by the reality, which is - alas - scraping around to get presenters. I was relieved it's not just the Development SIG with this problem. There's just over four weeks to go before the next SIG and I still have only three confirmed speakers, although I am confident of getting at least one more.

There are a number of different sources for speakers. The obvious one is Oracle itself. Every SIG has an Oracle buddy (strictly speaking they're called Ambassadors but the "buddy" tag stuck). Our buddy is the redoubtable Grant Ronald, who is a Group Product Manager in Oracle's Tools Division, which makes him the go-to man for Forms and JDeveloper. For the upcoming SIG Grant will be presenting on Oracle ADF 11g: New Declarative Development Features for Fusion . This will be an opportunity to see whether JDeveloper is yet approaching the productivity which Forms has offered us for more than fifteen years. Grant will also be giving us a brief overview of the new features in Forms 11g.

Oracle Consulting also provide us with speakers. It's a good place to get information on the newer Oracle products and technology. I'm still waiting for David Richards, the SIG's Consulting contact, to get back to me, but he usually comes up with the goods.

We do get offered sessions from vendors. I think it is an important function of the SIG to bring relevant products to the notice of our audience. But we have had feedback that people don't like sales-oriented presentations, particularly at SIGs because the single stream means there's no choice. This is why I am especially grateful to Peter Sechser from PITSS for offering a wholly technical presentation on turning an older Forms application into a SOA application which isn't a sales pitch for their PITSS.CON product.

Another, largely theoretical, source is people - actual users - who have volunteered to present. Unfortunately we get almost no such offers. I'm not quite sure why this is, but I suspect it is related to the reason why we get lower attendance compared to the DBA SIGs. Apart from anything else, the smaller audience represents a smaller pool of potential speakers. Then again, perhaps developers are just shyer than DBAs.

My final option is to phone a friend - or at least e-mail people whose blogs I like. In previous years I have buttonholed Rob Baillie and Tim Hall. This year's victim is Adrian Billington, who will be presenting on pipelined functions in PL/SQL. Adrian is a knowledgeable practitioner of PL/SQL so I'm hoping to learn lots from this one. I also approached another Oracle ACE for a presentation: I've still got my fingers crossed but I fear this one's going to fall through.

Putting the agenda together is a tough task, I try to balance the topics between the firm favourites (i.e. Forms) and the new (Java, ApEx). I like to include at least one unfamiliar topic in the mix (PHP, .Net, Rules Engines). But most of all I think it is my duty to ensure that we have presentations from different sources: I could easily have five sessions from Oracle employees but it is meant to be a User Group, not a branch of Oracle marketing.

So, if you're an Oracle developer based in the UK please come to the SIG. It's in Blythe Valley Park (near Solihull) on July 10th 2008. It's open to all comers (it's just more expensive to get in if your organisation doesn't have a UKOUG membership). But also, please consider presenting at the SIG. I admit it's not as glamorous as the annual conference but it is still a worthwhile and rewarding experience. If you are interested please contact Julius Kisielius at the UKOUG office.

Hanging around

I've just come back from holiday in Kos. One of the modern challenges of going away is figuring out how to hang up a pair of trousers in the hotel wardrobe. This apparently simple task has become more complicated because of the replacement of old-fashioned hook hangers with two-part security hangers. The hook hangers could be easily removed from the rail in order to facilitate the hanging of clothes. With the new hangers we have to disengage the frame from the closed loop. In this hotel the connection was a ball-and-socket arrangement which required a technique like that required by those buzzing wire steady-hand games at school fairs. The equivalent of the dreaded buzz is your trousers slipping off the frame and falling in a heap on the wardrobe floor. It is particularly difficult in a crammed wardrobe (my wife is a firm believer in "way too many" being better than "too few" when it comes to packing holiday clothes).

As a hotel guest, my user requirements for a clothes hanger are:

1. hanging my trousers;
2. simple to use.

However, guests are not the only stakeholders. The hotel owners also have a set of requirements:

1. allow guests to hang their trousers;
2. discourage guests from taking hangers home.

Clearly the first requirements on both stakeholders' lists are in alignment. But whilst the hook hanger satisfies the guest's second requirement it doesn't satisfy the owner's second requirement. Whereas the closed loop hanger meets the owner's second requirement but fails to meet the guest's. In situations like this, when two requirements clash it is usual for the customer's requirement - the bill payer - to trump the user's requirement.

And that's why almost universally hotels now have security hangers in their wardrobes. At least in this situation the user's top requirement - hanging my trousers - has been implemented in a convenient fashion. The hotel could just have provided a wardrobe rail and told us to bring our own clothes hangers (i.e. as a self-service application).

Idle thoughts of a idle coder

Brian Tkatch has launched a thread on the PL/SQL forum about enhancements to SQL which would just basically save some typing: Things i wish SQL supported. The lazy man's list. This is quite a revealing thread, because it is always interesting to see what shortcuts people would like to take. It's a bit like peeking inside the medicine cabinet in other people's bathrooms (not that I would ever do that).

My personal wish is for:

select * {-empno} from emp;

That is, select all columns from the EMP table except EMPNO. This would be particularly useful for querying tables with BLOB columns in SQL*Plus.

As the thread as grown it has turned into a discussion of SQL theory ("conceptually, (using Venn diagrams) the tables/views are the circles, and the predicates define in what way the circles overlap") which requires too much concentration. The thread was supposed to be about laziness!

The patron saint of programmer laziness is Larry Wall, the inventor of Perl:

"The virtues extolled for Perl programmers are laziness, impatience, and hubris. Together, these admirable characteristics have led to the creation and use of many publicly accessible Perl modules. Because of laziness, programmers would rather write modules than repeat a procedure over and over (and would rather use modules written by other people than write new code from scratch). Because of impatience, programmers write consolidated code that is flexible enough to anticipate their future needs. And because of hubris, programmers share their triumphs with the rest of the Perl community and continually tweak their modules until they're the best they can be."

The problem with proactive laziness is that it can be hard to estimate how much effort will be saved later by putting in some extra effort now. Plus, writing automating utilities and code generators can just be a seductive form of procrastination. It feels like work but we aren't moving forwards. In the end we spend so much time sharpening the axe that we never get around to cutting down the tree. So the trick is to only automate the things we know it will be worth automating. This means doing something the plain way at first. Only when we get to the second or third cut'n'paste should we consider whether we need a parameterised module instead. The important thing is to automate early, in order to derive the maximum return on the work.

I am currently practicing cut'n'paste programming in a test data generator. I could refactor my code to drive off an array but re-editing my package to populate a collection will be a PITA. I should have done it some time ago, but I failed to realise just how many additional datasets I was going to need. At this point the ROI on the automation is quite small. So I have chosen to continue paying the find/copy/edit tax rather than spending half a day to figure out a better way of doing things. In the long run I will have expended more effort but in the meantime I keep making progress towards the main goal.

Update

Over on the Artima site Jeremy Meyer has written an article on Why it is better to be lazy.

Esprit de cores

Oracle-L has been hosting an interesting thread on migrating to another (cheaper) DBMS. It seems like the company in question has not targeted a specific product yet, they just want a cheaper one. The entire thread has much to recommend it but I would like to highlight Mark Brinsmead's analysis of the definition of 'processor' in the Oracle License and Services Agreement, because it complements my post on licensing multi-core servers.

"[The OLSA] certainly adds a new wrinkle to SE licensing that I had not noticed until just now. Probably a lot of IT professionals, few IT managers, and even fewer lawyers, know the difference between a 'chip' and a 'carrier'. What's more, how many people *know* when they are purchasing a system with quad-core X86 'CPUs' whether the carriers in that system contain a single chip with 4 cores, 2 chips with two cores each, or four single-core chips. It makes little difference when purchasing the hardware (well, okay, it might make more than you think), but it can make a *huge* difference to your license costs and compliance."

Core blimey!

The Register reports on the latest developments in Oracle's multi-core licensing policy:

"Oracle, we hear, is charging a factor of .75 for Sun's T2 and T2+ systems even though they're running at about the same speed as the T1s. The major difference with the new chips is their support for more threads and the fact that the T2+s can go into multi-socket servers making them more useful for, er, databases. And by 'more useful' here we mean 'useful at all' since no one in their right mind would have thrown the older T1 systems at Oracle.

The .75 T2 factor comes as quite a shock to Sun customers who have upgraded their hardware only to have the Oracle tax man come along and tell them that the solid price/performance they were expecting via the hardware will be eroded via the software."

Of course Oracle is entitled to price its licences however it wants. But trebling the fees for customers who move to a different server with the same number of cores as their old kit doesn't seem like the best way to maintain loyalty. It also alienates Sun, who have previously used the favourable licensing terms to sell T1 boxes.

I disagree with Ashlee Vance's conclusion that Oracle's licensing policy will drive customers into the arms of IBM. If you're going to take the pain of moving to a new DBMS and if licensing costs are the main driver then free is a lot more attractive than DB2, no matter how sane IBM's fee structure. In his blog Sun CEO Jonathan Schwartz recounts a pertinent story from a recent visit to a large commercial institution:

"We had just closed the acquisition of MySQL, so before I wrapped up, I asked, 'And would you like a quick update on the newest addition to our family, MySQL?'

The CIO responded categorically with 'we don't run MySQL, we run [name withheld to protect the proprietary].' The CISO said, 'We can't just let developers download software off the net, you know, we've got regulation and security to worry about.' The CTO smiled. Everyone else appeared to be sitting on their hands. I was going to leave it at that. Thanks for the business.

Until a (diplomatically) assertive Sun sales rep piped up, 'Um... no, I connected with a buddy of mine over at MySQL, and had him check - you've downloaded MySQL more than 1,300 times in the last twelve months.'

After a profoundly awkward silence, one of the individuals from their internal development team piped up, 'Actually, everybody uses it. Why bother hassling with license agreements when MySQL's got you covered. We're stoked you bought them.'"

As we all know, purchasing decisions are frequently made on the basis of which product is the most cost-effective rather than which product is the best. The danger for Oracle is that Sun's purchase of MySQL lends the free database a lot more credibility than it had before. If Oracle won't give Sun any more sweetheart deals then Sun has more reason to start trumpeting the price-performance advantages of running MySQL on their new multi-core boxes. Oracle has made a lot of play about the benefits of free when it adopted Linux as its OS of choice. So it's not like it doesn't understand the allure. Which is probably why Oracle has restricted the price gouging to the Enterprise Edition licences whilst the Standard Edition is charged per socket. The jump from the SE feature set to MySQL is probably a lot less daunting than the equivalent jump from EE.

Footnote

Over at the Service Architecture - SOA blog Steve Jones considers the implications of the Register article for Software licensing in a virtual world.

Footnote #2

At the time of writing, the Multi-Core Processors - Impact on Oracle Processor Licensing document hurls an 404 error. This may indicate that Oracle are updating it to reflect the new chip sets.

Footnote #3

Also worth a read is the Register's recent interview with Mark Shuttleworth, Ubuntu's rentaquote-in-chief. Amongst other things he makes this prediction about Oracle's Unbreakable Linux strategy:

"Oracle will find themselves in a position where, if this business is successful or strategically important to them, they will need to fork or buy Red Hat. They will do one of those things within three to five years."

.

Jean Prouvé: The poetics of the technical object

I confess I had never heard of Prouvé before I came across this exhibition at London's Design Museum but the title grabbed me. If I had have known how interesting and relevant Prouvé was I would not have left it to the last minute to go. I think he's not better known outside of France because he mainly worked on municipal projects.

He never formally trained as an architect; so although he did work on the design of buildings, his is not the name which tends to be associated with them. His most iconic designs are chairs. But these are chairs for university halls of residence, works canteens and classrooms, not the sort of chairs which grace Notting Hill living rooms.

Although he came from an artistic background Prouvé started out as an artisanal blacksmith in 1919. He quickly moved from wrought ironwork into steel and aluminium, but he always remained rooted in the practice of working with materials. He designed through trials and testing of concepts.

"...one should not sketch out utopian projects, because evolution can only result from practical experience."

This commitment to evolution is demonstrated by a display of Standard Chairs, variations on a theme produced by Prouvé's workshop over the course of two decades. The basic shape and configuration of Chair No.305 is not markedly different from Chair No.4. There are minor tweaks, and there are variations in material: wood, steel or aluminium, plain or lacquered. The biggest adaptation was the collapsible Standard Chair.

As an artisan and then a factory owner he understood the properties of wood and metal and their appropriate usages. Designers and architects more driven by the need to appear avant garde tended to get carried away with the thrill of new materials and looking modern. Prouvé appreciated that good design had to come from functional success: no matter how striking it looks, a chair is no good if it is not comfortable to sit in. An example is the Solvay table, which is made of wood bolted together with lacquered steel. The engineering of the table is not hidden, it is part of the aesthetic, but neither is it fetishised.

Prouvé was a early adopter of the concept of design patterns. He assembled a dictionary of structures which could be reused in different situations and scales. The crutch - a asymmetric Y shape - which supports the roof of the Pump House at Evian re-appears in the design of an armchair. He devised a roof made of single curved pieces of steel. These shells were light enough for two men to slot them together. At a larger scale this shape could be rested on the ground to form vaulted halls. One favourite shape, a elongated pentagon, appears repeatedly in his work: as the back legs of the Standard Chair, as the legs of various tables, in the cross-section of a table top, even as the handles of a sideboard.

I tend to be wary of attempts to draw parallels between our industry and branches of engineering or architecture, as these strike me as attempts to lend software development a spurious sense of discipline. Just calling it "software engineering" does not make writing a program as rigourous an activity as building a motorway flyover. However, with his commitment to iteration, re-use, modification and adaptation, and his championing of practice over theory it is hard not to regard Jean Prouvé as the Godfather of Extreme Programming.

There's more

The other exhibition at the Design Museum featured lots of modern work. One of the most striking exhibits was a chair "sketched" by a Japanese design house called FRONT. Their designers have developed a mechanism for designing furniture through motion capture and then rendering the designs using extruded plastic. Unlike Prouvé's work you probably wouldn't want to sit on the chair or rest a cup of coffee on the table but the process is fascinating to watch.

Big zips

I have just downloaded the database 10.0.2.4 patch for Windows 32bit. It is a zip file which weighs in at a cool 1,034,080,256 bytes. Opening it up reveals a single directory called Disk1. Well I suppose it's one way of maintaining the fiction that Oracle installs fit on a single disk. This is the first time I have come across a patch which is too big to fit on a CD-R. It's a pain, because DVD burners are not yet standard kit and certainly all our servers still just have CD drives.

Comparing CHAR values

Here is a table with a single row of data.

SQL> create table my_tab (c2 char(2))
  2  /

SQL>  insert into my_tab (c2) values ('Y ')
  2  /

1 row created.

SQL>

Which of the following queries will match that row?

select * from my_tab where c2 = 'Y'
/

select * from my_tab where c2 = 'Y '
/

select * from my_tab where c2 = 'Y  '
/

If you said all three you get a cigar (providing you live in a jurisdiction where such infernal devices are still permitted).

SQL> select * from my_tab where c2 = 'Y';
c2
--
Y

SQL> select * from my_tab where c2 = 'Y ';
c2
--
Y

SQL> select * from my_tab where c2 = 'Y  ';
c2
--
Y

The reason is due to Oracle's rules for comparing blank-padded datatypes. If the two values are of differing sizes Oracle pads the smaller variable with the requisite number of blank spaces. Obviously it is documented.

On the whole I think this is a boon - CHAR columns are a pain in the neck at the best of times, without having to worry unnecessarily about additional RPAD calls. This is unfortunate if you are relying on 'Y ' being different to 'Y'; but if your application depends on trailing spaces for data integrity then you probably have bigger problems.

Hacking a pacemaker

In the last few days I have read a couple of pieces which quote the William Gibson dictum "the future has already arrived, it’s just not distributed evenly". Confirmation that we are indeed living in a science-fiction story arrived today in the shape of a message on the Full Disclosure list: hacking a pacemaker. Security researcher Gadi Evron cites a report in the NYT.

" The threat seems largely theoretical. But a team of computer security researchers plans to report Wednesday that it had been able to gain wireless access to a combination heart defibrillator and pacemaker.

They were able to reprogram it to shut down and to deliver jolts of electricity that would potentially be fatal . if the device had been in a person. In this case, the researcher were hacking into a device in a laboratory. "

What a great way to assassinate somebody....

Code quality metrics

Whenever two or three developers gather together they will argue over the best tool for editing code. Once they've chewed the spearmint out of that they will commence a heated discussion on ways to measure code quality. But I think this cartoon by Thom Holwerda pretty much nails it.

Ellison on hostile acquisitions

Fake Steve Jobs links to a piece in the New Yorks Times by Andrew Ross Sorkin on Larry Ellison's thoughts about the recent spate of hostile acquisitions (many of them by Oracle). Larry demonstrates his knack for a well-turned line. I have never worked for Big Red so I am not in a position to comment on his assessment of Oracle's friendliness. But he has a very sound view of sales and marketing people:

“What makes you think that engineers are happier, for example, working at PeopleSoft rather than at Oracle?” he asked. “Who says it’s unfriendly?”

The people who are most likely to scream and moan about an acquisition are the marketing people and salespeople — who, by the way, are the most dispensable, he said.

What... is your favourite colour?

Yesterday somebody called Pradeep posted a question in the OTN PL/SQL forum asking for careers advice. He is a VB programmer working in a Microsoft code factory (in India I would guess). He wanted to know what the Oracle job market was like, because he wanted thinking of training as an "oracle developer".

The reaction from some of the forum regulars was dismissive. Here is a sample:

"There are so many career/job opportunities in IT that to ask on a forum like this for career direction advice is just ... Well, just not a good idea.

But put yourself in Pradeep's position. (Note the following is an extrapolation: I don't really know his circumstances). A combination of the commoditization of IT and outsourcing has produced software sweatshops, where you, the developer equivalent of a sharecropper, churn out code chunks to be assembled into systems elsewhere. It's repetitive, stultifying and without much room for personal growth. Even if you do hear of a better job you know you are surrounded by dozens of other sharecroppers who stand just as much chance of getting it as you.

So Pradeep has thought to himself, Oracle is a famous company but he doesn't know anybody who works as an Oracle developer. That means he is going to face much less competition for any Oracle job which might arise. You might find this optimistic but it is thinking outside of the box. He then shows further initiative by posting a question about Oracle careers on the OTN forums. Because he thinks that the people who answer questions there will be helpful - which they are - and they will have the relevant knowledge - they all have careers in Oracle development.

What he hadn't anticipated was that quite so many people regard the PL/SQL forums as a suitable place for rehashing Monty Python jokes and discussing antiquated programming languages but not for offering careers advice. It's a funny old world.

Openness: CMG's unsecret sauce

The CMG Wake last Friday was a lot of fun. Estimates have placed attendance at around the 700 mark, which made the pub extremely crowded and rather noisy.

Whilst queuing at the bar I got talking to someone who left the company in 1987. He put his finger on the unique thing about CMG. He now lectures in Business Studies and cites CMG to his students as an example of openness in business. In CMG everything was open: the personal files hung in open cabinets in the office. You could read anyone's file - from their job application form to their latest staff appraisal. Yes, including salary.

That's the bit which gets people. When Brian Behlendorf talked about introducing open source principles in general working practices at OOW2K6 one of the questions afterwards was whether such openness, if taken to its logical extreme, would result in everybody knowing how much everybody else earns.

Well, why not? Partly it's just embarrassment - many people would rather discuss their medical conditions or their bedroom fantasies than their pay-packet. But the main objection seems to be "I wouldn't want my colleagues to know how much I earn". The objectors are presuming that they earn more than their co-workers. I think some of those people would be very interested to discover that all their colleagues get paid more than them. And they'd want to know why.

The accepted wisdom in CMG was that open salaries promoted fairness: the management couldn't play favourites because anybody could ask them "How come Joe Soap gets paid 10K more than me?" and have to provide an answer. In practice there were probably all sorts of anomalies - especially in pay rates across different divisions - but it felt fair. I know people who work in law firms and finances houses where discussing your salary with co-workers is a disciplinary offence. Most companies aren't that fierce but very few companies are as open as CMG was.

I don't think openness was the only thing which made CMG special, but it was one of the reasons why so many people feel affection for the company, even if they did stop working for it over twenty years ago. Although I'm sure the promise of a free bar helped too.

Update: 06-MAR-2008

Currently Dilbert has an amusing take on payroll secrecy. I think this link will eventually break, so get it whilst it's hot :D

Better late than never

There is a new white paper on OTN called Upgrading from Oracle Database 9i to 10g: What to expect from the Optimizer. I am working on a 9i project which is considering upgrading to 10g (quite possibly the last such in the world) so I think we'll find it useful. Of course, it might also be helpful to people who have already done the upgrade and are experiencing performance problems in their new environment.

Farewell to CMG

Thirteen years ago tomorrow I joined a consultancy company called CMG. When I went for the interview I'd never heard of it but the interview process impressed me. Fortunately things worked out well and I've been with the company ever since. In 2003 we merged with another consultancy, Logica, to become LogicaCMG. Until Wednesday, when the name was reverted back to Logica.

This makes sense in many ways. More people had heard of Logica than had heard of CMG, although the separate companies had been of equivalent size. And it was a bit of a mouthful - even I had taken to calling us plain Logica. But it is a sad moment. CMG was a special sort of company; people define themselves by the fact that they worked for CMG, even if they left the company years ago. And that's why several hundred ex-CMGers - because we are all ex-CMGers now - are descending on a pub in London to mark the passing of the name in the traditional CMG style. Cheers!

The Guru's burden

Those of us who answer questions in forums or add a comment to a blog will recognise the truth of this cartoon from the ever-reliable xkcd comic.

Data modelling and other dying arts

Martin Widlake sent me an e-mail after last months UKOUG Unix SIG:

"At my presentation at the UKOUG Unix SIG yesterday I suggested that formal design was almost dead, replaced with organic design and asked if anyone still used ERDs. No one did. Not one.
This kind of bothered me. Does it mean that just ERDs are dead? Or that a room full of DBAs is a room full of people who do not do systems design (I am just as shocked by that if it is true)? Or maybe formal design is a dead concept."

There's two points here. The first, the utter lack of DBAs who do data modelling tasks, doesn't surprise me in the slightest. This is the nature of the modern DBA's job. Production DBAs look after live systems: they don't design them. Increasingly people are becoming DBAs straight out of college. These guys have never worked as developers and probably never will. The older geezers, who followed the more traditional route of starting out as programmers and progressing into the DBA role, probably haven't worked on development projects in years.

Also the IT landscape has changed. Even ten years ago many organisations had one or at most a handful of databases. It was possible for a DBA to be responsible for a single database; knowing its purpose and its value to their organisation was part of the job description. These days it is not uncommon to find DBAs working in teams looking after dozens even hundreds of databases. Furthermore the production DBA may well work for a different company (i.e. an outsourcer), possibly in a different continent from the users. Their relationship with the databases they administer is mediated through SLAs and ITIL compliant procedures. So they have little incentive and even less time to appreciate the databases under their care. Indeed, given the prevalence of Sarbanes-Oxley and similar pressures, production DBAs will be increasingly encouraged to remain in ignorance. A production DBA is somebody who knows the metadata of everything and the business purpose of nothing.

Of course, there are DBAs who do work on development projects. They are often combine the role with that of being a developer, especially on smaller projects. They often get called database engineers rather than DBAs. And production DBAs tend to regard database engineers as being developers not "proper" DBAs. I have been a database engineer on sites where I wasn't allowed the SYSTEM or SYS passwords for my project's development database. I would bet that everybody who goes to the Unix SIG is a production DBA.

The second question is whether anybody uses entity relationship diagrams, or more broadly, whether anybody still does logical data modelling. I can't answer this one from personal experience. I've been on a data warehouse project for four years now: I only deal in existing schemas. Even when I have done design it has been for ETL infrastructure and similar, so I have leapt straight to physical tables. As I started out with SSADM I do feel a bit guilty about this. Although I must say I haven't exactly missed drawing Entity Life History diagrams.

Anecdotally, there does seem to be a general decline in the practice of data modelling. There were hardly any presentations on modelling at the last UKOUG conference or at Open World 2007. The Modelling and Design is one of the smaller UKOUG SIGs. The ODTUG Designer listserver has flurries of activity but since Oracle announced the death of Designer it has - understandably - experienced a major drop in traffic. There are occasional questions about data modelling in the OTN forums, but these are frequently from students rather than practitioners. It is depressing to consider that the most commonly referenced data model seems to be the fundamentally flawed Entity-Attribute-Value. My last piece of circumstantial evidence is that the Oracle blogosphere rarely features posts about data modelling. The only blog I know which regularly discusses data modelling is The Database Programmer and even Ken Downs only talks about tables.

Of course people are doing system design. There's lots of design about but I would guess that it all happens in UML. So the majority of logical data modelling these days produces class models rather than ERDs. The physical database design stage is much more likely to consist of ORM than mapping entities to tables. Now that's not the sort of party you invite a DBA to, because you just know they're going to glower in the corner, drinking heavily and muttering to themselves. So the mappings and the database design will be done by middle-tier developers. Our communal prejudices tell us this is unlikely to produce a correct and peformant database design, not least because projects which use such an approach tend to make a fetish of database agnosticism and platform independence. So in the long run we might see a resurgence in data modelling, as part of the tool set for rescuing poorly performing class models.

As a tangent, Dominic Delmolino observed in a recent blog that

"many of the people I’ve been interviewing seem to be taken aback by a few simple SQL questions, telling me that DBA’s (sic) don’t do SQL."

Again, why is this surprising? SQL knowledge is going the way of data modelling for production DBAs. There is a whole raft of GUI administration tools - Quest Spotlight, BMC Patrol, Embarcadero, OEM, etc - whose sole purpose is to allow DBAs to monitor and manage large numbers of databases without using the command line and without knowing SQL. Again this is inevitable given the landscape I described above. Old skool DBAs - the ones who started out managing a single database - will have accreted a personal library of SQL scripts, shell scripts and utilities which do all these things. But people starting out now will probably find themselves operating in shops with dozens of databases and no time to roll their own tools. If they are lucky there will be an old lag to pass on some skills and some scripts; more likely there will be a shrink-wrapped GUI tool. Besides, remember that Oracle Enterprise Manager was introduced in Oracle7: it is perfectly feasible for somebody to describe themselves as an experienced DBA who has never administered a database in any other way.

Scoping with SQL Types

The scoping rules for function calls are quite clear. Given a package with a function which has the same name as a standalone function, another function in that package will call the packaged function not the standalone one:

SQL> create or replace function toto 
  2      return varchar2 
  3  as
  4  begin
  5      return 'TOOTING';
  6  end toto;
  7  /

Function created.

SQL> create or replace package a as
  2      function toto return varchar2;
  3      function tata return varchar2;
  4  end a;
  5  /

Package created.

SQL> create or replace package body a as
  2      function toto return varchar2 
  3      as
  4      begin
  5          return 'KANSAS';
  6      end toto;
  7      function tata return varchar2 
  8      as
  9      begin
 10          return 'We''re not in '||toto||' anymore';
 11      end tata;
 12  end a;
 13  /

Package body created.

SQL> select a.tata from dual
  2  /
TATA
-----------------------------------
We're not in KANSAS anymore

SQL> 

The rules apply the same way if we're working with an object rather than a package ....

SQL> drop package a
  2  /

Package dropped.

SQL> create or replace type a as object (
  2      attr1 varchar2(20)
  3      , member function toto return varchar2
  4      , member function tata return varchar2
  5  ) NOT FINAL;
  6  /    

Type created.

SQL> create or replace type body a as 
  2      member function toto return varchar2 
  3      as
  4      begin
  5          return attr1;
  6      end toto;
  7      member function tata return varchar2 
  8      as
  9      begin
 10          return 'We''re not in '||toto||' anymore';
 11      end tata;
 12  end;
 13  /

Type body created.

SQL> set serveroutput on
SQL> declare 
  2      my_a a := new a('KANSAS');
  3  begin
  4      dbms_output.put_line(my_a.tata);
  5  end;
  6  /
We're not in KANSAS anymore

PL/SQL procedure successfully completed.

SQL> 

However, there is a gotcha: the scoping rules do not work that way when our type inherits from a super-type....

 
SQL> create or replace type b under a (
  2      overriding member function tata return varchar2
  3  );
  4  /    

Type created.

SQL> create or replace type body b as 
  2      overriding member function tata return varchar2 
  3      as
  4      begin
  5          return 'We''re not in '||toto||' anymore!!!';
  6      end tata;
  7  end;
  8  /

Type body created.

SQL> declare 
  2      my_b b := new b('KANSAS');
  3  begin
  4      dbms_output.put_line(my_b.tata);
  5  end;
  6  /
We're not in TOOTING anymore!!!

PL/SQL procedure successfully completed.

SQL> 

The solution is quite straightforward: use the SELF keyword to make the scope explicit.

 
SQL> create or replace type body b as 
  2      overriding member function tata return varchar2 
  3      as
  4      begin
  5          return 'We''re not in '||SELF.toto||' anymore!!!';
  6      end tata;
  7  end;
  8  /

Type body created.

SQL> 
SQL> declare 
  2      my_b b := new b('KANSAS');
  3  begin
  4      dbms_output.put_line(my_b.tata);
  5  end;
  6  /
We're not in KANSAS anymore!!!

PL/SQL procedure successfully completed.

SQL> 

I admit I am not clear about the rules for using SELF. Sometimes it is compulsory, sometimes it is optional. So it's just easier to always include it whenever we reference anything inside a type body.

NB: I ran these tests on 9.2.0.6, if that makes any difference.