Dropping Primary keys, Index and tables at once.



IBM's flagship relational database management system

Dropping Primary keys, Index and tables at once.

Postby jaggz » Mon Jan 10, 2011 1:32 pm

Hi,

Is it possible to drop all the primary keys, Index and tables created by user. Could you please let me know if this is feasible.
User avatar
jaggz
 
Posts: 356
Joined: Fri Jul 23, 2010 8:51 pm
Has thanked: 8 times
Been thanked: 5 times

Re: Dropping Primary keys, Index and tables at once.

Postby dick scherrer » Tue Jan 11, 2011 4:03 am

Hello,

Dropping them will/should be easy - identifying exactly what you want to delete may not. . .

Before working with the "how" you need to post the rules for identifying the objects to be deleted.

On most systems "users" do not create tables or other database objects.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Dropping Primary keys, Index and tables at once.

Postby Akatsukami » Tue Jan 11, 2011 4:38 pm

Once again, I'll begin by noting that any resemblance between my shop and a well-managed system is purely coincidental.

That said, on our development sysplex, users creating tables is a very frequent event. Whilst I don't have the DB2 usage statistics to hand (not my job to keep them), the testing tool usage statistics (which it is my job to keep), combined with my knowledge of the applications being tested, suggest that users create tables hundreds of times a day. Indeed, it is so frequent that we have a mod to our development DB2 subsystems to give objects a retention period, like data sets (and yes, expiring those katrillions of objects at midnight does tie up the sysplex for 5-10 minutes).

I think that one of the first questions to be asked of anyone posting a vaguely DBAish or sysadminish query in these fora ought to be, "Is this in a development or production environment?" I do -- it is part of my job to do -- all sorts of things in the development environment that would quite justifiably get me terminated if I were to do them in the production environment.

That said, I do not think that all the objects created by a given user can be dropped through SQL alone (albeit I am not the world's greatest expert on SQL); I think you need a little Rexx or some such to loop through the DB2 catalog tables. I'll investigate further when and if I get into my office (the weather is rather threatening here and now).
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: Dropping Primary keys, Index and tables at once.

Postby GuyC » Tue Jan 11, 2011 7:22 pm

if you drop a table , all indexes, relations get dropped with it.
Only the database and the tablespace (if not implicitly created) remain.

select char('drop table "' !! strip(creator) !! '"."' !! strip(name) !! '" ;') from sysibm.systables where creator = 'userid'
generates all drops necessary.
run dsntiaul with this select and then run DSNTIAD with the output.

actually we run the follwing to clean up all user-created tables which are older than 14 days and who didn't bother to specify a database or tablespace:

SELECT CHAR('DROP TABLE "'                   
             CONCAT RTRIM(CREATOR)           
             CONCAT'"."'                     
             CONCAT RTRIM(NAME)             
             CONCAT '";')                   
FROM SYSIBM.SYSTABLES                       
WHERE TYPE IN ('M','T')                     
AND (DBNAME = 'DSNDB04' OR DBNAME LIKE 'DSN00%')                   
AND (ALTEREDTS + 14 DAYS) < CURRENT_TIMESTAMP
FOR FETCH ONLY WITH UR;                     
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Dropping Primary keys, Index and tables at once.

Postby jaggz » Wed Jan 12, 2011 2:54 pm

Guyc,

As usual your DB2 advice works wonder. now my works gets more easier. Thanks again
User avatar
jaggz
 
Posts: 356
Joined: Fri Jul 23, 2010 8:51 pm
Has thanked: 8 times
Been thanked: 5 times

Re: Dropping Primary keys, Index and tables at once.

Postby GuyC » Wed Jan 12, 2011 4:23 pm

jaggz wrote:Guyc,

As usual your DB2 advice works wonder. now my works gets more easier. Thanks again

ty and you're welcome
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post