ObjectUseCountCollectRate displays as null

Database
Junior Supporter

ObjectUseCountCollectRate displays as null

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?

thanks

3 REPLIES
Senior Apprentice

Re: ObjectUseCountCollectRate displays as null

Hi Brian,

 

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.

 

HTH,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Supporter

Re: ObjectUseCountCollectRate displays as null

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 ?

Senior Apprentice

Re: ObjectUseCountCollectRate displays as null

Hi Brian,

 

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 15.10.1.1 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.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com