 |
 |
Archives of the TeradataForum
Message Posted: Fri, 09 Aug 2002 @ 12:02:12 GMT
| Subj: | | Re: Collect statistics |
| |
| From: | | ZAPPET |
I had the same question and was fortunate to have a Teradata contractor working at our site that created the following SQL to do what you
are asking. This will show all column statistics.
sel
c.databasenamei as DatabaseName,
b.tvmnamei as TableName,
a.fieldname as ColumnName,
cast((case 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(a.lastaltertimestamp as char(32)) ,1,10) as date) as
LastAlter,
date - collectiondate as FromCurrent,
lastalter - collectiondate as FromAlter
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 upper(trim(c.databasenamei)) in ('databasename') *Replace with the
database that you want to analyze or delete the row to get all tables on
your system.
and a. fieldstatistics is not null
order by 1,2,3
;
This scripts reports on all index statistics:
sel
a.tableid,
b.tvmnamei,
(case when substr(indexstatistics,1,1) = 'D1'XB
then '2001-'
when substr(indexstatistics,1,1) = 'D0'XB
then '2000-'
when substr(indexstatistics,1,1) = 'CF'XB
then '1999-'
when substr(indexstatistics,1,1) = 'CE'XB
then '1998-'
else 'xxxx-'
end)||
(case when substr(indexstatistics,3,1) = '01'XB
then '01-'
when substr(indexstatistics,3,1) = '02'XB
then '02-'
when substr(indexstatistics,3,1) = '03'XB
then '03-'
when substr(indexstatistics,3,1) = '04'XB
then '04-'
when substr(indexstatistics,3,1) = '05'XB
then '05-'
when substr(indexstatistics,3,1) = '06'XB
then '06-'
when substr(indexstatistics,3,1) = '07'XB
then '07-'
when substr(indexstatistics,3,1) = '08'XB
then '08-'
when substr(indexstatistics,3,1) = '09'XB
then '09-'
when substr(indexstatistics,3,1) = '0A'XB
then '10-'
when substr(indexstatistics,3,1) = '0B'XB
then '11-'
when substr(indexstatistics,3,1) = '0C'XB
then '12-'
else 'xx-'
end)||
(case when substr(indexstatistics,4,1) = '01'XB
then '01'
when substr(indexstatistics,4,1) = '02'XB
then '02'
when substr(indexstatistics,4,1) = '03'XB
then '03'
when substr(indexstatistics,4,1) = '04'XB
then '04'
when substr(indexstatistics,4,1) = '05'XB
then '05'
when substr(indexstatistics,4,1) = '06'XB
then '06'
when substr(indexstatistics,4,1) = '07'XB
then '07'
when substr(indexstatistics,4,1) = '08'XB
then '08'
when substr(indexstatistics,4,1) = '09'XB
then '09'
when substr(indexstatistics,4,1) = '0A'XB
then '10'
when substr(indexstatistics,4,1) = '0B'XB
then '11'
when substr(indexstatistics,4,1) = '0C'XB
then '12'
when substr(indexstatistics,4,1) = '0D'XB
then '13'
when substr(indexstatistics,4,1) = '0E'XB
then '14'
when substr(indexstatistics,4,1) = '0F'XB
then '15'
when substr(indexstatistics,4,1) = '10'XB
then '16'
when substr(indexstatistics,4,1) = '11'XB
then '17'
when substr(indexstatistics,4,1) = '12'XB
then '18'
when substr(indexstatistics,4,1) = '13'XB
then '19'
when substr(indexstatistics,4,1) = '14'XB
then '20'
when substr(indexstatistics,4,1) = '15'XB
then '21'
when substr(indexstatistics,4,1) = '16'XB
then '22'
when substr(indexstatistics,4,1) = '17'XB
then '23'
when substr(indexstatistics,4,1) = '18'XB
then '24'
when substr(indexstatistics,4,1) = '19'XB
then '25'
when substr(indexstatistics,4,1) = '1A'XB
then '26'
when substr(indexstatistics,4,1) = '1B'XB
then '27'
when substr(indexstatistics,4,1) = '1C'XB
then '28'
when substr(indexstatistics,4,1) = '1D'XB
then '29'
when substr(indexstatistics,4,1) = '1E'XB
then '30'
when substr(indexstatistics,4,1) = '1F'XB
then '31'
else 'xx'
end),
substr(indexstatistics,1,8)
from
dbc.indexes a
inner join dbc.tvm b
on ( a.tableid = b.tvmid
and b.tablekind ='T')
where a. indexstatistics is not null;
Enjoy!
Tobi
| |
