Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Unhappy Unanswered: Extending Range of the autogenerated partion in DB2 V9.7

    Hi Team,

    I made a range partitioned table as below;-

    db2 "create table test.part_test (a int, b int) partition by range (b)(starting 1 ending 4 every 2)"

    now I will be able to enter values from 1 to 4 and every two will go in different partitions by name PART0 and PART1

    now the issue is I want to extend this partition from ending at 4 to suppose 10 so that the table keeps on generating a partition after every 2 values till 10, please suggest me the best way to do it, which would have minimum outage and also minimum effort.

    Would be really thank ful to your help

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    there is no alter possible
    you have to alter table detach .. and alter attach .. with new limits
    or add a new partition
    if indexes are not partitioned this can take a certain time related to the size of table
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2012
    Posts
    12

    Thank you

    Thanks for the reply,

    can you please elaborate a bit and also can u tell me some way to automate the same ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want to add new partitions with new ranges (that don't overlap with any existing ranges), you can do that with an alter table to add the new partitions. If you need to change the range of an existing partition, you will have to detach the partition and recreate it with the new range. The commands are documented in the Alter Table SQL section of the SQL Reference manual Vol 2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in case you don't have the address of infocenter
    Adding data partitions to partitioned tables
    we use a script to rotate partitions each/day/month depending on the table and as we have 9.5 and only global indexes this requires some time.. because the partitions are very large..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by przytula_guy View Post
    ...we have 9.5 and only global indexes this requires some time.. because the partitions are very large..
    Even if you have 9.7 (which supports partitioned indexes) you have to make sure that the partitioning column for a table is included in every unique index for that table, otherwise the indexes will not be partitioned. This sometimes results in "unusual" multi-column PK designs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2012
    Posts
    12

    Sapace estimation for Tablespaces for Range partitioned tables.

    Guys, Thanks for the help. Can you please suggest me how the capacity planning for tablespace shall be done for range partitioned tables.

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Marcus_A View Post
    Even if you have 9.7 (which supports partitioned indexes) you have to make sure that the partitioning column for a table is included in every unique index for that table, otherwise the indexes will not be partitioned. This sometimes results in "unusual" multi-column PK designs.
    Yep. And when you think: "a well, I change the index in 'not unique' and drop the PK constraint." Beware that all the FK-contraints pointing that table are also dropped without any warning. So, when you're dealing with an existing table the PK-index can not be partitioned and that is too bad for all the performance gains you thought you could achieve....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •