How to release space after deleting a table in DB2?

1 year ago
Ava Mitchell
2 minutes

In DB2, when you delete a table, the space is not immediately released. Instead, DB2 keeps the space of the deleted table for future use. This is done to improve performance and avoid frequent space allocation and deallocation.

If you are sure that the space of the table you have deleted can be released, you can follow these steps to free up space:

  1. Firstly, after deleting a table, you should commit the current transaction and disconnect from the database. This will ensure that no other sessions are utilizing the space of the deleted table.
  2. Next, log in to the database using the DB2 command line window or the DB2 Control Center (GUI tool).
  3. Execute the following command to rebuild the directory of the database’s catalog table spaces:
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('<database_name>')

Replace with the name of your database.

  1. Execute the following command to reorganize the database table spaces:
CALL SYSPROC.ADMIN_MOVE_TABLE('<table_space_name>', '<table_name>', '<new_table_space_name>')

Replace with the tablespace name of the deleted table, with the name of the deleted table, and with a new tablespace name.

  1. Finally, reconnect to the database using the DB2 command line window or DB2 Control Center and verify that the space of the deleted table has been released.

Please make sure to backup the database before proceeding with these steps in order to prevent any unexpected situations. Additionally, these steps may take some time to complete, depending on the size and complexity of the database.

bannerAds