System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

Database

System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

Hey All

I have a procedure which creates dynamic SQL statements to collect stats on table after its loaded.

The procedure gathers stats from two tables DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS.

Now this procedure works fine in TD 13.10 but fails in TD 14 saying dbc.COLUMNSTATS does not exist.

Are these table Deprecated in TD 14? Do we have any replacement?

Please Help

Thanks

Ankit

4 REPLIES
Teradata Employee

Re: System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

See the Data Dictionary manual for TD14. Stats information is significantly changed.

DBC.ColumnStatsV, DBC.IndexStatsV, DBC.MultiColumnStatsV exist but are likely not direct replacement for your application.

Re: System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

Thanks Fred.

Yes it seems I will have to change my application code in order to use these views.

Junior Supporter

Re: System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

Hi Fred,

I have a very similar question for my installation.

We have moved from TD 13.10 to Td 14.10 and in one of the queries in stats collection, we have the below query to find out the tables having stats defined on them- on indexes/multicolumn/single column. Now, this query fails saying that dbc.multicolumnstats doesn't exist. I saw the stats manual that you mentioned above and  it does have DBC.ColumnStatsV, DBC.IndexStatsV, DBC.MultiColumnStatsV views mentioned there. But, i don't see column - COLUMNSSTATISTICS in DBC.MultiColumnStatsV and INDEXSTATISTICS in view DBC.IndexStatsV. Also, as you mentioned above, these might not be a direct replacement for my views, then from where can i get the required information here ?

SELECT DATABASENAME,TABLENAME  

FROM DBC.INDEXSTATS 

WHERE INDEXSTATISTICS IS NOT NULL  

UNION

  SELECT DATABASENAME,TABLENAME  

FROM DBC.MULTICOLUMNSTATS 

WHERE COLUMNSSTATISTICS IS NOT NULL

================================

I can get the multicolumn stats from the query below, but don't we have a view like dbc.multicolumnstats in 13.10 with such information ?

SELECT db.DATABASENAME,t.TVMName , s.expressionlist

   FROM DBC.StatsTbl AS s

   JOIN DBC.Dbase AS db

     ON s.DatabaseId = db.DatabaseId

   JOIN DBC.TVM AS t

     ON s.ObjectId = t.TVMId

     WHERE ExpressionCount > 1

     and db.databasename = 'DBname'

     and t.TVMName ='tablename'

I have posted the same query to Diether's blog as well.

--Samir

Teradata Employee

Re: System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14

With the TD14 views, rows will be only returned when stats are present, so there is no need for the NOT NULL filter in the above queries.

(Alternatively, if you were interested in finding indexes without stats, you might query dbc.IndicesV WHERE NOT EXISTS corresponding dbc.IndexStatsV.)

Not sure I understand your second question. There is a view named dbc.MultiColumnStatsV in both TD14 and TD13.10, but the definitions are quite different. TD14 does not have a dbc.MultiColumnStats (without the V).