Collect Stats Report

Database
N/A

Collect Stats Report

All,

Is there a way (SQL) we can get information as we get from Teradata Administrator to check statistics information.
Sample output from TD Administrator

Date Time Unique Values Column Names
06/04/01 08:24:09 5 C_ENT_TYPE

I looked up Manuals and found a SQL which gives me part of this information.
I also know that the underlying DBC tables used are Indexes and TVFields.

The problem arises if we need to look up a Multi-Column Stats information.
Do you know a easy way to find this.

I wanted to generate a report to get the statistical information on a daily basis. I can get Statistics information for Indexes (Single/Multi Column) and Single Column Indexes. I need some help on Multi-Column Stats (Non-indexe columns).

Thanks,
Vinay
2 REPLIES

Re: Collect Stats Report

Try :

select * from dbc.multicolumnstats where tablename='XX' and databasename='XX'

Thanks.

N/A

Re: Collect Stats Report

Sathish,

Thanks for sharing the information.
I also wanted the stats date.
I actually used the base View definition of MultiColumnStats to come up with this SQL. I can capture the last refresh time here as well.
I think this information would be sufficient.

Thanks for directing me to the view.
Never thought about looking at the view definition itself.

SELECT
DBC.DBase.DatabaseNameI(NAMED DatabaseName),
DBC.TVM.TVMNameI(NAMED TableName),
DBC.Indexes.IndexNumber(NAMED StatisticsId),
DBC.Indexes.FieldPosition(NAMED ColumnPosition,FORMAT 'Z9'),
DBC.TVFields.FieldName(NAMED ColumnName),
DBC.Indexes.IndexStatistics(NAMED ColumnsStatistics),
DBC.Indexes.LastAlterTimeStamp
FROM DBC.Indexes
LEFT OUTER JOIN DBC.Dbase DB1
ON DBC.indexes.DatabaseId = DB1.DatabaseID,
DBC.dbase,
DBC.TVM,
DBC.TVFields
WHERE DBC.TVM.DatabaseId = DBC.dbase.DatabaseId

AND DBC.TVM.TVMid = DBC.indexes.tableid
AND DBC.TVM.TVMid = DBC.TVFields.tableid
AND DBC.indexes.indextype = 'M'
AND DBC.TVFields.fieldid = DBC.indexes.fieldid
and db1.DatabaseNameI = 'DW_SUPPORT_DB'
order by 3,4;

Vinay