Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    33

    Question Unanswered: Dropping Primary keys, Index and tables at once.

    Hi,

    Is it possible to drop all the primary keys, Index and tables created by user. Please share me the SQL query to achieve this objective.

  2. #2
    Join Date
    Apr 2009
    Posts
    42
    check syscat.tables, syscat.indexes and syscat.constraints, see the field "create user" then generate the sql

  3. #3
    Join Date
    Oct 2010
    Posts
    33

    Question

    Hi,

    I would Like to know if we can achieve this Via Batch process in mainframe to drop all the indexes, primary key and tables associated to a userid.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It is sufficient to just drop tables; all dependent indexes and constraints, including PKs, will be dropped automatically.

  5. #5
    Join Date
    Oct 2010
    Posts
    33

    Talking

    Thanks for the clarification

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    perumj1, since you are on the mainframe (z/os I assume), it also depends on how your system is set up.

    If every user has a separate table space, you can drop the table space which will get rid of all dependent objects (tables, Indexes, etc.).

    If every user has a separate database, you can drop the database which will get rid of all dependent objects (table spaces, tables, Indexes, etc.).

    Note: One item that will not be dropped automatically is any Aliases. These have to be dropped separately. Additionally, any Plans or Packages will have to be freed to remove them from the system (they will only be invalidated if the dependent tables / indexes are dropped).

  7. #7
    Join Date
    Oct 2010
    Posts
    33
    Wilson,

    Yes I am from Z/os. Here My challenge is to just free up the tablespaces for One Database. Since No users were able to insert any tables any more and they were getting an error of Sqlcode = -497 error.

Posting Permissions

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