Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: alter table <tabname> partition by range

    Hi,

    I am trying to alter existing non partition table to add partition by range. below is the command i was using.. didn't work.

    alter table staging.tab1 partition by range (LLT9999) ;
    SQL0104N An unexpected token "alter table staging.tab1" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include:
    "<create_staging_table_like>". SQLSTATE=42601

    please help me how to alter table to create parition by range.

    is it like .. do i have to recreate table with partition by range cluase and load data..?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Have you checked the SQL Reference manual?
    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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    According to the DB2 10 for LUW manual, an originally non-partitioned table cannot be altered to partitioned; there is no "alter table partition by ...", only an "alter table add partition ...", but the latter (obviously) is not allowed on a non-partitioned table:
    SQL20183N The table "TST" is not compatible with the specified table partitioning operation. SQLSTATE=428FT

    So indeed, you will have to drop the existing table and create a new, partitioned one.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Peter.Vanroose View Post

    So indeed, you will have to drop the existing table and create a new, partitioned one.
    Not necessarily. One could create a new, partitioned table with the matching definition, then attach the existing table to the new one as a partition. That would only work, of course, if the partitioning key values in the existing table fall within a single partition range.

Posting Permissions

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