CODEWITHSUNDEEP
db2movetablespace
Koushik Chandra
Koushik Chandra

Reputation: 1491

DB2 - Move table from one to other tablespace

How could a table can be moved from one tablespace (16K page size) to another tablespace(32K page size) in DB2 version 10 onwards? I want to avoid the options like rename, create another table, copy data, drop renamed tables etc.

In Oracle world the same can be achieved through a simple ALTER statement. My question is there anything similar available for DB2 ?

Upvotes: 2

Views: 5884

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

If on DB2 10 for z/OS the manual suggests the following

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/admin/src/tpc/db2z_movetabledifferentpagesize.html

To move a table to a table space of a different page size:

  1. Unload the table using UNLOAD FROM TABLE or REORG UNLOAD EXTERNAL FROM TABLE.
  2. Use CREATE TABLE LIKE on the table to re-create it in the table space of the new page size.
  3. Use DB2® Control Center, DB2 Administration Tool for z/OS®, or catalog queries to determine the dependent objects: views, authorization, plans, packages, synonyms, triggers, referential integrity, and indexes.
  4. Drop the original table.
  5. Rename the new table to the name of the old table using RENAME TABLE.
  6. Re-create all dependent objects.
  7. Rebind plans and packages.
  8. Reload the table using data from the SYSRECnn data set and the control statements from the SYSPUNCH data set, which was created when the table was unloaded.

For Db2 (for LUW), use ADMIN_MOVE_TABLE

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055069.html

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.

Upvotes: 3

Related Questions