Hi All,

I am running DB2 7.2.5 on Windows 2000 server and want to create a sql script that users can run to drop their schema. This is useful as I work in a test lab and schemas regularly have to be dropped to create a clean environment.

I tried using DROP SCHEMA xxx RESTRICT, but get an error becuase there are objects in the schema. Is there a way to tell db2 (using SQL) to drop all objects in the schema? I have similar scripts for Oracle, using DROP USER CASCADE, but can't seem to find an equivalent statement in DB2.

Any ideas? The script can either drop the schema or drop the user and recreate it.

Thanks
JP

Your script could read the catalog and find out what objects belong to the schema and then drop them.

So there is no single command that can accomplish it? If the script has to query the catalog first, would there have to be a different drop statement for each type of objects?

Thanks
JP

Some drops may automatically drop dependent objects, but I am not sure which ones. Consult the documentation or run some tests.

I checked the SQL Reference. Why are you using RESTRICT?

The RESTRICT keyword enforces the rule that no objects can be defined in the specified schema for the schema to be deleted from the database SQLSTATE 42893).

If I try without restrict, I get the error

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "END-OF-STATEMENT" was found following "drop schema qa6". Expected tokens may include: "RESTRICT". SQLSTATE=42601

Are there any other options other than RESTRICT?

You are correct that the RESTRICT keyword is required.