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 ?
If on DB2 10 for z/OS the manual suggests the following
To move a table to a table space of a different page size:
- Unload the table using UNLOAD FROM TABLE or REORG UNLOAD EXTERNAL FROM TABLE.
- Use CREATE TABLE LIKE on the table to re-create it in the table space of the new page size.
- 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.
- Drop the original table.
- Rename the new table to the name of the old table using RENAME TABLE.
- Re-create all dependent objects.
- Rebind plans and packages.
- 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
The
ADMIN_MOVE_TABLEstored 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.
