Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 28 Dec 2011 @ 10:45:07 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Find Last Update date for a table
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, December 28, 2011 05:39 -->


In most of the Teradata production systems, DBQL logging will be enabled. In this case, all the queries will be logged in the DBQL tables. You can join DBC.QRYLOG and DBC.QRYLOGSQL based on query_id and then filter out by the username or by the sqltextinfo.

     Sel *
     ,           DBC.QRYLOGSQL B
     AND  A.USERNAME like  '%user%'
     and B.SQLTEXTINFO LIKE '%give_ur_table_name_here%';

Usually, DBQL will be holding only the current day's data. So, if you are looking at previous days queries, then you need to check the PDCR tables.

The following sql may be used:

      pdcrdata.DBQlogtbl_hst lg
      , pdcrdata.DBQlobjtbl_hst sq
      , pdcrdata.DBQLSqlTbl_Hst tx
     WHERE lg.procid = sq.procid
     AND lg.username like '%username%'
     and sq.ObjectTableName = 'table_name_which_you_want_to_check'
      and lg.logdate > any_date
      and sqltextinfo like '%insert/update/delete%'

Also, wanted to point out that we don't have anything called Truncate in Teradata. Truncate functionality is achieved by DELETE itself.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023