Difference in lastaccesstimestamp and dbql logdate

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Difference in lastaccesstimestamp and dbql logdate

Hi,

 

I need to get list of obsolete tables to drop unused tables.The query is simple but tables last access timestamp is coming as less than 180 days but has been accessed recently as per dbql log.The list is hugh so very difficult to check tables manually in DBQL(like any is taking time).CAn anybody help why this gap logdatevs lastaccesstimestamp

DBC:

sel b.databasename,a.tablename,a.CreatorName,a.CreateTimeStamp,a.LastAccessTimeStamp,sum(currentperm)/(1024*1024) as MB_SIZE from dbc.tables a
inner join
dbc.tablesize b
on a.tablename=b.tablename
and a.databasename=b.databasename
and a.databasename like 'aw%'
and lastaccesstimestamp<date-180
or (lastaccesstimestamp<date-180 and lastaccesstimestamp is null)
and tablekind='t'
group by 1,2,3,4,5
;

DBQL:

sel LOGDATE,CLIENTID,collecttimestamp,sessionid,queryid,STATEMENTTYPE,substr(QUERYTEXT,1,159),AMPCPUTIME,ParserCPUTime ,SPOOLUSAGE, a.starttime,username,

( ( a.firstresptime - a.starttime ) HOUR( 4 ) TO SECOND( 2 ) ) AS ElapsedTime

from PDCRINFO . DBQLogTbl_v1400 A
/*join PDCRINFO.DBQLSqlTbl_V1400 b
on a.queryid=b.queryid*/
where
LOGDATE>date-180
and querytext like any
(
'%AA_SW_DD_Account_Final%',
'%AA_SW_DD_ACCT%',
'%AA_SW_DD_IPI%',
'%AA_SW_FINAL_2015%',
'%AA_SW_FINAL_2016%',
'%AA_SW_FINAL_COPY%',
'%ACXIOM_EXT_DATA_CONS%'
)

5 REPLIES
Highlighted
Apprentice

Re: Difference in lastaccesstimestamp and dbql logdate

Hi,

I think your mismatch is probably due to a lack of 'object use logging' on the TD system. This feature is not turned on by default and therefore the columns you're querying will not get updated.

Assuming that you're on TD14 or higher then look at the USECOUNT feature of query logging - see https://info.teradata.com/HTMLPubs/DB_TTU_15_10/index.html#page/SQL_Reference/B035_1144_151K/BEGIN_Q....

Cheers,

Dave

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

Re: Difference in lastaccesstimestamp and dbql logdate

Hi,

As per dbql rule..

we have object usgae is true where as other columns are false..for this DB.is this make difference?The dates are like different not null or blank.

DBC.tables have 04/14/2017 whereas dbql says logon select was on 10/09/2017

Enthusiast

Re: Difference in lastaccesstimestamp and dbql logdate

Also can u tell me which DBS display i can see this for further info on parameter

Apprentice

Re: Difference in lastaccesstimestamp and dbql logdate

Hi Minakshi,

 

Firstly looking at your queries, how do the following selection criteria relate to each other in your environment? They do not appear to be obviously related to each other.

a.databasename like 'aw%'

and

and querytext like any
(
'%AA_SW_DD_Account_Final%',
'%AA_SW_DD_ACCT%',
'%AA_SW_DD_IPI%',
'%AA_SW_FINAL_2015%',
'%AA_SW_FINAL_2016%',
'%AA_SW_FINAL_COPY%',
'%ACXIOM_EXT_DATA_CONS%'
)

I understand what each of those is doing, but if you're expecting to be able to compare the results from each query and make a decision then I'm a little bit confused.

 

Also you might not be getting back what you want. If you explain your 'dbc' query I think you'll find that the term is completely ignored:

 (lastaccesstimestamp<date-180 and lastaccesstimestamp is null)

This term by itself can never be satisfied and as it is OR'd with everything else it is simply ignored.

 

If you are trying to find tables where 'lastaccesstimestamp' is over 180 days ago or it is null then you need something like:

sel b.databasename,a.tablename,a.CreatorName,a.CreateTimeStamp,a.LastAccessTimeStamp,sum(currentperm)/(1024*1024) as MB_SIZE from dbc.tables a
inner join
dbc.tablesize b
on a.tablename=b.tablename
and a.databasename=b.databasename
and a.databasename like 'aw%'
and (lastaccesstimestamp<date-180 or lastaccesstimestamp is null)
and tablekind='t'
group by 1,2,3,4,5
;

You also asked "Also can u tell me which DBS display i can see this for further info on parameter". If the 'parameter' you're referring to is the USECOUNT then please refer to the link I provided in my first response in this topic.

 

Cheers,

Dave

 

 

 

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

Re: Difference in lastaccesstimestamp and dbql logdate

enable object logging for the databases. It will tell you who and when they were accessed and give you information to make the delete decision.

 

Thx

 

dave