Archives of the TeradataForum

Message Posted: Thu, 22 Apr 2004 @ 15:27:05 GMT

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

I run it when needed. It doesn't take long to run it for a period of two or three days.

The report is a really long text file without sear4ch capabilities, To make it useful, I exoport it and open it in notepad or word.

As an alternative, I have two views (see below) that were stolen from some of John Carpenter's macros. They make it real easy to figure out what to do. You have to specify the locking logger table to use them.

Glen

     replace view O10782.analyze_locklog as
          select a.begdate as begin_Date,a.begtime as begin_Time,
          b.databasename as Databasename,
          c.tvmname as Tablename,
          d.username as Blocked_Username,  d.sessionno as BLOCKED_SESSION,
          e.username as Blocking_Username,  e.sessionno as BLOCKING_SESSION,
          a.deadlock,a.stmttype, a.delay
          from O10782.locklog a
        , dbc.databases2 b
        , dbc.tables2 c
        , dbc.logonoff d,
          dbc.logonoff e
          where   a.dbid = b.databaseid
          and  a.tid = c.tvmid
          and  a.blkdsessno = d.sessionno
          and  a.blkingsessno = e.sessionno
          and d.event = 'logoff'
         and e.event = 'logoff'
         AND begin_date * 1000000 + begin_time between d.logondate * 1000000 +
     d.logontime  AND d.logdate * 1000000 + d.logtime
             AND begin_date * 1000000 + begin_time between e.logondate *
     1000000 + e.logontime  AND e.logdate * 1000000 + e.logtime;

     replace view o10782.analyze_locklog_no_blocker as
          select a.begdate as begin_Date,a.begtime as begin_Time,
          b.databasename as Databasename,
          c.tvmname as Tablename,
          d.username as Blocked_Username,
          a.deadlock,a.stmttype, a.delay
          from o10782.locklog a
        , dbc.databases2 b
        , dbc.tables2 c
        , dbc.logonoff d
          where   a.dbid = b.databaseid
          and  a.tid = c.tvmid
          and  a.blkdsessno = d.sessionno
          and d.logondate between a.begdate - 1 and a.begdate
          and d.event = 'logon'
          and  a.blkingsessno = 0;

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