Dropping a table space

You can remove an existing table space by issuing a DROP TABLESPACE statement. This can be useful if you plan to change the table space name or plan to recreate the table space with a different set of attributes.

Ensure that if you are removing the a table space, you do not break the requirements of the database access types. Some database access types require at least one table space. Also, ensure that any tables that are assigned to the table space are not affected.

Procedure

  1. Issue an ALTER DATABASE statement to identify the database in which you plan to modify.
  2. Issue a DROP TABLESPACE statement. Ensure that you also provide the IN keyword to reference it to the database it belongs to.
  3. Issue any ALTER TABLE statements to move any tables you need to a different table space (data set group).
  4. Issue a COMMIT DDL statement.

Dropping a table space to recreate it with modified attributes

Procedure

  1. Issue an ALTER DATABASE statement to identify the database in which you plan to modify.
  2. Issue a DROP TABLESPACE statement. Ensure that you also provide the IN keyword to reference it to the database it belongs to.
  3. Issue a CREATE TABLESPACE statement giving it modified attributes. Ensure that you also provide the IN keyword to reference it back to the database it belongs to.
  4. Issue a COMMIT DDL statement.

Dropping a table space to change the table space name

Procedure

  1. Issue an ALTER DATABASE statement to identify the database in which you plan to modify.
  2. Issue a DROP TABLESPACE statement. Ensure that you also provide the IN keyword to reference it to the database it belongs to.
  3. Issue a CREATE TABLESPACE statement giving it a new desired name. Ensure that you also provide the IN keyword to reference it back to the database it belongs to.
  4. Issue any ALTER TABLE statements to move any tables from the old table space over to the new table space (data set group).
  5. Issue a COMMIT DDL statement.