Archives of the TeradataForum

Message Posted: Fri, 03 Jul 2009 @ 14:44:23 GMT

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

Here is some similar code we run, to validate when activity has occurred for a given table. I submit this, since it contains some relationships between certain TD log tables, so might be a guide for you. This SQL is run interactively from BI/Query, so the <> form is a user input (table name searched), or <> determines how far back in time to search for activity. We extensively use such MDM and meta data to validate, govern, and qualify data and data structures.

     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
                         , BC.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;

Quincy Leslie

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