0

For some reason, I have created a few tablespaces for testing in DB2, I realized that if I didn't specify which tablespace the table should be created in, DB2 will select it for me.

The question is, I want to delete the unused tablespace, but I am afraid I will delete something that I didn't know. I have checked the tables, index and sequence after dropping the unused tablespace, and the number of rows is the same. Will this checking be enough to conclude the tablespace is good to be dropped?

Kara
  • 6,115
  • 16
  • 50
  • 57
coldholic
  • 161
  • 2
  • 3
  • 12

1 Answers1

1

You can query the catalog in order to retrieve the tables and where they are stored.

select tabschema, tabname, tbspaceid, tbspace
from syscat.tables 
where tabschema not like 'SYS%'"

You can change the where condition, in order to filter the tablespace you are going to drop.

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • 1
    You should include the columns `INDEX_TBSPACE` and `LONG_TBSPACE` in the query – DB2 can write indexes and LOB data into different tablespaces from the base table data. – Ian Bjorhovde Mar 25 '14 at 15:42
  • 1
    You might also consider using the `SYSCAT.DATAPARTITIONS` catalog view instead of `SYSCAT.TABLES` – range partitioned tables will show a NULL in `SYSCAT.TABLES` for the tablespace. – Ian Bjorhovde Mar 25 '14 at 15:45