Re: Databases and Instances

On Wed, 05 Apr 2000 23:46:51 GMT,
granta_at_nospam.student.canberra.edu.au (Fuzzy) wrote:

>
>Yes, I was simplifying things. And you go on to hit the nail on the
>head ... what happens when you (me, you, poor DBA's in general) get
>off-the-shelf software with said hard-coded names. I was probably
>being a little rough on Oracle ... but I'm sure Larry can handle it.

Be rough as hell, mate! Larry needs to be kept on his toes, else he'll stagnate. :-) Now your problem: yes, it's dicey.

Ahh, off-the-shelf software! ORACLE chased them up for so long and it's the very thing that's killing them...

All ORACLE had to do to avoid this was insist on a minimal set of standards for 3rd-party coders with an ORACLE certification program. Licen$e advantages to third party developers who passed their certification. Bingo, no more problems.

Instead, they insist on DBA certification. Like a 3rd-party DBA is gonna send ORACLE broke... They had to do it the stupid way. And get heaps of crap aps out there for every single one that's worth the wrapping paper.

I dunno why, but there was a "school of thought" a few years back in ORACLE development that said "code the table name as schema.tablename, it runs faster". Yes it does, but by such an infinitesimal fraction its really not worth the problems it creates!

Rates right up there with the aps that insist on installing themselves on the SYSTEM tablespace...

One way of providing a minimal amount of transparency is to trick the bastards:

Assume they coded their table name as CRAP.TAB What you need is to put TAB in another schema, say GOOD. It now becomes GOOD.TAB.
Now create user CRAP and inside it a synonym called TAB, pointing to GOOD.TAB.
App refers to CRAP.TAB (from any user) and gets in reality GOOD.TAB. Authorizations handled by roles, of course.

Need another database? SQL*Pus (!) script with schema name as parameter. Run it to create a new set of synonyms in user CRAP to point to another schema. Now you can use the app and access the other schema while you fix CRAP.

But it won't allow you to have two of them at the same time. Just give you some more flexibility. Not a complete solution. Blame Larry.

:-)  

>
>"Thingies"? That's a good one. I bet the M$ people reading this
>thread are turning red right now. :-)
>

;-) Let them choke, they've done enough damage to this industry...

>You mean you're not looking forward to running 58000000000 Win2000
>machines in some god-awful cluster to try and come close to what big
>iron was doing ten years ago? (yeah, that was a joke.)

10? You feeling generous? How's about 20? (joke)

>
>Mmm ... don't know enough myself about this, though it is there in
>version 5, 6 (and soon to be 7) of DB2 on UNIX and NT.

Didn't know that. As I said, it won't be difficult. What will be difficult is make it perform well in those environments. Due to the other missing features at OS level.

> OK,
>this isn't really a problem with the Oracle way of doing things per
>se, but the db/instance/schema limitations it has do make it more
>complicated to do things like upgrades, testing, etc. when you can't
>change things at the schema level.

You bet. A (simplified) way is what I indicate above, but it's not a final solution. You may have to consider the multiple instances here, I'm afraid. Or go to the next ORACLE user's group conference and help me belt the crap out of the Larries! :-)

>I'm not sure how they're getting around it, but the DB2 instances I
>run on NT and UNIX (Solaris, Linux) do provide for this. It may be
>some fancy footwork within the instance itself, to take one logical
>block of virtual memory, and the add another "virtual partitioning"
>over the top of it to give the illusion of what we see ... or
>something far more sophisticated ... can't say I've really bothered
>with how it does it, I just smile knowing that it does.

Yeah, they must be doing some fancy footwork... The thing is: the mechanism itself isn't hard. What will be hard is to make a separate database in the same instance not affect the performance of other databases in same instance. None of those OS's provide the controlled partitioning of virtual memory MVS does. All you need is for one of those databases to page out the other's shared memory in the same instance and all hell will break loose performance-wise.

>You think they've got no chance of coming back? (I happen to agree,
>but they do seem to be refusing to die, unlike Sybase, which seems to
>be disappearing faster than free beer.)

What is holding Informix there is the amazing amount of third party software written for their "engine". They used to package it real cheap for developers who wanted to have access to a database. Turn-key stuff. If you look at maintenance $$$ they still collect from all those, although very small individually, it's heaps overall. That will keep them afloat. Until all those developers re-write their stuff for the other "el-cheapo" on the block: sql server. Then they're gone. Pity....

>:-) (And the first thing to go is bloody hard coded schema's!!!!)
>(I can hear you laughing from here!)

No, I understand your problem. Been in the same situation many times. Stuck with something written by sawdust-brains, who collected the money and ran. Then the suckers are stuck with maintaining it and making it work...

Hang in there and keep belting Larry. The more, the better: we may even get some action soon...

:-)

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Thu Apr 06 2000 - 00:00:00 CDT