Archives of the TeradataForum

Message Posted: Wed, 07 Mar 2002 @ 01:58:16 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Tony,

John's advice of saving the data to a history table and doing a delete all is sound. If, for some reason you choose to delete only certain rows from the ACCTG table there is something critical to be aware of. DBC.Acctg is not hashed. There is a row on every amp for each username in the table. The data cannot be deleted by the following method, even though it is tempting:

Example attempt to delete data from DBC.Acctg for rows created 2 days ago using date portion of ASE:

/* identify rows to delete */
insert into SomeTempTbl
sel username,(substr(accountname,x,6)(date)) from dbc.acctg
where substr(accountname,x,6) = (date-2 (format 'yymmdd'));
/* delete some rows */
delete from dbc.acctg
where username = SomeTempTbl.Username
and substr(accountname,x,6) = SomeTempTbl.DelDate;

This would attempt to hash the join criteria, and would delete from one amp per row that qualifies in the join, but would not delete from the other amps!

If you wished to delete two day old data, you could export the identifying data (username, accountname), then using a BTEQ repeat loop import the exported data into a delete statement.

Example:

Select username, accountname
from dbc.acctg
where substr(accountname,x,6) = (date-2 (format 'yymmdd'));

Then .import the file and use the exported values to do the delete.

There is something to be said for doing this as you won't delete data from dbc.acctg for inflight queries. By taking advantage of the account string expansion and putting in some additional information in the account string you can group users for the purpose of aggregating CPU and DiskIO by groups, individuals, priority, or whatever your interested in. By saving the info into a history table you can track usage trends over time. You can build a whole application around this, and possibly do charge backs to departments based on their usage if it is culturally acceptable in your company. Charge back systems may also include some percentage weighting based on disk space or spool space usage, so you may end up with a historical system usage monitor by the time you're through. I've done that at a previous customer, and it was very helpful for a number of things, the above only being a small sample of what can be done.

Hope you have some fun with it.

Sam Mosley

	<Prev	Next>		<<First	<Prev	Next>	Last>>