Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Move Table to another Table Space

    Need to reorganize tables into different table spaces based on the size of the tables. As far as I am aware, you need to drop and recreate the table in the new table space in DB2 UDB. You can however, alter the buffer pool allocated to the table space. DB2 UDB does not provide a ALTER TABLE command to move a table to another table space like Oracle.

    Pls correct me if I am wrong.

    TIA.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are correct. You cannot alter a table to define a new tablespace location. But you do the following:

    Create a new table like the old one (CREATE LIKE) and specify the new tablespace location. Use the same schema name, but a different table name.

    Move the data with a INSERT INTO new-table SELECT * FROM old-table

    Rename the old table to something different.

    Rename the new table to the name of the old table.

    Create any Primary and foreign keys as exist in the old table. Create any other indexes that exist on the old table.

    Drop the old table.

    Check out the SQL Reference Vol 2 manual for exact 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

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    How would we copy the constaints the old table had?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By using db2look to extract their definition, then running the resulting script.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you happen to be on DB2 9.7, you can use ADMIN_MOVE_TABLE procedure.

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sathyaram_s View Post
    use ADMIN_MOVE_TABLE procedure.
    Not really. It does not move referential integrity constraints, as far as I know.

  7. #7
    Join Date
    Feb 2012
    Posts
    3
    Sorry, generating the DDL and using it on the new table doesn't allow me to remove the old one. the constraints are still there

  8. #8
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    To remove the old constraints, you would need to drop them via an ALTER TABLE statement.

    To recap, if the objective is to move a table from one table space to another, you would need to do the following (one of the methods):

    Create a new table like the old one (CREATE LIKE) and specify the new tablespace location. Use the same schema name, but a different table name.

    Move the data with a INSERT INTO new-table SELECT * FROM old-table

    Rename the old table to something different.

    Rename the new table to the name of the old table.

    Create any Primary and foreign keys as exist in the old table. Create any other indexes that exist on the old table. db2look can be used for this.

    Drop the old table.

    Hope this helps.

Posting Permissions

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