Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: How to write a script to drop a schema

    Since DB2 will not allow dropping a schema with anything in it, I would like to make a little script to save me the pain and agony of clearing it out by hand every time. I tried this:

    BEGIN ATOMIC
    FOR V AS
    SELECT TABNAME FROM SYSCAT.TABLES
    WHERE TABSCHEMA='DIE_SCHEMA'
    DO DROP TABLE TABNAME;
    END FOR;
    END@

    but of course that would be to easy. It fails because DROP TABLE is not DML.

    So how does one automate this all to common yet painful task?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use a cursor, fetch loop and dynamic SQL.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is a stored procedure that will do that for you:

    connect to sample;
    call SYSPROC.ADMIN_DROP_SCHEMA('DB2INST1',NULL,'ERROR', 'DROP_ERROR');


    This assumes that the database is sample and the schema to drop is 'DB2INST1'

    There is also a SP to copy one schema name to another:

    call SYSPROC.ADMIN_COPY_SCHEMA('DB2INST1','DB2INST2,'CO PY',NULL,NULL,NULL,'ERROR','ADMIN_COPY_ERRORS');

    do a DB2 infocenter search for more info on these and other useful SP's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For some reason the word 'COPY' displays as 'CO PY' in the second command and I can't fix it (looks fine in edit mode).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would also use the ADMIN_DROP_SCHEMA procedure that Marcus mentioned for the simple reason that not only table objects are an issue but you could also have routines (stored procs or UDFs), triggers, or other things in the schema, which may have cross-dependencies and need to be dropped in a specific order.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •