Sample Code for Generating a List of Table Spaces to Drop

The following sample SQL code can be used to generate a list of the table spaces that must be dropped before applying additive schema changes.

The table spaces can be determined with the following SQL:

--
-- CREATE DROP STATEMENTS FOR EIM PARTITIONED TABLES
--
SET CURRENT SQLID='xxxxxxxx' ; <-- set to current TARGET tableowner
SELECT
SUBSTR(
CONCAT(
CONCAT(' DROP TABLESPACE ' ,
CONCAT(STRIP(S.DBNAME) ,
CONCAT('.',
STRIP(S.NAME)
)
)
)
, ' /'
)
, 1, 36) AS STATEMENT
FROM SYSIBM.SYSTABLES T
, SYSIBM.SYSTABLESPACE S
WHERE T.CREATOR = CURRENT SQLID
AND T.NAME LIKE 'EIM_%'
AND T.DBNAME=S.DBNAME
AND T.TSNAME=S.NAME
AND T.TYPE='T'
AND S.PARTITIONS>0
ORDER BY 1;

The following is an example of a list of tables generated by running the query in the sample code:

DROP TABLESPACE D0020004.H0004000 /
DROP TABLESPACE D0020010.H0010000 /
DROP TABLESPACE D0020031.H0031000 /
DROP TABLESPACE D0020065.H0065000 /
DROP TABLESPACE D0020102.H0102000 /
DROP TABLESPACE D0020194.H0194000 /
DROP TABLESPACE D0020255.H0255000 /
DROP TABLESPACE D0020309.H0309000 /