Identification of data that is not being used/Cold data using DBQL or other approach

Database
Teradata Employee

Identification of data that is not being used/Cold data using DBQL or other approach

Hi All,

We need to identify data that is not being used/Cold data. What's the best way of identifying data that is not being used/Cold data using DBQL or other approach? Thanking you in advance.

2 REPLIES
Teradata Employee

Re: Identification of data that is not being used/Cold data using DBQL or other approach

I'm thinking of getting the SQL run againt the table, analyse the where predicate and identify the date accessed to identify the cold data:

sel S.username,S.starttime,S.firstresptime , S.elapsedtime, L.SqlTextInfo,

Q.ObjectDatabaseName, Q.ObjectTableName

from    dbc.qrylogV S  

inner join dbc.qryLogSqlV L  

on S.ProcID=L.ProcID 

and  S.QueryID=L.QueryID 

inner join DBC.QryLogObjects Q

on S.ProcID = Q.ProcID

where starttime>='2015-08-30 04:00:00.00' and starttime<='2015-09-22 09:24:00.00' 

and  username = ‘User_Name’ — This can be updated for specific users

and Q.ObjectDatabaseName = ‘Test_DB’ — The database where the transaction tables exist

and Q.ObjectTableName = ‘Tbl_Bal_Single’ — The transaction tables

and L.SqlTextInfo like  ‘%Where Run_Date = Run_Date-1%’ This can be updated to SELECT, UPDATE… and ignore statements like COLLECT STATS….

order by elapsedtime desc;

 

Anyone has a better approach? Thank you

 

Enthusiast

Re: Identification of data that is not being used/Cold data using DBQL or other approach

Hi 

To find thetable last access timestamp combine tablesize with tables, solution provided alrady by Dieter and Adarsh

https://forums.teradata.com/forum/database/tracking-time-when-last-accessed

To get only the accesscount use the accescount from tables view

sel DatabaseName, TableName, AccessCount, LastAccessTimeStamp
from DBC.TablesVX where TableName IN ('tab1', 'tab2', 'tab3', 'tab4');