All Forums Database
ab186024 16 posts Joined 11/11
18 Sep 2015
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.
 
 
 

ABH
ab186024 16 posts Joined 11/11
21 Sep 2015

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
 

ABH

gskaushik 56 posts Joined 09/10
26 Sep 2015

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');

 

Regards
Subramanian kaushik Gurumoorthy

You must sign in to leave a comment.