Archives of the TeradataForum

Message Posted: Wed, 27 Aug 2003 @ 16:53:11 GMT

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

Stats are kept in dbc.tvfields and dbc.indexes

This query will give you tables without stats

select appl_nm, dbc.dbase.databasename, tvmname
from dbc.tvm,
dbc.dbase,
dba.t_appl_db,
dba.appl_nm
where
appl_nm not in ('DBC','OTHER','DBA','VALUE WORK','SPOOL')
and
dbc.dbase.databaseid = dbc.tvm.databaseid
and dbc.dbase.databasename = t_appl_db.databasename
and t_appl_db.databasename like 'deiw%'
and dba.t_appl_db.appl_id = dba.appl_nm.appl_id
and trim(dbc.dbase.databasename) not like ALL
('%TMPT','DQMDB%','DAVR885T','JUNK','RBDQM%')
and
tablekind = 'T'
and
tvmid
not in
(
select tableid from   dbc.tvfields where fieldstatistics is not null
UNION
select tableid from   dbc.indexes where indexstatistics is not null
)
order by 1,2,3;

This one will give you the last date they were collected

SELECT
databasename
, tablename
, indexcolumnname
, cast((case when substr(fieldstatistics,1,1) = 'D3'XB
           then '2003-'
      when substr(fieldstatistics,1,1) = 'D2'XB
           then '2002-'
           when substr(fieldstatistics,1,1) = 'D1'XB
           then '2001-'
          when substr(fieldstatistics,1,1) = 'D0'XB
          then '2000-'
          when substr(fieldstatistics,1,1) = 'CF'XB
          then '1999-'
          when substr(fieldstatistics,1,1) = 'CE'XB
          then '1998-'
           else NULL
end)||
(case when substr(fieldstatistics,3,1) = '01'XB
           then '01-'
           when substr(fieldstatistics,3,1) = '02'XB
           then '02-'
           when substr(fieldstatistics,3,1) = '03'XB
           then '03-'
           when substr(fieldstatistics,3,1) = '04'XB
           then '04-'
           when substr(fieldstatistics,3,1) = '05'XB
           then '05-'
           when substr(fieldstatistics,3,1) = '06'XB
           then '06-'
           when substr(fieldstatistics,3,1) = '07'XB
           then '07-'
           when substr(fieldstatistics,3,1) = '08'XB
           then '08-'
           when substr(fieldstatistics,3,1) = '09'XB
           then '09-'
           when substr(fieldstatistics,3,1) = '0A'XB
           then '10-'
           when substr(fieldstatistics,3,1) = '0B'XB
           then '11-'
           when substr(fieldstatistics,3,1) = '0C'XB
           then '12-'
          else 'xx-'
end)||
(case when substr(fieldstatistics,4,1) = '01'XB
             then '01'
             when substr(fieldstatistics,4,1) = '02'XB
             then '02'
             when substr(fieldstatistics,4,1) = '03'XB
             then '03'
             when substr(fieldstatistics,4,1) = '04'XB
             then '04'
             when substr(fieldstatistics,4,1) = '05'XB
             then '05'
             when substr(fieldstatistics,4,1) = '06'XB
             then '06'
             when substr(fieldstatistics,4,1) = '07'XB
             then '07'
             when substr(fieldstatistics,4,1) = '08'XB
             then '08'
             when substr(fieldstatistics,4,1) = '09'XB
             then '09'
             when substr(fieldstatistics,4,1) = '0A'XB
             then '10'
             when substr(fieldstatistics,4,1) = '0B'XB
             then '11'
             when substr(fieldstatistics,4,1) = '0C'XB
             then '12'
             when substr(fieldstatistics,4,1) = '0D'XB
             then '13'
             when substr(fieldstatistics,4,1) = '0E'XB
             then '14'
             when substr(fieldstatistics,4,1) = '0F'XB
             then '15'
             when substr(fieldstatistics,4,1) = '10'XB
             then '16'
             when substr(fieldstatistics,4,1) = '11'XB
             then '17'
             when substr(fieldstatistics,4,1) = '12'XB
             then '18'
             when substr(fieldstatistics,4,1) = '13'XB
             then '19'
             when substr(fieldstatistics,4,1) = '14'XB
             then '20'
             when substr(fieldstatistics,4,1) = '15'XB
             then '21'
             when substr(fieldstatistics,4,1) = '16'XB
             then '22'
             when substr(fieldstatistics,4,1) = '17'XB
             then '23'
             when substr(fieldstatistics,4,1) = '18'XB
             then '24'
             when substr(fieldstatistics,4,1) = '19'XB
             then '25'
             when substr(fieldstatistics,4,1) = '1A'XB
             then '26'
             when substr(fieldstatistics,4,1) = '1B'XB
             then '27'
             when substr(fieldstatistics,4,1) = '1C'XB
             then '28'
             when substr(fieldstatistics,4,1) = '1D'XB
             then '29'
             when substr(fieldstatistics,4,1) = '1E'XB
             then '30'
             when substr(fieldstatistics,4,1) = '1F'XB
             then '31'
             else 'xx'
end)as date) AS COLLECTIONDATE,
cast(substr(cast(lastaltertimestamp as char(32))
,1,10) as date) as LASTALTER,
date - collectiondate  as FROMcollection,
lastalter  - collectiondate as fromalter

FROM
(
sel
c.databasenamei,
b.tvmnamei,
CAST(a.fieldname as CHAR(40)),
A.FIELDSTATISTICS
,a.lastaltertimestamp
 from
dbc.tvfields  a,
dbc.tvm b,
dbc.dbase c
where a.tableid = b.tvmid
and b.tablekind = 'T'
and  b.databaseid = c.databaseid
and a. fieldstatistics is not null
UNION
sel
c.databasenamei
, b.tvmnamei
,TRIM(a.name ) || ' #' ||
TRIM(a.indexnumber)
, a.indexstatistics
,a.lastaltertimestamp
 from
dbc.indexes  a,
dbc.tvm b,
dbc.dbase c
where a.tableid = b.tvmid
and b.tablekind = 'T'
and  b.databaseid = c.databaseid
and a. indexstatistics is not null
) stats(databasename, tablename, indexcolumnname, fieldstatistics,
lastaltertimestamp)
WHERE databasename like
('DINF%')
order by 1,2,3
;

Glen

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