Dropping a non-empty schema in DB2 is not as easy as it seems. The schema must be empty in order to be dropped, i. e. it cannot have any objects in it.
The following script procedure will drop a schema after all tables have been dropped:
/**
* Drops an entire schema even if it has tables.
* Views are not supported.
* @schemaName: the name of the schema to drop
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.DROP_SCHEMA(
schemaName VARCHAR(50)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtDropSchema VARCHAR(250);
DECLARE stmtDropTable VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH RETURN FOR V_STMT;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
-- Copy tables and views
SET V_SQL = 'SELECT name FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || TRIM(schemaName) || '''
AND CREATOR NOT LIKE ''SYS%''';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;
fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName;
IF at_end <> 0 THEN LEAVE fetch_loop;
ELSE
SET stmtDropTable = 'DROP TABLE ' ||
schemaName || '.' || tableName;
EXECUTE IMMEDIATE stmtDropTable;
END IF;
END LOOP fetch_loop;
CLOSE TGT_TABLE_CUR;
-- Drop schema
SET stmtDropSchema = CHAR('DROP SCHEMA ' concat
CHAR(schemaName) concat ' RESTRICT');
EXECUTE IMMEDIATE stmtDropSchema;
END#
* Drops an entire schema even if it has tables.
* Views are not supported.
* @schemaName: the name of the schema to drop
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.DROP_SCHEMA(
schemaName VARCHAR(50)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtDropSchema VARCHAR(250);
DECLARE stmtDropTable VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH RETURN FOR V_STMT;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
-- Copy tables and views
SET V_SQL = 'SELECT name FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || TRIM(schemaName) || '''
AND CREATOR NOT LIKE ''SYS%''';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;
fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName;
IF at_end <> 0 THEN LEAVE fetch_loop;
ELSE
SET stmtDropTable = 'DROP TABLE ' ||
schemaName || '.' || tableName;
EXECUTE IMMEDIATE stmtDropTable;
END IF;
END LOOP fetch_loop;
CLOSE TGT_TABLE_CUR;
-- Drop schema
SET stmtDropSchema = CHAR('DROP SCHEMA ' concat
CHAR(schemaName) concat ' RESTRICT');
EXECUTE IMMEDIATE stmtDropSchema;
END#
Note that the procedure ends with a # instead of the regular ; terminator.
Also note that the procedure is created under the COPY_DATABASE_SCHEMA schema, which most likely may not exist in your database.
To use this just
CALL COPY_DATABASE_SCHEMA.DROP_SCHEMA('deleteSchemaName');