Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Mar 18
    2012

    how does the partition by growth and partition by range tablespace work?

    Rick Carlson

    Hello Everyone,

    In both the type of tablespaces- partition by growth and partition by range, one also specifies SEGSIZE parameter.

    Now to put my question, suppose i want to create a partition by growth table, with MAXPARTITIONS 10, and SEGSIZE 64 and DSIZE 4Gb.

    After creating my tablespace,table and indexes, i handover the table to application team. Now as we know that a segmented tablespace has a maximum limit of 64gb. Also in partitioned tablespace we give partition keys.Howver in partition by growth table, we do not give limit keys.

    So now my doubt and question is that at what point DB2 will start creating the partitions? After the data has grown to 4Gb(DSSIZE Value) or after the maximum limit for the segmented tablespace is reached?

    Then as data grows, and the partition 10 gets full, and one does alter for MAXPARTITION to set it to new higher value.Does this pose any recovery threat/concern as in case of improper ROTATE partitions?

    As i've never used these new tablespace, so it will be really helpful, if i can get our esteemed members experience.

    Please do share or point at any whitepaper or presentation related to this.

    thanks!!!

    Steen Rasmussen
    [CA technologies]
    You are correct that DSSIZE will dictate a new PBG partition.
    Also correct that once the 10th partition is full you can alter MAXPARTITIONS to continue to grow (you probably want to alter B4 you run out of partition 10).

    For PBG you will not ROTATE - that's for TCP or PBR.

    PBG is great of you don't have a partition scheme - but make sure you don't have any looping applications since DB2 will keep on adding :) Also - be careful doing partition-level reorgs of a PBG since you might not be able to fit all the rows in the partition after a reorg, so the REORG will fail. You will be on the safe side doing reorg of the entire PBG - but then every reorg will run much longer.
    For ROTATE - I really prefer to ADD PART and empty out the logical low partition. The reason is - having LOGICAL and PHYSICAL partitions out of sync might make your head spin when it comes to utilities running on the partition level.

    Steen Rasmussen
    CA Technologies
    Sr Engineering Services Architect

    From: Rick Carlson [mailto:[login to unmask email]
    Sent: Sunday, March 18, 2012 12:44 PM
    To: [login to unmask email]
    Subject: [DB2-L] - how does the partition by growth and partition by range tablespace work?


    Hello Everyone,

    In both the type of tablespaces- partition by growth and partition by range, one also specifies SEGSIZE parameter.

    Now to put my question, suppose i want to create a partition by growth table, with MAXPARTITIONS 10, and SEGSIZE 64 and DSIZE 4Gb.

    After creating my tablespace,table and indexes, i handover the table to application team. Now as we know that a segmented tablespace has a maximum limit of 64gb. Also in partitioned tablespace we give partition keys.Howver in partition by growth table, we do not give limit keys.

    So now my doubt and question is that at what point DB2 will start creating the partitions? After the data has grown to 4Gb(DSSIZE Value) or after the maximum limit for the segmented tablespace is reached?

    Then as data grows, and the partition 10 gets full, and one does alter for MAXPARTITION to set it to new higher value.Does this pose any recovery threat/concern as in case of improper ROTATE partitions?

    As i've never used these new tablespace, so it will be really helpful, if i can get our esteemed members experience.

    Please do share or point at any whitepaper or presentation related to this.

    thanks!!!

    -----End Original Message-----
    William Favero
    [IBM]
    DSSIZE is the underlying partition size of a partition-by-growth
    universal table space. In you case you will extend the
    partition-by-growth table space each time you reach 4GB.

    In your case, an ALTER MAXPARTITIONS is necessary to extend your table
    space to more than 10 partitions once 10 is reached. The new column
    MAXPARTITIONS will tell you what value you are set at and PARTITIONS
    will tell you how close you are getting to the MAXPARTITIONS value.

    My IOD presentation is in SlideShare...
    http://www.slideshare.net/wfavero/db2-10-for-z-os-universal-table-spaces-iod-2010-seesion1929-favero

    Willie

    ROTATE is not allowed for a partition-by-growth table space. It is only
    allowed on range-partition and legacy partitioned table spaces.

    On 3/18/2012 12:44 PM, Rick Carlson wrote:
    >
    > Hello Everyone,
    >
    > In both the type of tablespaces- partition by growth and partition by
    > range, one also specifies SEGSIZE parameter.
    >
    > Now to put my question, suppose i want to create a partition by growth
    > table, with MAXPARTITIONS 10, and SEGSIZE 64 and DSIZE 4Gb.
    >
    > After creating my tablespace,table and indexes, i handover the table
    > to application team. Now as we know that a segmented tablespace has a
    > maximum limit of 64gb. Also in partitioned tablespace we give
    > partition keys.Howver in partition by growth table, we do not give
    > limit keys.
    >
    > So now my doubt and question is that at what point DB2 will start
    > creating the partitions? After the data has grown to 4Gb(DSSIZE Value)
    > or after the maximum limit for the segmented tablespace is reached?
    >
    > Then as data grows, and the partition 10 gets full, and one does alter
    > for MAXPARTITION to set it to new higher value.Does this pose any
    > recovery threat/concern as in case of improper ROTATE partitions?
    >
    > As i've never used these new tablespace, so it will be really helpful,
    > if i can get our esteemed members experience.
    >
    > Please do share or point at any whitepaper or presentation related to
    > this.
    >
    > thanks!!!
    >
    >
    > -----End Original Message-----

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

    William Favero
    [IBM]
    Here's a more current copy of my DB2 universal table space presentation.

    http://www.slideshare.net/wfavero/db2-10-universal-table-space-20120318-no-template

    Willie



    On 3/18/2012 12:44 PM, Rick Carlson wrote:
    >
    > Hello Everyone,
    >
    > In both the type of tablespaces- partition by growth and partition by
    > range, one also specifies SEGSIZE parameter.
    >
    > Now to put my question, suppose i want to create a partition by growth
    > table, with MAXPARTITIONS 10, and SEGSIZE 64 and DSIZE 4Gb.
    >
    > After creating my tablespace,table and indexes, i handover the table
    > to application team. Now as we know that a segmented tablespace has a
    > maximum limit of 64gb. Also in partitioned tablespace we give
    > partition keys.Howver in partition by growth table, we do not give
    > limit keys.
    >
    > So now my doubt and question is that at what point DB2 will start
    > creating the partitions? After the data has grown to 4Gb(DSSIZE Value)
    > or after the maximum limit for the segmented tablespace is reached?
    >
    > Then as data grows, and the partition 10 gets full, and one does alter
    > for MAXPARTITION to set it to new higher value.Does this pose any
    > recovery threat/concern as in case of improper ROTATE partitions?
    >
    > As i've never used these new tablespace, so it will be really helpful,
    > if i can get our esteemed members experience.
    >
    > Please do share or point at any whitepaper or presentation related to
    > this.
    >
    > thanks!!!
    >
    >
    > -----End Original Message-----

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


    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact