Archives of the TeradataForum

Message Posted: Wed, 01 Dec 2004 @ 18:21:40 GMT

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

Here ya go...

This assumes you are logging the SQL in the DBCSQLTbl. Also remember, you will have to wait until the DBQL info has been flushed from memory to disk (which can take a while on a slow system if you aren't willing to manually flush it.) Also, if you system has taken a restart, you might get some bad results do to the re-use of QueryIDs.

     Select   logtbl.SessionID
     ,     logtbl.ExpandAcctstring
     ,     db.DatabaseNameI as User_ID
     ,     db.CommentString as User_Name
     ,     ((logtbl.LastRespTime - logtbl.StartTime) Day(4) to Second) as Duration
     ,     logtbl.FirstStepTime
     ,     logtbl.LastRespTime
     ,     sqltbl.SQLTextInfo as SQLStatement
     From  DBC.DBQLogTbl logtbl
     ,     DBC.Dbase db
     ,     DBC.DBQLSQLTbl sqltbl
     Where logtbl.UserID     =  db.DatabaseID
     and      sqltbl.ProcID        =  logtbl.ProcID
     and      sqltbl.QueryID       =  logtbl.QueryID
     and      logtbl.CollectTimeStamp between
           (sqltbl.CollectTimeStamp   -  Interval '24' Hour)
           and
           (sqltbl.CollectTimeStamp   +  Interval '24' Hour)
     and      (
           sqltbl.SQLTextInfo      like  '%CBS_ME_VIEWS%'
           )
     and      logtbl.CollectTimeStamp >= '2004-11-08 00:00:00.00'
     and      sqltbl.CollectTimeStamp >= '2004-11-08 00:00:00'
     and      User_ID     =  'E50520'

Claybourne L. Barrineau
Senior Application DBA
Coca-Cola Enterprises, Inc.

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