Extent Sizes and Page sizes

[Asphalt Blazer]

Can anybody explain to me the relation between extentsize and pagesize.
When I am creating a new tablespace what do I need to be careful of?
Say pagesize is 8K is keeping extentsize(pages) 64 too large? What
exactly is the relation?

Thanks

[Pierre Saint-Jacques]

For a tablespace, you need to define the size 4,8,16,32k) of the page that
will hold the data. Each tablespace of a page size must have access to
bufferpool of same page size and a system temporary tablespace of same page
size.

The extent size determines the number of those pages that db2 will use to
manage the physical allocation of disk space on each container when new data
needs to be written and no empty pace exists to receive the row. Then the
write will force to physically allocate a full extent size of contiguous
pages.

The extent size is also used to determins the size of prefetcblock when DB2
has to retrieve the data. DB2 will determine that prefetching block data is
preferred and will look at the prefetch size defined for that tablespace.
it is dependent on the extent size you must define.

Page size and extent size cannot be changed by ALTER commands or some such.
Once chosen and defined, they're cast in concrete.

If you do not know anything about the data and how it will be manupulated
then you can use a "gross" rule of thumb.
OLTP environment: Small pages (4k,8k) small no. of pages per extent: 4,6,16
pages.
DSS or WareHouse: Larger pages (16k,32k) larger extents (16,32,64,128
pages). Use 256 extent size judiciously. The size of the extent is chosen
on the basis of the size of the table data and its forecasted usage (query
intensive, trans. intensive, mix, ...).

Hope this helps, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Asphalt Blazer" <as***********@gmail.coma écrit dans le message de news:
11**********************@v33g2000cwv.googlegroups. com...

Can anybody explain to me the relation between extentsize and pagesize.
When I am creating a new tablespace what do I need to be careful of?
Say pagesize is 8K is keeping extentsize(pages) 64 too large? What
exactly is the relation?

Thanks

[Asphalt Blazer]

Thanks for your response

>From what I understand, DB2 will create a new extent if there is now

space for the new row that comes in.
As long as this goes on its fine.

But what does people mean by saying for 8 K pagesize 64 extentsize is
too large. say the prefetch is set to automatic and tablespace is
spread over 2 disks. Where is the space wastage coming into picture?
On Jan 26, 4:16 pm, "Pierre Saint-Jacques" <[e-mail]:

For a tablespace, you need to define the size 4,8,16,32k) of the page that
will hold the data. Each tablespace of a page size must have access to
bufferpool of same page size and a system temporary tablespace of same page
size.

The extent size determines the number of those pages that db2 will use to
manage the physical allocation of disk space on each container when new data
needs to be written and no empty pace exists to receive the row. Then the
write will force to physically allocate a full extent size of contiguous
pages.

The extent size is also used to determins the size of prefetcblock when DB2
has to retrieve the data. DB2 will determine that prefetching block data is
preferred and will look at the prefetch size defined for that tablespace.
it is dependent on the extent size you must define.

Page size and extent size cannot be changed by ALTER commands or some such.
Once chosen and defined, they're cast in concrete.

If you do not know anything about the data and how it will be manupulated
then you can use a "gross" rule of thumb.
OLTP environment: Small pages (4k,8k) small no. of pages per extent: 4,6,16
pages.
DSS or WareHouse: Larger pages (16k,32k) larger extents (16,32,64,128
pages). Use 256 extent size judiciously. The size of the extent is chosen
on the basis of the size of the table data and its forecasted usage (query
intensive, trans. intensive, mix, ...).

Hope this helps, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Asphalt Blazer" <[e-mail] écrit dans le message de news:
[e-mail]. com...

Can anybody explain to me the relation between extentsize and pagesize.
When I am creating a new tablespace what do I need to be careful of?
Say pagesize is 8K is keeping extentsize(pages) 64 too large? What
exactly is the relation?

Thanks

[Pierre Saint-Jacques]

TRhey may mean that if you do a singleton insert and this very infrequently,
the first time DB2 needs new space, it will put the row as row1 of Page1 of
64 allocated and "waste" the space. Imagine that for an employee table with
a hiring freeze !!!!
Now if your issue is that you have to worry about (8k*4096*64=2.1MB) of
wasted space, then ....
Mind you, anytime DB2 decides to prefetch and that extent is chosen, you
write in the bufferpool 63 empty pages.
Whether 8k, 64 pagwes is a good or bad combination is more driven by what
you do with the data and how much of it you have.
HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Asphalt Blazer" <as***********@gmail.coma écrit dans le message de news:
11**********************@m58g2000cwm.googlegroups. com...
Thanks for your response

>From what I understand, DB2 will create a new extent if there is now

space for the new row that comes in.
As long as this goes on its fine.

But what does people mean by saying for 8 K pagesize 64 extentsize is
too large. say the prefetch is set to automatic and tablespace is
spread over 2 disks. Where is the space wastage coming into picture?
On Jan 26, 4:16 pm, "Pierre Saint-Jacques" <[e-mail]:

For a tablespace, you need to define the size 4,8,16,32k) of the page that
will hold the data. Each tablespace of a page size must have access to
bufferpool of same page size and a system temporary tablespace of same
page
size.

The extent size determines the number of those pages that db2 will use to
manage the physical allocation of disk space on each container when new
data
needs to be written and no empty pace exists to receive the row. Then the
write will force to physically allocate a full extent size of contiguous
pages.

The extent size is also used to determins the size of prefetcblock when
DB2
has to retrieve the data. DB2 will determine that prefetching block data
is
preferred and will look at the prefetch size defined for that tablespace.
it is dependent on the extent size you must define.

Page size and extent size cannot be changed by ALTER commands or some
such.
Once chosen and defined, they're cast in concrete.

If you do not know anything about the data and how it will be manupulated
then you can use a "gross" rule of thumb.
OLTP environment: Small pages (4k,8k) small no. of pages per extent:
4,6,16
pages.
DSS or WareHouse: Larger pages (16k,32k) larger extents (16,32,64,128
pages). Use 256 extent size judiciously. The size of the extent is chosen
on the basis of the size of the table data and its forecasted usage (query
intensive, trans. intensive, mix, ...).

Hope this helps, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Asphalt Blazer" <[e-mail] écrit dans le message de
news:
[e-mail]. com...

Can anybody explain to me the relation between extentsize and pagesize.
When I am creating a new tablespace what do I need to be careful of?
Say pagesize is 8K is keeping extentsize(pages) 64 too large? What
exactly is the relation?

Thanks