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

    Question Unanswered: DROP table by Userid at once

    Hi,

    Is there a way to Drop all the tables created by One Mainframe ID on DB2 z/os. Please advise.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by perumj1 View Post
    Is there a way to Drop all the tables created by One Mainframe ID on DB2 z/os. Please advise.
    In two steps this is indeed possible:
    (1) collect the table names from SYSIBM.SYSTABLES, thereby generating the DROP statements
    (2) in a second pass, run the SQL generated in step (1) by cut/pasting it into your SQL frontend (e.g. SPUFI).

    Here is a possible SQL statement for pass 1, assuming the user ID is XYZ:

    SELECT 'DROP TABLE XYZ.'||rtrim(name)||';'
    FROM sysibm.systables
    WHERE creator = 'XYZ' AND type = 'T'

    (Don't forget the "type" condition: this way only "normal" tables are dropped. Views on those tables will automatically be cascade-dropped.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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