select 'DROP TABLE "'||tabschema||'"."'||tabname||'" ;'
from syscat.tables
where date(current timestamp) - date(create_time) > 60
and tabschema not like ('%SYS%')
;The above command will generate the drop statement for table > 60 days old.
Enclose the above command in a file ...say gen.drop.sql and run it redirecting the output to a file..
db2 -tvf gen.drop.sql > drop.cmd
now run the drop.cmd file to do the actual drop db2 -tvf drop.cmd > drop.out
You could do the same via script too....
Umair Hussain******************************** DBA Consultant * DB2 UDB, Oracle, Sybase, DataJoiner * IBM Certified Solution Expert - DB2 UDB DBA * IBM Certified AIX System Administrator * ********|All Disclaimers Apply|**************
From: "Johnson, Shaunn" <[EMAIL PROTECTED]> Reply-To: "Johnson, Shaunn" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [DB2EUG] get a tables creation date Date: Tue, 25 Feb 2003 13:33:20 -0500
Howdy:
Is there a way to get a tables age from within the database?
What I mean is, I know that I have created a list of temporary tables, but I may not know what they are. I would like to create a script that drops those tables by a date parameter (if the age of t_table is > 60 days; drop t_table) rather than dropping the tables by name association (t_table).
Suggestions? Comments?
Thanks!
-X
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
- ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
