Search code examples
db2zos

How to delete a tablespace if it exists in DB2

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

Solution

  • 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.