Archives of the TeradataForum

Message Posted: Wed, 25 Sep 2002 @ 17:33:19 GMT

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

I built this view off a Macro that Jon Carpenter gave me

replace view dbcmngr5.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,
e.username as Blocking_Username,
a.deadlock,a.stmttype, a.delay
from DBCMNGR5.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;

Only problem is that sometimes the blocking session number is 0.

Here is his macro

replace macro dbcmngr5.analyze_lock_log as (
sel a.begdate as "Date",a.begtime as "Time",
b.databasename as "Database",
c.tvmname as "Table",
d.username as "Blocked User",
e.username as "Blocking User",
a.deadlock,a.stmttype
from locklog a, dbc.databases2 b , dbc.tables2 c, dbc.logonoff d,
dbc.logonoff e
where begdate >= date-7
and a.dbid = b.databaseid
and a.tid = c.tvmid
and a.blkdsessno = d.sessionno
and a.blkingsessno = e.sessionno
order by 1,2,3,4
;);

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