Are there any standard TD scripts that I can reuse/ download for maintaining DBC history? I found scripts for DBQL. But am looking for maintenance for the following:
Acctg or AMPUsage:
Any other process that hogs DBC space that I should consider dumping out? Logonoff? We are not capturing AccessLog anymore.
AFAIK there are no "official" standard scripts (but when you ask your Teradata PS they can probably provide you with some).
The process is similar to DBQL when you want to keep a history:
move the data to a temp table/delete the old/merge it into the history table
The history table are usually clones of the dbc tables with lots of COMPRESS and might get a better PI and PARTITION BY date.
During the merge you might do some some cleanup/rollup to reduce the datasize.
Especially in TD14 the dbc.EventLog (session history = dbc.LogonOff) will grow very fast and should be maintained, too.
Other tables which are usually not historized, just "delete rows older than x" are sw_event_log and the rc* tables.
And i prefer not to keep lots of days in dbc because when you got a system with SSDs/Virtual Storage all dbc tables are considered hot (and old DBQL/ResUsage is defintely not hot).
In best case you move all the rows to temp: FastPath delete without Transient Journal -> much faster (but historization scripts might be a bit more complex). Of course you need to create a UNION ALL view which covers both, dbc + hist.
Thank you Dieter for your reply. Appreciate it and I always look forward to your expert advice on other threads as well. We are on 13.00 Appliance.
So, once these scripts are there, what is the recommended way of running them nightly? Is it through Teradata Manager/ cron scripts?
Would you have an example of how to schedule through a cron job?
You run it using a BTEQ script submitted by your company's scheduling tool, this might be a simple cron job or CONTROL-M or UC4 or whatever.