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')
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