Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Question Unanswered: Table Range Partitioning with MAXVALUE; how to attach new partition?

    Hello folks,

    I have a range partition table which looks like this
    Code:
    partition by range ( DATE )
    (
      partition TEST_PARTITION_MI starting minvalue                                         in TEST_DATA_TS_MI INDEX in TEST_INDEX_TS_MI,
      partition TEST_PARTITION_15 starting from ( '2014-11-15' )   ending at ( '2014-11-15' )  in TEST_DATA_TS_15 INDEX in TEST_INDEX_TS_15,
      partition TEST_PARTITION_16 starting from ( '2014-11-16' )   ending at ( '2014-11-16' )  in TEST_DATA_TS_16 INDEX in TEST_INDEX_TS_16,
      partition TEST_PARTITION_17 starting from ( '2014-11-17' )   ending at ( '2014-11-17' )  in TEST_DATA_TS_17 INDEX in TEST_INDEX_TS_17,
      partition TEST_PARTITION_18 starting from ( '2014-11-18' )   ending at ( '2014-11-18' )  in TEST_DATA_TS_18 INDEX in TEST_INDEX_TS_18,
      partition TEST_PARTITION_19 starting from ( '2014-11-19' )   ending at ( '2014-11-19' )  in TEST_DATA_TS_19 INDEX in TEST_INDEX_TS_19,
      partition TEST_PARTITION_20 starting from ( '2014-11-20' )   ending at ( '2014-11-20' )  in TEST_DATA_TS_20 INDEX in TEST_INDEX_TS_20,
      partition TEST_PARTITION_21 starting from ( '2014-11-21' )   ending at ( '2014-11-21' )  in TEST_DATA_TS_21 INDEX in TEST_INDEX_TS_21,
      partition TEST_PARTITION_22 starting from ( '2014-11-22' )   ending at ( '2014-11-22' )  in TEST_DATA_TS_22 INDEX in TEST_INDEX_TS_22,
      partition TEST_PARTITION_23 starting from ( '2014-11-23' )   ending at ( '2014-11-23' )  in TEST_DATA_TS_23 INDEX in TEST_INDEX_TS_23,
      partition TEST_PARTITION_24 starting from ( '2014-11-24' )   ending at ( '2014-11-24' )  in TEST_DATA_TS_24 INDEX in TEST_INDEX_TS_24,
      partition TEST_PARTITION_25 starting from ( '2014-11-25' )   ending at ( '2014-11-25' )  in TEST_DATA_TS_25 INDEX in TEST_INDEX_TS_25,
      partition TEST_PARTITION_26 starting from ( '2014-11-26' )   ending at ( '2014-11-26' )  in TEST_DATA_TS_26 INDEX in TEST_INDEX_TS_26,
      partition TEST_PARTITION_27 starting from ( '2014-11-27' )   ending at ( '2014-11-27' )  in TEST_DATA_TS_27 INDEX in TEST_INDEX_TS_27,
      partition TEST_PARTITION_28 starting from ( '2014-11-28' )   ending at ( '2014-11-28' )  in TEST_DATA_TS_28 INDEX in TEST_INDEX_TS_28,
      partition TEST_PARTITION_29 starting from ( '2014-11-29' )   ending at ( '2014-11-29' )  in TEST_DATA_TS_29 INDEX in TEST_INDEX_TS_29,
      partition TEST_PARTITION_30 starting from ( '2014-11-30' )   ending at ( '2014-11-30' )  in TEST_DATA_TS_30 INDEX in TEST_INDEX_TS_30,
      partition TEST_PARTITION_01 starting from ( '2014-12-01' )   ending at ( '2014-12-01' )  in TEST_DATA_TS_01 INDEX in TEST_INDEX_TS_01,
      partition TEST_PARTITION_02 starting from ( '2014-12-02' )   ending at ( '2014-12-02' )  in TEST_DATA_TS_02 INDEX in TEST_INDEX_TS_02,
      partition TEST_PARTITION_03 starting from ( '2014-12-03' )   ending at ( '2014-12-03' )  in TEST_DATA_TS_03 INDEX in TEST_INDEX_TS_03,
      partition TEST_PARTITION_04 starting from ( '2014-12-04' )   ending at ( '2014-12-04' )  in TEST_DATA_TS_04 INDEX in TEST_INDEX_TS_04,
      partition TEST_PARTITION_05 starting from ( '2014-12-05' )   ending at ( '2014-12-05' )  in TEST_DATA_TS_05 INDEX in TEST_INDEX_TS_05,
      partition TEST_PARTITION_06 starting from ( '2014-12-06' )   ending at ( '2014-12-06' )  in TEST_DATA_TS_06 INDEX in TEST_INDEX_TS_06,
      partition TEST_PARTITION_07 starting from ( '2014-12-07' )   ending at ( '2014-12-07' )  in TEST_DATA_TS_07 INDEX in TEST_INDEX_TS_07,
      partition TEST_PARTITION_08 starting from ( '2014-12-08' )   ending at ( '2014-12-08' )  in TEST_DATA_TS_08 INDEX in TEST_INDEX_TS_08,
      partition TEST_PARTITION_09 starting from ( '2014-12-09' )   ending at ( '2014-12-09' )  in TEST_DATA_TS_09 INDEX in TEST_INDEX_TS_09,
      partition TEST_PARTITION_10 starting from ( '2014-12-10' )   ending at ( '2014-12-10' )  in TEST_DATA_TS_10 INDEX in TEST_INDEX_TS_10,
      partition TEST_PARTITION_11 starting from ( '2014-12-11' )   ending at ( '2014-12-11' )  in TEST_DATA_TS_11 INDEX in TEST_INDEX_TS_11,
      partition TEST_PARTITION_12 starting from ( '2014-12-12' )   ending at ( '2014-12-12' )  in TEST_DATA_TS_12 INDEX in TEST_INDEX_TS_12,
      partition TEST_PARTITION_13 starting from ( '2014-12-13' )   ending at ( '2014-12-13' )  in TEST_DATA_TS_13 INDEX in TEST_INDEX_TS_13,
      partition TEST_PARTITION_14 starting from ( '2014-12-14' )   ending at ( '2014-12-14' )  in TEST_DATA_TS_14 INDEX in TEST_INDEX_TS_14,
      partition TEST_PARTITION_MA ending maxvalue
    When I try to alter the table and attach a new partition for example

    Code:
    alter table TESTSCHEMA.TEST_PART_TABLE add partition TEST_PARTITION_141215 starting from ('2014-12-15') ending at ('2014-12-15') in TEST_DATA_TS_15 INDEX in TEST_INDEX_TS_15;
    I receive the following error code
    Code:
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0636N  Range specified for data partition "TEST_LEG_HISTORY_141215" is not
    valid.  Reason code = "10".  SQLSTATE=56016
    with MINVALUE and MAXVALUE specified, how do I proceed with rolling in / rolling out new/old partitions?
    Any help would be appreciated, thank you very much in advance

    Homes

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    I would detach the open partition : export this table
    add new partition with new borders
    add partition with open bounders
    reload data from detached open partition (because there could be data now residing in lastest non-open partition)
    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
    Jul 2013
    Posts
    4

    detach - reattach

    Hi, thanks przytula_guy for your reply.

    I have solved it meanwhile by myself with a slightly different approach. I did the following steps:
    • Detach the partition with MAXVALUE into a temporary table
    • Add the new partition
    • Insert into new partition, all records within range from temporary table
    • delete records from temporary table
    • re-attach the MAXVALUE partition
    • run referential integrity checks


    this avoids the range boundary conflicts when running the RI and keeps data inserted into MAXVALUE partitions sorted within its according range as you have mentioned

    regards

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
  •