[DB2-L] Version 9 Universal Table Space SEGSIZE parameter

Walter Janißen

[DB2-L] Version 9 Universal Table Space SEGSIZE parameter
Willie

I thought, that db2 does the preallocation of space in advance? In earlier releases only for load-processing, but (I don't know, which release) for inserts as well.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Willie Favero
Gesendet: Donnerstag, 27. November 2008 03:36
An: [login to unmask email]
Betreff: Re: [DB2-L] Version 9 Universal Table Space SEGSIZE parameter

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

______________________________________________________________________

* 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