Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96

    Unanswered: DB2 Table Partitoning

    Hello,
    Does DB2 support Table Paritioning Interval?

    For example, in Oracle it has a feature of table partitioning interval.
    Please find example as below here : -
    create table
    pos_data (
    start_date DATE,
    store_id NUMBER,
    inventory_id NUMBER(6),
    qty_sold NUMBER(3)
    )
    PARTITION BY RANGE (start_date)
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    (
    PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
    PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
    );

    Thank you in advance!

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    Yes, db2's supports partitoned tables, article below gives all of the details:
    http://www.ibm.com/developerworks/da...istributedDBA/

  3. #3
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Hi Azready,

    Thanks for your reply, but does DB2 support table partitioning with Interval?
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

    From the following code,

    create table
    pos_data (
    start_date DATE,
    store_id NUMBER,
    inventory_id NUMBER(6),
    qty_sold NUMBER(3)
    )
    PARTITION BY RANGE (start_date)
    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
    (
    PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
    PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
    );

  4. #4
    Join Date
    Apr 2012
    Posts
    156
    They syntax is a bit different on db2 than oracle but you can achieve the same results as in Oracle.

    For example, you could do the following:
    create table
    pos_data (
    start_date DATE,
    store_id NUMBER,
    inventory_id NUMBER(6),
    qty_sold NUMBER(3)
    )
    IN tbsp0, tbsp1, tbsp2, tbsp3, tbsp4, tbsp5, tbsp6, tbsp7, tbsp8, tbsp9, tbsp10, tbsp11, , tbsp12
    PARTITION BY RANGE (start_date)
    (STARTING '1/1/2007' ENDING '12/31/2010'
    EVERY 1 MONTHS)

    This would create 12 partitions each with a single month of data. Double check the exact syntax of course. For the date format I always match the date format that db2 is using. You can of course alter the table and add new partitions, detatch partitions etc.

  5. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Hmm, I searched on table partitioning in the doco and lo and behold it came up with the answer.
    http://www-01.ibm.com/support/knowle.../c0021557.html
    you may also want to visit this redbook although it's getting a little old
    http://www.redbooks.ibm.com/abstract...7467.html?Open
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  6. #6
    Join Date
    Feb 2004
    Location
    earth
    Posts
    96
    Thanks everyone. Earlier I got a wrong understanding when reading up on oracle table partitioning with interval, which I thought it is able to create a new range of partition automatically without alter the table to add a new range of partition when inserting new range of data that is not within the partition keys.

Posting Permissions

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