How to move a table to a different tablespace in DB2

Recently I had to move a table of some DB2 database to a different tablespace (building an index required a larger page size). At first, I suspected changing tablespaces to be one of those complicated operations that essentially require you to re-create the whole table. However, I was surprised to see that there is a simple admin procedure in DB2 for doing what I wanted (see ADMIN_MOVE_TABLE). For example, to move the table myschema.mytable to data tablespace mydatatablespace, index tablespace myindextablespace, and LOB tablespace mylobtablespace, just run the following SQL statement:

CALL SYSPROC.ADMIN_MOVE_TABLE('myschema', 'mytable', 'mydatatablespace', 'myindextablespace', 'mylobtblespace', NULL, NULL, NULL, NULL, NULL, 'MOVE')

This entry was posted in DB2. Bookmark the permalink.

14 Responses to How to move a table to a different tablespace in DB2

  1. Ansley says:

    Thanks for this tip. I’ve been searching for something similar and everything I’d come across before this did say to drop and recreate or export/import. This is perfect!

    Ansley

  2. Manjula Kumaragama says:

    Thanks.. This is great..

  3. Masoud Salehi Alamdari says:

    Thanks Joachim. Very helpful to me!

  4. Ankit says:

    Hi,
    Thanks for the post but what i do want to know is if during this process the table will be available for other transaction or not.
    I’m basically an Oracle DBA but have to work on DB2 for some reason.
    Please revert ASAP.
    Thanks,
    Ankit

    • The documentation states it quite clearly:
      The ADMIN_MOVE_TABLE stored procedure moves the data in an active table into a new table object with the same name, while the data remains online and available for access.

  5. Pingback: #DB2-2 ONLINE TABLESPACE MIGRATION USING SYSPROC.ADMIN_MOVE_TABLE | PRASAD PANDE

  6. Dimi says:

    Thanks it works

  7. Shivan says:

    Absolutely great! Exactly what I was searching for!

    Thanks!

  8. dhiraj bilwal says:

    SYSPROC.ADMIN_MOVE_TABLE FUCTION NOT COMPATABLE WITH COLUMN ORGANIZE TABLE WHICH IS INTRODUCESD IN DB2 V10.5 BLU .

  9. Krishna says:

    In DB2 V10.5 FP 5. Is there a way to move a single partition of a partitioned table into another tablespace container.

    • Johan Swart says:

      Yes! That is exactly what I need to know too!!!

      I want to move a single partition of a table to another tablespace.

  10. Johan Swart says:

    Just a small update. I Managed to do this using the following method:

    (1) Detach partition as a new table.
    (2) Use the system function SYSPROC.ADMIN_MOVE_TABLE to move the table (detached partition) to the new tablespace.
    (3) Attach the table again to the original table.
    (4) Run Integrity.

    Hope this helps someone! 🙂

    Example:
    (1) alter table SALES
    detach partition P201602
    into SALES201602;

    (2) CALL SYSPROC.ADMIN_MOVE_TABLE (
    ‘TEST’ — table schema name
    ,’SALES201602′ — table name
    ,’TSP_SALES_SLOW’ — tablspace data
    ,’TSP_SALES_SLOW’ — tablespace index
    ,’TSP_SALES_SLOW’ — tablespace lob
    ,” — MDC columns
    ,” — ??
    ,” — ??
    ,” — ??
    ,” — ??
    ,’MOVE’
    );

    (3) alter table SALES
    attach partition P201602 starting ‘2/1/2016’ ending ‘2/28/2016’
    from SALES201602
    build missing indexes;

    (4) set integrity
    for SALES
    ALLOW WRITE ACCESS
    IMMEDIATE CHECKED
    FULL ACCESS;

  11. Arshid says:

    Can we have a same table name in different table spaces in db2 in the same database. I know it is possible at schema level. I have done the same using export/import using different servers. Ist take export then move the export file(.ixf)to another server and import using the different table space. But within the same server and same database can we do it????

Leave a Reply

Your email address will not be published. Required fields are marked *