Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 09 Aug 2002 @ 12:02:12 GMT


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


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



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016