How to delete a tablespace if it exists in DB2

Question

I've been trying to delete a tablespace if it exists in DB2 (Z/OS) if it exists. The purpose of this is to be able to have a script to create a new dev or replace an existing dev database.

I've been trying something like:

BEGIN 
    IF (exists (SELECT * FROM SYSIBM.SYSTABLESPACES WHERE TBSPACE = 'SGE')) THEN 
        DROP TABLESPACE "SGE";
    END IF;
END @

But it doesn't seem to be taking the DROP (at least on Data Studio 4.1). Is there anything else I should be doing?

Thanks in advance

Try executing the statement dynamically: `EXECUTE IMMEDIATE 'DROP TABLESPACE "SGE"'` — mustaccio, Apr 28 '15 at 19:31
You are right, I should have thought about the execute command beforehand. Thanks! — asemprini87, Apr 28 '15 at 21:57

score 1 · Accepted Answer · answered Apr 28 '15 at 22:02

mustaccio was right on his comment. I left the code like this and it worked for me:

BEGIN 
    IF (exists (SELECT * FROM SYSIBM.SYSTABLESPACES WHERE TBSPACE = 'SGE')) THEN 
        EXECUTE IMMEDIATE 'DROP TABLESPACE "SGE"';
    END IF;
END @

I can do the same for other objects as well such as schemas, etc. I only need to change the exists condition based on what I want to delete.

How to delete a tablespace if it exists in DB2

1 Answers1