Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: ADD partition using ALTER and EVERY keyword

    EVERY keyword is not working in ALTER statement for adding new partitions. I successfully created table using the same EVERY keyword. However when I do ALTER using EVERY keyword it is neither throwing an error nor adding new partitions.

    Initially I created a table with three partitions, i.e for 13,14 and 15. I see these partitions under syscat.datapartitions.

    create table r_dummy
    (
    a_date date,
    b_int int
    )
    partition by range(a_date)
    (
    starting from('03/13/2013') ending at('03/15/2013') every 1 day
    )
    ;

    select *
    from syscat.datapartitions
    where tabname='R_DUMMY'
    ;

    Now I need to add three more partitions for consecutive days.

    alter table r_dummy add partition starting('03/16/2013') ending('03/18/2013') every 1 day;

    The above alter statement got executed successfully without any parsing issues. However I dont see any new partitions added.

    Please correct if I'm missing something. Any help is appreciated.

    Thanks in advance
    Raghu

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you check the documentation of the ALTER TABLE statement to see if that is allowed?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2013
    Posts
    2
    Thanks for the quick response.

    However it should at least throw an error, in first place. I did check the documentation and I dont the see the option for keyword EVERY in the ALTER statement.

    Please help me if there is way to add new partitions for a given range with a width of every day.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by raghunadhr View Post
    Thanks for the quick response.

    However it should at least throw an error, in first place. I did check the documentation and I dont the see the option for keyword EVERY in the ALTER statement.

    Please help me if there is way to add new partitions for a given range with a width of every day.
    Did you get a response that the SQL was completed successfully with return code of 0? If you didn't, don't assume it worked, and probably you are not retrieving the DB2 response correctly from the command you submitted.

    For the partitioned tables that I manage, I wrote a script that can automatically add new partitions on to the end, based on various input parms into the script.

    Another option is to create a new table with the partitions you want added (different table name), then do a db2look to see the partition syntax for the table, and then somehow use that partition syntax to cut and paste into a script that adds the partitions using the correct alter table syntax.
    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
    if looking at the alter table command
    add-partition

    |--+----------------+--| boundary-spec |--+---------------------+-->
    '-partition-name-' '-IN--tablespace-name-'

    you can specify boundary start stop but not every day..
    this has been done when creating the table and can not be changed for this table nor to be specified
    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

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
  •