Serge Rielau wrote:

>
> DROP COLUMN btw is in DB2 Viper. So get the best mileage out of the
> deficiency while you can ;-)
>


From what I can see we will still be enjoying the deficiency (albeit in
a different form) for quite awhile longer

Brian Tkatch wrote:
>> I fail to see the value of separating index and data table spaces in general.
>
> I thought it was so the tablespaces could be a separate physical disks,
> allowing for sumultaneous reads of index and data. And that this gave a
> speed increase.
>
> I'm very interested in hearing more on this topic. I know very little
> about it.
>
> B.
>
One of the more popular discussion points on the Oracle forum - see
http://groups.google.com/groups?lnk=...espaces+oracle

Brian Tkatch wrote:
>> I fail to see the value of separating index and data table spaces in general.
>
> I thought it was so the tablespaces could be a separate physical disks,
> allowing for sumultaneous reads of index and data. And that this gave a
> speed increase.
>
> I'm very interested in hearing more on this topic. I know very little
> about it.
The trend is that you don't have much say in the placement of the data
on that SAN anyway.
You create your database with automatic storage (in fact that's the
default in DB2 Viper if I'm not mistaken).
For your tablespaces you say absolutely nothing anymore. Just name and
page size. DB2 will take care of it.
Talking of which. With large RID's tablespace limits are a thing of the
past and there is little incentive for multiple page sizes either.
For a run of the mill application using DB2 V8.2 or DB2 Viper I'd create
the data base with a 16k or even 32k page size form the get go. I would
not worry about creating any extra buffer pools (self tuning memory in
Viper) or table spaces.
Will it be as fast as an expertly tuned DB2? No, but define expert. If
it gets within 90% of maximum you're better off buying memory than
hiring that expert to create a dedicated bufferpool to pin that hot
table in a separate tablespace and eek out and extra teeny% by going DMS
RAW...

Now, the "experts" will complain and cry foul of course, but leave them
to deal with that 40TB warehouse of a credit card company Y and the 100
node DPF system that ensures you get your customized, pre-approved
credit card from Y.
There are those who need experts and those who don't. Most don't.

We have long since given up on writing assembler in the industry outside
of niche areas. Data placement IMHO falls into that category.

Cheers
Serge

PS: Keep your logs on separate disks of course. ;-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Mark Townsend wrote:
>
>>>> Oracle implements implements partitioning and online rebuild (of
>>>> what, indices?) in packages? *gosh* No wonder they need so many
>>>> packages.
>>>
>>> Oracle does not implement paritioning or online rebuild of indices
>>> via packages.
>> I know that :-) Daniel is sloppy in is allegations and claims, and he
>> deserves to be called upon it.
> You reasonably called him on it. But you also inreasonably responded
> with allegations of your own. Pot. Kettle. Black
It's called sarcasm Mark. You know that one well, as long as you serve it.
Now be a good sport and take it.

Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Mark Townsend wrote:
> One of the more popular discussion points on the Oracle forum - see
> http://groups.google.com/groups?lnk=...espaces+oracle
I actually liked that game:
http://www.amiga.hu/amigos/ancientoy...lemming1_1.jpg

Cheers
Serge

PS: And no, I'm not off topic.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Mark Townsend wrote:
> Brian Tkatch wrote:
> >> I fail to see the value of separating index and data table spaces in general.
> >
> > I thought it was so the tablespaces could be a separate physical disks,
> > allowing for sumultaneous reads of index and data. And that this gave a
> > speed increase.
> >
> > I'm very interested in hearing more on this topic. I know very little
> > about it.
> >
> > B.
> >
> One of the more popular discussion points on the Oracle forum - see
> http://groups.google.com/groups?lnk=...espaces+oracle

I remember it somewhat. But, it's been a few years since i've treaded
there. Got to follow the database i'm told to work with.

B.

Serge Rielau wrote:
> Brian Tkatch wrote:
> >> I fail to see the value of separating index and data table spaces in general.
> >
> > I thought it was so the tablespaces could be a separate physical disks,
> > allowing for sumultaneous reads of index and data. And that this gave a
> > speed increase.
> >
> > I'm very interested in hearing more on this topic. I know very little
> > about it.
> The trend is that you don't have much say in the placement of the data
> on that SAN anyway.
> You create your database with automatic storage (in fact that's the
> default in DB2 Viper if I'm not mistaken).
> For your tablespaces you say absolutely nothing anymore. Just name and
> page size. DB2 will take care of it.
> Talking of which. With large RID's tablespace limits are a thing of the
> past and there is little incentive for multiple page sizes either.
> For a run of the mill application using DB2 V8.2 or DB2 Viper I'd create
> the data base with a 16k or even 32k page size form the get go. I would
> not worry about creating any extra buffer pools (self tuning memory in
> Viper) or table spaces.
> Will it be as fast as an expertly tuned DB2? No, but define expert. If
> it gets within 90% of maximum you're better off buying memory than
> hiring that expert to create a dedicated bufferpool to pin that hot
> table in a separate tablespace and eek out and extra teeny% by going DMS
> RAW...
>
> Now, the "experts" will complain and cry foul of course, but leave them
> to deal with that 40TB warehouse of a credit card company Y and the 100
> node DPF system that ensures you get your customized, pre-approved
> credit card from Y.
> There are those who need experts and those who don't. Most don't.
>
> We have long since given up on writing assembler in the industry outside
> of niche areas. Data placement IMHO falls into that category.
>
> Cheers
> Serge
>
> PS: Keep your logs on separate disks of course. ;-)
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> IOD Conference
> http://www.ibm.com/software/data/ond...ness/conf2006/

Good points, thanx Serge.

> We have long since given up on writing assembler in the industry outside
> of niche areas. Data placement IMHO falls into that category.

It is called "Assembly" which is "assembled" by the "Assembler".
"Assembler" code would be some form of meta-language that doesn't
exist.

One of my many pet pieves.

B.

Brian Tkatch wrote:
>
> It is called "Assembly" which is "assembled" by the "Assembler".
> "Assembler" code would be some form of meta-language that doesn't
> exist.
>
> One of my many pet pieves.

You mean peeves. Couldn't resist.

Serge Rielau wrote:

> Data placement IMHO falls into that category.

This is true, up to a limit. Spending time to try and isolate I/O
from tables/indexes/temp/etc. is one thing. But understanding how
your data is laid out on a SAN is still very relevant.

I have seen small data warehouses absolutely CRAWL (constant 95%
I/O wait) when no one bothered to pay attention to how tablespaces,
filesystems and LUNs map to physical disks in a SAN. And this
happens a LOT.

Ian wrote:
> Serge Rielau wrote:
>
>> Data placement IMHO falls into that category.
>
> This is true, up to a limit. Spending time to try and isolate I/O
> from tables/indexes/temp/etc. is one thing. But understanding how
> your data is laid out on a SAN is still very relevant.
>
> I have seen small data warehouses absolutely CRAWL (constant 95%
> I/O wait) when no one bothered to pay attention to how tablespaces,
> filesystems and LUNs map to physical disks in a SAN. And this
> happens a LOT.
The rule of thumb is: strip everything across everything.
If your storage admin dedicates 2 disks to DB2 then there isn't much
help to be had. ;-)

BTW, I got, after posting my "proclamation", a tad scared and consulted
with our DMS (Data management services) folks and got the content confirmed.
I paraphrase wat I was told:
"A good(!) DBA may be able to boost performance by 10% compared to DB2's
built in algorithms, but that same DBA is likely to gain a lot more by
tuning the schema itself (create/drop indices, etc...) rather than
wasting his/her time on data placement."

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/