Archives of the TeradataForum

Message Posted: Mon, 02 Aug 2004 @ 20:02:29 GMT

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

Anomy,

This is hokey, but works 99.99% of the time in our shop:

     Select dbase.DatabaseNameI as User_ID
     ...
     From  DBC.DBQLogTbl
     ,     DBC.Dbase
     ,     DBC.DBQLObjTbl
     ...
     Where dbqlogtbl.UserID       =  dbase.DatabaseID
     and      dbqlobjtbl.ProcID   =  dbqlogtbl.ProcID
     and      dbqlobjtbl.QueryID  =  dbqlogtbl.QueryID
     and      dbqlogtbl.CollectTimeStamp between
           (dbqlobjtbl.CollectTimeStamp  -  Interval '6' Hour)
           and
           (dbqlobjtbl.CollectTimeStamp  +  Interval '6' Hour)
     ...

Basically, QueryIDs get recycled when the max QueryID value (2 billions something) is hit or when the node takes a restart. So, if you have multiple restarts within a 12 hour period, this solution may not work.

The between logic in the above SQL makes for some horrid join plans if you are working with large tables. I suggested filtering on both sides of the join:

     ...
     Where ...
     and      dbqlogtbl.CollectTImeStamp   >=   '2003-10-08 03:00:00.00'
     and      dbqlobjtbl.CollectTimeStamp  >=   '2003-10-08 03:00:00'
     ...

One would assume (based upon the recycling of QueryID and the odd choice of NUPIs) that Teradata didn't think we (the users) would be joining the DBQL tables together.

Hope this helps,

Clay Barrineau

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