0

for the purpose of testing I have to drop a schema with a large amount of objects often. It consists of > 2000 tables, a large amount of foreign key and check constraints, triggers views and so on. All tables are within the same tablespace, but the tablespace is used by other schemas too.

I did try admin_drop_schema procedure at first, but it takes a lot more time than parsing all schema catalogs, generate all drop statements and execute it as one sql script. The script runs under 45 minutes and the procedure more than a hour. Further more the procedure locks to many things.

What is the fastest way to drop such a schema?

  • @mustaccio thanks for your hint. I did already try admin_drop_schema at first, but it does not seem to be the fastest way. With admin_drop_schema I need more than 1 hour. When I parse the system catalogs and generate a large Sql script with all drop statements it need only 45 min. So I think there some more ways which are faster. – slowjack2k Nov 18 '16 at 8:41
  • Do you have multiple schemas or is this only one in this particular database? – user62379 Nov 22 '16 at 7:46
  • I have multiple schemas within this database. – slowjack2k Nov 22 '16 at 8:56
  • Ages ago I did create a db2utils package which included a DROP_SCHEMA function - no idea if it's faster than ADMIN_DROP_SCHEMA (never tested it for speed) but it might be worth a look? – Dave Jones Nov 22 '16 at 10:43
1
+50

Several years ago I did put together a package of db2utils which included a procedure called DROP_SCHEMA. This was written before ADMIN_DROP_SCHEMA became available in DB2, and uses a fairly crude mechanism for determining what to drop, and in what order. To whit: it uses a large UNION query to get all objects belonging to the schema from the various SYSCAT views:

WITH DROP_LIST (CREATE_TIME, SCHEMA_NAME, DDL) AS (
    SELECT
        CREATE_TIME,
        TABSCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE TYPE
            WHEN 'A' THEN 'ALIAS'
            WHEN 'H' THEN 'TABLE'
            WHEN 'N' THEN 'NICKNAME'
            WHEN 'S' THEN 'TABLE'
            WHEN 'T' THEN 'TABLE'
            WHEN 'U' THEN 'TABLE'
            WHEN 'V' THEN 'VIEW'
            WHEN 'W' THEN 'VIEW'
        END || ' ' || QUOTE_IDENTIFIER(TABSCHEMA) || '.' || QUOTE_IDENTIFIER(TABNAME) AS DDL
    FROM SYSCAT.TABLES
    UNION
    SELECT
        CREATE_TIME,
        TRIGSCHEMA AS SCHEMA_NAME,
        'DROP TRIGGER ' || QUOTE_IDENTIFIER(TRIGSCHEMA) || '.' || QUOTE_IDENTIFIER(TRIGNAME) AS DDL
    FROM SYSCAT.TRIGGERS
    UNION
    SELECT
        CREATE_TIME,
        ROUTINESCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE ROUTINETYPE
            WHEN 'F' THEN 'SPECIFIC FUNCTION'
            WHEN 'M' THEN 'SPECIFIC METHOD'
            WHEN 'P' THEN 'SPECIFIC PROCEDURE'
        END || ' ' || QUOTE_IDENTIFIER(ROUTINESCHEMA) || '.' || QUOTE_IDENTIFIER(SPECIFICNAME) AS DDL
    FROM SYSCAT.ROUTINES
    UNION
    SELECT
        CREATE_TIME,
        TYPESCHEMA AS SCHEMA_NAME,
        'DROP TYPE ' || QUOTE_IDENTIFIER(TYPESCHEMA) || '.' || QUOTE_IDENTIFIER(TYPENAME) AS DDL
    FROM SYSCAT.DATATYPES
    UNION
    SELECT
        CREATE_TIME,
        SEQSCHEMA AS SCHEMA_NAME,
        'DROP SEQUENCE ' || QUOTE_IDENTIFIER(SEQSCHEMA) || '.' || QUOTE_IDENTIFIER(SEQNAME) AS DDL
    FROM SYSCAT.SEQUENCES
    WHERE SEQTYPE <> 'I'
    UNION
    SELECT
        CREATE_TIME,
        SCHEMANAME AS SCHEMA_NAME,
        'DROP SCHEMA ' || QUOTE_IDENTIFIER(SCHEMANAME) || ' RESTRICT' AS DDL
    FROM SYSCAT.SCHEMATA
)
SELECT CREATE_TIME, DDL
FROM DROP_LIST
WHERE SCHEMA_NAME = ASCHEMA!

(Full source; the QUOTE_IDENTIFIER function is another utility in the package)

It then orders the result in descending order by creation time, running the SQL DROP statements with EXECUTE IMMEDIATE:

BEGIN ATOMIC
    FOR D AS
        SELECT DDL
        FROM TABLE(X_DROP_LIST(ASCHEMA))
        ORDER BY CREATE_TIME DESC
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END

This is simple, but crude especially in light of DB2's later introduction of deferred revalidation of objects. This can result in creation timestamp orderings for which the drop sequence is no longer correct. In practice, it mostly worked for me and on the rare occasions it didn't I could fall back to running the query manually to figure out what needed dealing with.

This brings me to the other major difference to ADMIN_DROP_SCHEMA: error handling. ADMIN_DROP_SCHEMA takes several parameters which are used to construct and fill a table with error codes and messages for post-analysis.

While comprehensive, I found this didn't work well with most of my use-cases. Most of the time I was using these tools to tear down development / testing schemas, then tweaking a creation script and re-running it, or using it to destroy relatively simple personal schemas when users were deleted. Hence I designed DROP_SCHEMA deliberately to be atomic: it either works and the schema disappears in its entirety, or it fails with an error and the schema remains in its entirety (assuming you rollback the containing transaction).

I did generally find it faster than ADMIN_DROP_SCHEMA but that wasn't an intention of the design, and given I haven't maintained it for several years there may well be new schema-based objects in DB2 which it doesn't correctly handle. Still, it may serve as a useful starting point for others' efforts.

  • DROP_LIST shows a realy good pattern for ddl statement generation. I did put it to good use for some different use cases too. Maybe you find the time to maintain these scripts once again. A fast db drop & create improves testing environments a lot. In testing environments speed is in my point of view more important than the possibility to do a rollback. – slowjack2k Nov 24 '16 at 18:32
  • I'd love to find some time to do a bit of work on db2utils again - unfortunately I've found little demand for DB2 now I'm freelancing (I say unfortunately because I think it's a genuinely superb engine); what time I have for open source work these days is pretty easily swallowed by picamera and gpiozero. Still, I'd like to return to db2utils at some point; the history utils in particular are some of my favourite (and the regex bits could do with some work) – Dave Jones Nov 25 '16 at 9:57
0

You can use the ADMIN_DROP_SCHEMA stored procedure.

  • Thanks for your answer. I did edit my question to clarify what I have already tried. – slowjack2k Nov 18 '16 at 12:14
0
  1. The tablespace must contain only the schema to remove.
  2. Reorder the script :

    2.1 The rules must be the first to delete

    2.2 After we delete the index

    2.3 Applying cascade suppression

Because time cpu is consumed by the twi first points.

  • The tablespace contains multiple schemas. It's a given, I can't change. Do you mean with point 2.1 that I should first drop all constrains? Does db2 support a single command to disable all cascades? – slowjack2k Nov 24 '16 at 5:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.