Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Location
    australia
    Posts
    17

    Unanswered: alter table move on DB2 as in Oracle

    Hi Guys,

    Just wondering whether is anyone have heard or know of a new db2 command equivalent to the 'alter table move' existing in Oracle. I need to move a bunch of tables to a new tablespace and I know that since DB2 8.2 became available it has a lot of new goodies so I'm hoping we have something new able to help me. Otherwise I will have to stick with export/import approach.

    Cheers,

    harby.

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    i dont think so,
    u have to apply same old approach export/import :-(
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You should be able to use the ALTOBJ stored procedure to change the tablespace for a table.

    Andy

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Yet another alternative:
    1. RENAME TABLE
    2. CREATE TABLE ... LIKE <renamed-table>
    3. INSERT INTO ... SELECT * FROM <renamed-table>
    4. DROP <renamed-table>

    You will have to take care of constraints, however.

    A problem may be that the INSERT will be logged. You can use NOT LOGGED INITIALLY in that case, or export/load (import will be logged, too!)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    A problem may be that the INSERT will be logged. You can use NOT LOGGED INITIALLY in that case, or export/load (import will be logged, too!)
    There's also a load from cursor, which should be a bit faster than messing with files.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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