Db2 LUW one tablespace vs multiple table spaces

Renu Sharma

Db2 LUW one tablespace vs multiple table spaces
Hi List
Question for Gurus, I know the answer but clients wants a proof. On DB2 LUW for dataware house , of 50 GB should I put every table in their tablespace or combine them in one tbs. Another question their indexes should be in a different tablespace or not. I know the answer but I don’t have any benchmark , does anyone have any presentation , if yes can you please direct me to it.
Thanks in Advance
DB2 LUW row organized on Linux with version 10.5

Sent from my iPhone
Renu Sharma
SR DBA
ACE DBA Consulting www.db2dbas.com


Bill Gallagher

Db2 LUW one tablespace vs multiple table spaces
(in response to Renu Sharma)
As with all things DB2, there's probably no single correct answer. There are multiple ways to go, each with their own pros and cons. But here are just a couple of considerations:

1) It's probably a good rule of thumb to separate the indexes into separate tablespaces from the data, and even assign the index tablespaces to their own bufferpool that is only for indexes. Indexes tend to behave differently from tables, so they should be managed differently in terms of storage and memory.

2) You don’t need to necessarily put each table into its own individual tablespace, but you do want to be careful about how you group them together. If you're talking about a star schema for your warehouse, you're probably talking about fact tables and dimension tables. The fact tables could get very large in terms of number of rows and width of rows, and because of this you might want to put them in tablespaces with larger pagesizes, extent sizes and prefetch sizes than you would for dimension tables. And much like what you would do with indexes, you might want to have the fact table tablespaces and dimension table tablespaces allocated to different bufferpools because of different behavior patterns for each.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


-----Original Message-----
From: Renu Sharma [mailto:[login to unmask email]
Sent: Wednesday, January 24, 2018 3:44 PM
To: [login to unmask email]
Subject: [DB2-L] - Db2 LUW one tablespace vs multiple table spaces

Hi List
Question for Gurus, I know the answer but clients wants a proof. On DB2 LUW for dataware house , of 50 GB should I put every table in their tablespace or combine them in one tbs. Another question their indexes should be in a different tablespace or not. I know the answer but I don’t have any benchmark , does anyone have any presentation , if yes can you please direct me to it.
Thanks in Advance
DB2 LUW row organized on Linux with version 10.5

Sent from my iPhone
Renu Sharma
SR DBA
ACE DBA Consulting www.db2dbas.com



-----End Original Message-----

Nadir Doctor

Db2 LUW one tablespace vs multiple table spaces
(in response to Bill Gallagher)
Hi Renu,

It becomes easier to trend growth from a capacity planning perspective when
each large table/index is in its own dedicated <table/index>space
respectively.

Besides database partitioning, one should also use a combination of
table-partitioning and MDC indexes + adaptive compression to optimize space
usage.


Best Regards,
Nadir


On Wed, Jan 24, 2018 at 3:12 PM, GALLAGHER, WILLIAM <[login to unmask email]>
wrote:

> As with all things DB2, there's probably no single correct answer. There
> are multiple ways to go, each with their own pros and cons. But here are
> just a couple of considerations:
>
> 1) It's probably a good rule of thumb to separate the indexes into
> separate tablespaces from the data, and even assign the index tablespaces
> to their own bufferpool that is only for indexes. Indexes tend to behave
> differently from tables, so they should be managed differently in terms of
> storage and memory.
>
> 2) You don’t need to necessarily put each table into its own individual
> tablespace, but you do want to be careful about how you group them
> together. If you're talking about a star schema for your warehouse,
> you're probably talking about fact tables and dimension tables. The fact
> tables could get very large in terms of number of rows and width of rows,
> and because of this you might want to put them in tablespaces with larger
> pagesizes, extent sizes and prefetch sizes than you would for dimension
> tables. And much like what you would do with indexes, you might want to
> have the fact table tablespaces and dimension table tablespaces allocated
> to different bufferpools because of different behavior patterns for each.
>
> Bill Gallagher
> DB2 Database Administrator
> State of Connecticut
>
>
> -----Original Message-----
> From: Renu Sharma [mailto:[login to unmask email]
> Sent: Wednesday, January 24, 2018 3:44 PM
> To: [login to unmask email]
> Subject: [DB2-L] - Db2 LUW one tablespace vs multiple table spaces
>
> Hi List
> Question for Gurus, I know the answer but clients wants a proof. On DB2
> LUW for dataware house , of 50 GB should I put every table in their
> tablespace or combine them in one tbs. Another question their indexes
> should be in a different tablespace or not. I know the answer but I don’t
> have any benchmark , does anyone have any presentation , if yes can you
> please direct me to it.
> Thanks in Advance
> DB2 LUW row organized on Linux with version 10.5
>
> Sent from my iPhone
> Renu Sharma
> SR DBA
> ACE DBA Consulting www.db2dbas.com
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
>