IBM Mainframe Forum

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.

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.

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).

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:

Guyc,

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

jaggz wrote:Guyc,

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

ty and you're welcome