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.

Share This