Hi... I'm running Teradata 15.10. Just noticed in DBC.TABLES - column - LASTACCESSTIMESTAMP contains nulls; The
GDO record displays - ObjectUseCountCollectRate = 10. I'm assuming this is a default since I haven't
reset it, Teradata has been recycled many times... So, I've missed something in order to trigger the collection.
Anyone seen this or have a suggestion?
I think you'll find that you need to use the DBQL USECOUNT option.
Since TD 14.x (not sure if .0 or .10) this information is controlled by query logging rules. This allows you to control this recording by database or user.
If you look at the dbc.tables(v) view definitions, the LASTACCESSxxx columns no longer comes from DBC.TVM etc. but from DBC.ObjectUsage.
Thanks for the info.... I've started query logging but since I'm only interested last access to tables
in a particular database I was forced into using 'with usecount' . Now then..... I have a couple of Q's
1. The Teradata documentation indicates 'When you submit a BEGIN QUERY LOGGING request, the use counts and timestamps for the database or user are reset.'. So, if I ended query logging and then started it again, ran a couple of selects and waited until the time period tripped and DBC.TABLES showed the new accesses to the tables in the selects and the historical accesses to other tables... anyone have any idea what this documentation really means?
2. I've attempted to determine what DBQL tables are accumulating data when I turn query logging on...'WITH USECOUNT' and only for one database .I've checked all DBQL tables and don't see anything
of importance for my purposes......
Since I'm not logging SQL, gathering stats etc. anyone know of any tables I should keep an eye on for space issues ?
3. Other than dropping a table, is it possible to reset the access datetime for a table i.e. clear it completely ?
Some answers for you:
1) The docs say "When you submit a BEGIN QUERY LOGGING request, the use counts and timestamps for the database or user are reset."
- Almost. The data thta is recorded is split into 'system access counts' and 'user access counts'. I tested this on TD 220.127.116.11 and it is the 'end query logging' not 'beging query logging' command which changes something. Also in my testing it is only the 'system access counts' that are zero'd. The 'user access counts' and lastaltertimestamp information is left alone.
2) Where is this data stored? Please see my previous post. This information is in DBC.ObjectUsage (not DBC.DBQLxxx).
3) How to reset the access date time? Use macro DBC.ClearDatabaseUseCount.