Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: How to drop a schema/user from db2 command line

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your script could read the catalog and find out what objects belong to the schema and then drop them.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    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

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Some drops may automatically drop dependent objects, but I am not sure which ones. Consult the documentation or run some tests.
    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
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2004
    Posts
    3
    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?

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You are correct that the RESTRICT keyword is required.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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