Version 9 Universal Table Space SEGSIZE parameter

Hunter Cobb

Version 9 Universal Table Space SEGSIZE parameter
To create a partition-by-growth universal table space, you specify the
MAXPARTITIONS parameter in CREATE TABLESPACE. You can code SEGSIZE; if
omitted, the default SEGSIZE is 4. To create a range-partitioned universal
table space, you specify the NUMPARTS and SEGSIZE parameters in CREATE
TABLESPACE. Both types of universal table space allow only one table to be
defined within. So my question is: what is the significance, if any, of the
value of the SEGSIZE parameter for universal table spaces?

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Favero

Re: Version 9 Universal Table Space SEGSIZE parameter
(in response to Hunter Cobb)
SEGSIZE still determines the type of table space you get... NUMPARTS
without SEGSIZE is just a pre DB2 9 partition table space. SEGSIZE
with no NUMPARTS or MAXPARTITIONS is just a segmented table space.

In addition, just as in previous releases of DB2, SEGSIZE still
controls your segment size... if you have a very low insert rate you
may not want a SEGSIZE of 64... there's the potential of lots of
unused pages in a segment... If you are not concerned about how full a
segment is, I still like SEGSIZE 32 or 64 to match up with prefetch
quantities..

Willie

I sure others will have more reasons..

Hunter Cobb wrote:
> To create a partition-by-growth universal table space, you specify the
> MAXPARTITIONS parameter in CREATE TABLESPACE. You can code SEGSIZE; if
> omitted, the default SEGSIZE is 4. To create a range-partitioned universal
> table space, you specify the NUMPARTS and SEGSIZE parameters in CREATE
> TABLESPACE. Both types of universal table space allow only one table to be
> defined within. So my question is: what is the significance, if any, of the
> value of the SEGSIZE parameter for universal table spaces?
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
>
>
>

--
Willie
My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos
Houston, TX, USA

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Hunter Cobb

Re: Version 9 Universal Table Space SEGSIZE parameter
(in response to William Favero)
Willie,

I understand that the presence / absence of the SEGSIZE parameter (in
conjunction with MAXPARTITIONS and NUMPARTS) determines the table space
type. Your observations make perfect sense for segmented table spaces.
What I don't understand is the differential behavior you get from having
a SEGSIZE of 4 vs a SEGSIZE of 8, 12 ... 64, when you can only have one
table in the (universal) table space. Alternatively, why should I care
about how full a segment is, given that all segments in the (universal)
table space belong to the same table?

Hunter
---------------------------------------------
Willie Favero wrote:
> SEGSIZE still determines the type of table space you get... NUMPARTS
> without SEGSIZE is just a pre DB2 9 partition table space. SEGSIZE
> with no NUMPARTS or MAXPARTITIONS is just a segmented table space.
> In addition, just as in previous releases of DB2, SEGSIZE still
> controls your segment size... if you have a very low insert rate you
> may not want a SEGSIZE of 64... there's the potential of lots of
> unused pages in a segment... If you are not concerned about how full
> a segment is, I still like SEGSIZE 32 or 64 to match up with prefetch
> quantities..
> Willie
>
> I sure others will have more reasons..
> Hunter Cobb wrote:
>> To create a partition-by-growth universal table space, you specify the
>> MAXPARTITIONS parameter in CREATE TABLESPACE. You can code SEGSIZE; if
>> omitted, the default SEGSIZE is 4. To create a range-partitioned
>> universal
>> table space, you specify the NUMPARTS and SEGSIZE parameters in CREATE
>> TABLESPACE. Both types of universal table space allow only one table
>> to be
>> defined within. So my question is: what is the significance, if any,
>> of the
>> value of the SEGSIZE parameter for universal table spaces?
>>
>> ______________________________________________________________________
>>
>> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>> ______________________________________________________________________
>>
>>
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
>> DB2-L list archives, FAQ, and delivery preferences are at
>> http://www.idug.org/lsidug under the Listserv tab. While at the
>> site, you can also access the IDUG Online Learning Center, Tech
>> Library and Code Place, see the latest IDUG conference information
>> and much more. If you have not yet signed up for Basic Membership in
>> IDUG, available at no cost, click on Member Services at
>> http://www.idug.org/lsms
>>
>>
>>
>

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Favero

Re: Version 9 Universal Table Space SEGSIZE parameter
(in response to Hunter Cobb)
When searching for space to complete an INSERT, after a number of
different things, DB2 will eventually check to see if there is space in
the current segment... if that segment is full, DB2 allocates (formats..
grasping for the correct word here) a new segment. So to complete the
insert of one row, with a SEGSIZE of 64, DB2 has to allocate a 64 page
segment. If that was the only insert that has to complete, you know
have 63 unused pages.

Willie

Hunter Cobb wrote:
> Willie,
>
> I understand that the presence / absence of the SEGSIZE parameter (in
> conjunction with MAXPARTITIONS and NUMPARTS) determines the table
> space type. Your observations make perfect sense for segmented table
> spaces. What I don't understand is the differential behavior you get
> from having a SEGSIZE of 4 vs a SEGSIZE of 8, 12 ... 64, when you can
> only have one table in the (universal) table space. Alternatively, why
> should I care about how full a segment is, given that all segments in
> the (universal) table space belong to the same table?
>
> Hunter
> ---------------------------------------------
> Willie Favero wrote:
>> SEGSIZE still determines the type of table space you get... NUMPARTS
>> without SEGSIZE is just a pre DB2 9 partition table space. SEGSIZE
>> with no NUMPARTS or MAXPARTITIONS is just a segmented table space. In
>> addition, just as in previous releases of DB2, SEGSIZE still
>> controls your segment size... if you have a very low insert rate you
>> may not want a SEGSIZE of 64... there's the potential of lots of
>> unused pages in a segment... If you are not concerned about how
>> full a segment is, I still like SEGSIZE 32 or 64 to match up with
>> prefetch quantities.. Willie
>>
>> I sure others will have more reasons.. Hunter Cobb wrote:
>>> To create a partition-by-growth universal table space, you specify the
>>> MAXPARTITIONS parameter in CREATE TABLESPACE. You can code SEGSIZE; if
>>> omitted, the default SEGSIZE is 4. To create a range-partitioned
>>> universal
>>> table space, you specify the NUMPARTS and SEGSIZE parameters in CREATE
>>> TABLESPACE. Both types of universal table space allow only one table
>>> to be
>>> defined within. So my question is: what is the significance, if any,
>>> of the
>>> value of the SEGSIZE parameter for universal table spaces?
>>>
>>> ______________________________________________________________________
>>>
>>> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>>> ______________________________________________________________________
>>>
>>>
>>>
>>> The IDUG DB2-L Listserv is only part of your membership in IDUG.
>>> The DB2-L list archives, FAQ, and delivery preferences are at
>>> http://www.idug.org/lsidug under the Listserv tab. While at the
>>> site, you can also access the IDUG Online Learning Center, Tech
>>> Library and Code Place, see the latest IDUG conference information
>>> and much more. If you have not yet signed up for Basic Membership
>>> in IDUG, available at no cost, click on Member Services at
>>> http://www.idug.org/lsms
>>>
>>>
>>>
>>
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The
> DB2-L list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site,
> you can also access the IDUG Online Learning Center, Tech Library and
> Code Place, see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG, available
> at no cost, click on Member Services at http://www.idug.org/lsms
>
>

--
Willie
My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos
Houston, TX, USA

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms