Archives of the TeradataForum

Message Posted: Mon, 03 May 2010 @ 14:48:48 GMT

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

There are Teradata log tables that contain the desired information referenced below.

     PRODSTAT.DBQLOGTBL
     PRODSTAT.DBQLOBJTBL
     DBC.LOGONOFF

Some sample code: please analyze before assuming runs exactly as you desire.

     SELECT
     L1.OBJECTDATABASENAME AS DBNM    ,L1.OBJECTTABLENAME         AS TBNM   ,
                     L1. ACTION  , L1.LOG_JOB AS JOB_ID  , L1.LOGONSOURCE AS LOGONSRC ,
                     L1.ADT ,L1.START_TIME ,L1.TBL_USER ,L1.LOG_USER
     FROM     (
     SELECT
     CASE    WHEN A.USERNAME = 'ASCENTIAL_ETL_USER'          THEN 'DS JOB'
     ELSE    TRIM(SUBSTR(C.LOGONSOURCE,17,8))
     END     AS LOG_JOB      ,C.LOGONSOURCE    , B.OBJECTDATABASENAME    ,
                     B.OBJECTTABLENAME    ,A.LOGDATE as ADT    ,A.StartTime as
     START_TIME   ,
                     A.USERNAME as TBL_USER   ,C.USERNAME as LOG_USER ,
     A.EXTRAFIELD5 AS ACTION
     FROM                   PRODSTAT.DBQLOGTBL_HST           A
     ,PRODSTAT.DBQLOBJTBL_HST         B              ,
                     DBC.LOGONOFF                            C
     WHERE             C.LOGDATE <= DATE - 
             AND     C.LOGDATE >= DATE - 
             AND     A.LOGDATE=C.LOGDATE
             AND     B.LOGDATE=C.LOGDATE
             AND     A.QUERYID       = B.QUERYID
             AND     C.SESSIONNO = A.SESSIONID
             AND     A.USERNAME IN ('ESP', 'MLOADP', 'ASCENTIAL_ETL_USER')
             AND     B.OBJECTTABLENAME <> 'MLOAD_ERROR_HST'
             AND     B.OBJECTTABLENAME IS NOT NULL
             AND     A.EXTRAFIELD5 <> 'SELECT'
             AND     B.OBJECTTABLENAME=      ) L1
     ORDER   BY 1,2,3,4,5      ;

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