I need to build a query in Teradata to check what field/indexes/object were accessed/referenced in last one week (for example) for how many times along with timestamp.
Can anyone please help me in doing this? Is it something that needs a modification in DBS Control ?
You can use DBQL objects (dbc.qrylogobjects) for that purpose. At least tables and columns are there.
Another option is to use "accesscount" and "lastaccesstimestamp" fields in dbc views (dbc.tables, dbc.indices, etc.). There is a DBS Control to turn these fields on. But those "accesscount" numbers are always increasing only, not for period of time. So if you want the numbers for a time period, you should take snapshots of data and compare them to get the "delta"s.
You can reset Accesscount and lastaccesstimestamp counters via macros under dbc.
Indeed, the reset of counters is an elegant step here. But still, the counters' values have to be saved somewhere in a different place. Otherwise the data for the past data periods is simply lost.