Archives of the TeradataForum

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

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

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

Hi,

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 *
     FROM DBC.QRYLOG A
     ,           DBC.QRYLOGSQL B
     WHERE
       A.QUERYID = B.QUERYID
     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:

     FROM
      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.

HTH.

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