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

    Unanswered: alter table <tabname> partition by range


    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
    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
    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

  4. #4
    Join Date
    Jun 2003
    Toronto, Canada
    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