When IBM switched from old-school DMS and SMS tablespaces to Automatic Storage tablespaces, it became significantly easier for DBAs to create and manage tablespaces. This can lead to a proliferation of tablespaces; over time, some of these tablespaces may no longer be used in your database. When working to clean up a database and remove any unused tablespaces, the question often arises: Is this tablespace actually in use? Surprisingly, this is somewhat harder to answer than you might expect. This article presents a method for determining whether tablespaces containing user data are actually in use.
The catalog view SYSCAT.TABLES has 3 columns that provide information about which tablespaces are used for a particular table: TBSPACE, INDEX_TBSPACE, and LONG_TBSPACE. If the tablespace in question appears in any of these columns, then you can be sure that it’s in use. For example, if you want to drop the tablespace “MY_TBSP”, you could execute the query:
SELECT count(*) as num_tables FROM syscat.tables WHERE tbspace = 'MY_TBSP' OR index_tbspace = 'MY_TBSP' OR long_tbspace = 'MY_TBSP'
If this query returns a 0, the tablespace is probably not in use. However, this query is not definitive because range-partitioned tables are complicated.
A range-partitioned table might have values in the tablespace columns from SYSCAT.TABLES, but that does not necessarily mean that the tablespace listed is actually in use. Each individual table partition in a range-partitioned table can exist in its own separate tablespaces, and each individual index on a range-partitioned table may exist in an entirely different tablespace. These tablespaces may correspond to the values in SYSCAT.TABLES, but that’s not guaranteed.
The catalog view SYSCAT.DATAPARTITIONS contains an entry for every table in your database – even tables that are not range-partitioned – and provides the tablespace IDs for the data (TBSPACEID), index (INDEX_TBSPACEID) and long (LONG_TBSPACEID) tablespaces associated with each partition.
Indexes on range-partitioned tables can appear in multiple tablespaces, which means that the INDEX_TBSPACEID column in SYSCAT.DATAPARTITIONS is only valid for partitioned indexes. Non-partitioned indexes will be placed in the tablespace identified by INDEX_TBSPACE from SYSCAT.TABLES, unless a tablespace was explicitly specified in the CREATE INDEX statement. To verify which tablespace a particular index actually exists in, it is therefore necessary to look at the column TBSPACEID from SYSCAT.INDEXES.
This is quite complicated, but it is possible to express all of these conditions in a single SQL statement. The following query provides useful output to determine which tablespaces are in use and which are not, regardless of whether your database has range-partitioned tables or not:
WITH tables AS ( SELECT tbspace FROM syscat.tables WHERE tbspace is not null UNION SELECT tbs.tbspace FROM syscat.tablespaces tbs join syscat.datapartitions dp on dp.tbspaceid = tbs.tbspaceid ), indexes AS ( SELECT index_tbspace FROM syscat.tables WHERE index_tbspace is not null UNION SELECT tbs.tbspace FROM syscat.tablespaces tbs join syscat.datapartitions dp on dp.index_tbspaceid = tbs.tbspaceid UNION SELECT tbs.tbspace FROM syscat.tablespaces tbs join syscat.indexes i on i.tbspaceid = tbs.tbspaceid ), long AS ( SELECT long_tbspace FROM syscat.tables WHERE long_tbspace is not null UNION SELECT tbs.tbspace FROM syscat.tablespaces tbs join syscat.datapartitions dp on dp.long_tbspaceid = tbs.tbspaceid ) SELECT char(tbs.tbspace, 30) as tbspace, case when t.tbspace is not null then 'IN_USE' else NULL end as tables, case when i.index_tbspace is not null then 'IN_USE' else NULL end as indexes, case when l.long_tbspace is not null then 'IN_USE' else NULL end as long FROM syscat.tablespaces tbs left join tables t on tbs.tbspace = t.tbspace left join indexes i on tbs.tbspace = i.index_tbspace left join long l on tbs.tbspace = l.long_tbspace WHERE tbs.datatype not in ('U','T');
It’s necessary to eliminate the tablespaces that have a DATATYPE of ‘U’ or ‘T’ because these values represent user- and system-temporary tablespaces, and temporary tables usually do not appear in the system catalog views.
Executing this query will produce output like this:
TBSPACE TABLES INDEXES LONG ------------------------------ ------ ------- ------ SYSCATSPACE IN_USE IN_USE IN_USE SYSTOOLSPACE IN_USE IN_USE IN_USE TEST_DATA - - - TEST_INDEX - - - TM_4K IN_USE IN_USE IN_USE TM_4K_IDX - IN_USE - TM_4K_LOB - - - TS8K IN_USE - IN_USE TS8K_INDEX - IN_USE - USERSPACE1 IN_USE IN_USE IN_USE 10 record(s) selected.
Any tablespaces have a null value for all three columns – TABLES, INDEXES, and LONG – are not currently in use and therefore can be dropped.
With this output, it is possible to determine which tablespaces are actually in use in your database, so you can safely find and drop your unused tablespaces.