How to find last DML query on a table in a given day?

Database
Tourist

How to find last DML query on a table in a given day?

I have a set of tables, that I need to monitor - let's say, that at the end of the day I want to have a list of tables, that state has somehow changed (DDL or DML).

 

As I understand, getting the Timestamp of the last time a table was affected by the DDL query can be obtained from LastAlterTimeStamp field in DBC.Tables. What about DML queries though? My approach was to get all the Insert/Update/Delete queries invoked in a given day from DBC.DBQLogTbl (filtering by StatementType field) and then somehow filter out those, that does not affect any of the tables I need to monitor. I wanted to do that using DBC.DBQLObjTbl, but unfortunately there is no information about which of the tables, that appeared in the query, were actually updated - so I can fairly easily obtain all DML queries that any of the tables, I am interested in, appeared in, but can not determine which one of those queries actually upadated one of those tables. Another approach I see is trying to parse QueryText with regex, but that is very hard given the diversity of the queries I would have to analyze.

 

So is there a way to obtain that kind of information? Is my appraoch - up to this point - correct, or am I missing something? Are the fields I am using (like StatementType) reliable, should I pay special attention to any other fields (for example, at the beginning I forgot to filter out queries with ErrorCode other than 0)? By the way, my approach could be totally wrong, I am fairly new to this - if so, please guide me in the right direction. 

 

I have tried looking for an answer on the forum, but had no luck so far:

https://community.teradata.com/t5/Database/Identifying-when-the-last-update-or-insert-was-done-on-a/...

https://community.teradata.com/t5/Database/How-to-find-when-the-table-was-updated-in-Teradata-Databa...

 

All best,

Michał

Tags (4)