Teradata Urgent Help!

Tools
N/A

Teradata Urgent Help!

Hi ,

I need to find out the tables/views in teradata which have not been used from last 6 mths .(none of these select/Insert/update/delete should be performed)
and the tables should not been accessed via mainframe (in last 6 mths) . Could anyone please help me. its urgent
4 REPLIES

Re: Teradata Urgent Help!

Hello,
all you can get is an access count (any operation) and a timestamp of last access from DBC. Columns are AccessCount and LastAccessTimestamp from DBC.Tables.

Btw, you should have activated ObjectUseCountCollectRate in DBS Control Utility before...

Regards
N/A

Re: Teradata Urgent Help!

The table view script show the people and how many times they have accessed a particular table in the last three months.



SELECT B.COMMENTSTRING, LOGDATE

FROM DBC.ACCESSLOG A

LEFT JOIN DBC.DBASE B

ON A.USERNAME = B.DATABASENAMEI

WHERE A.DATABASENAME = '\[UD###\]'

AND A.TVMNAME = '\[TABLE NAME\]'

GROUP BY 1, 2

ORDER BY 1, 2;

Re: Teradata Urgent Help!

Hi,

As mentioned above,

(By default, AccessCount and LastAccessTimestamp will be disabled).

ObjectUseCountCollectRate field in DBS Control must be set to a non-zero value.

Then you can check from dbc.tables...

sel * from dbc.tables where tablename like '%%'

and lastaccesstimestamp<=date-180

Re: Teradata Urgent Help!

Hi, 

I have the same problem, but when i look into dbc.tables the field lastAccessTimeStamp is null.

Do i have to enable resusgaes? or what else i can do?

Thnks!!