Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Location
    Sacramento, California
    Posts
    14

    Question Unanswered: Move table to another tablespace

    Is it possible to move an existing table to another tablespace in Db2 without using export/import or db2move? For instance, Oracle allows "ALTER TABLE <tablename> MOVE <tablespacename>".

    I don't see anything similar in the Db2 manuals.

    Thank you.

    Db2 UDB v7.2
    AIX 4.3

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Move table to another tablespace

    It is not possible as far as I know ...

    Cheers
    Sathyaram


    Originally posted by Kedison
    Is it possible to move an existing table to another tablespace in Db2 without using export/import or db2move? For instance, Oracle allows "ALTER TABLE <tablename> MOVE <tablespacename>".

    I don't see anything similar in the Db2 manuals.

    Thank you.

    Db2 UDB v7.2
    AIX 4.3
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2003
    Location
    Sacramento, California
    Posts
    14

    Re: Move table to another tablespace

    Originally posted by sathyaram_s
    It is not possible as far as I know ...

    Cheers
    Sathyaram

    Thanks. That's what I thought.

  4. #4
    Join Date
    Nov 2003
    Location
    Barcelona (SPAIN)
    Posts
    4

    Cool Re: Move table to another tablespace

    Originally posted by Kedison
    Thanks. That's what I thought.
    Hi,

    it's posible to move a table to an other tablespace using the db2look utility.

    I did it and it works OK.

    Regards,

    Superfayer

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Re: Move table to another tablespace

    Originally posted by superfayer
    Hi,

    it's posible to move a table to an other tablespace using the db2look utility.

    I did it and it works OK.

    Regards,

    Superfayer
    db2look? That would extract the DDL, do you mean db2move?
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Nov 2003
    Location
    Barcelona (SPAIN)
    Posts
    4

    Cool Re: Move table to another tablespace

    Originally posted by J Petruk
    db2look? That would extract the DDL, do you mean db2move?
    Yes,

    whit db2look you obtaine the DDL. After that, whit the SQL file you have to do a create/insert in a intermediate table in the new tablespace.
    At the end you have to do a drop of old table (views and indexes included) and a rename of the new one (and recreate the indexes and views).

    Regards,

    Superfayer.

  7. #7
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    the 'like' operator

    I have used the following with succes:

    'create table newschema.newtable like oldschema.oldtable
    in "newtablespace";

    commit;

    insert into newschema.newtable (select * from oldschema.oldtable)

    commit;'

    Of course this is still moving the data instead of an actual renaming/
    reallocation, but at least the like command saves the trouble of using
    db2look.

    BOW
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Re: the 'like' operator

    Originally posted by Tank
    I have used the following with succes:

    'create table newschema.newtable like oldschema.oldtable
    in "newtablespace";

    commit;

    insert into newschema.newtable (select * from oldschema.oldtable)

    commit;'

    Of course this is still moving the data instead of an actual renaming/
    reallocation, but at least the like command saves the trouble of using
    db2look.

    BOW
    And of course if log space is low you can use NOT LOGGED INITIALLY...
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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