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.
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,
from dbc.qrylogV S
inner join dbc.qryLogSqlV L
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
To find thetable last access timestamp combine tablesize with tables, solution provided alrady by Dieter and Adarsh
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');