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.