Dropping all Views dynamically in Db2

Question

I want to include a drop everything section in a script which (re)generates a sample database.

I know I can find a list of views in the current schema and generate the DROP VIEW statements this way:

SELECT 'DROP VIEW ' || tabname || ';'
FROM syscat.tables
WHERE TYPE='V' AND tabschema=current_schema;

What I don’t know is how to go about executing the result.

I have found the following:

• Examples of EXECUTE or EXECUTE IMMEDIATE all have single statements, so I can’t see how to do this with with multiple statements.

I suppose I could try with:

WITH cte(dv) AS (
    SELECT 'DROP VIEW ' || tabname || ';'
    FROM syscat.tables
    WHERE TYPE='V' AND tabschema=current_schema
)
SELECT listagg(dv, ' ')
FROM cte;

• Other examples I’ve found to do this dynamically involve running from the Linux command line, so that doesn’t suit my script - I need to do all of this in the SQL script.

What is the best way of doing this?

I’m running Db2 LUW 11 or 12 (two versions).

Answer 1

Use a compound statement for this:

--#SET TERMINATOR @
BEGIN
  FOR C1 AS 
    SELECT 'DROP VIEW "' || VIEWSCHEMA || '"."' || VIEWNAME || '"' AS STMT
    FROM SYSCAT.VIEWS V
    WHERE VIEWSCHEMA = CURRENT_SCHEMA
    AND NOT EXISTS 
    (
      -- To avoid statistical views deletion associated with indexes using expressions
      SELECT 1 
      FROM SYSCAT.INDEXES I
      WHERE (I.VIEWSCHEMA, I.VIEWNAME) = (V.VIEWSCHEMA, V.VIEWNAME)
    )
  DO
    EXECUTE IMMEDIATE C1.STMT;
  END FOR;
END
@