Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Database
Enthusiast

Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Hi

 

I have compared TD 15 and TD 16 dbc.TVFIELDS table and get to see that FIELDSTATISTIC column is not present in newer version. Also I could see that IndexStatistics columns also not found in DBC.INDEXES.

 

Can you please help me with workaround with it?

 

Thanks,

Soumya

9 REPLIES
Senior Apprentice

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Hi,

Use the statistics views (most of them introduced with TD14.10 - I think).

Views like ColumnStatsV, IndexStatsV etc.

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Hi Dave,

 

Tried with both the views. but no luck.

 

In V15 dbc.TVFIELDS has "FieldStatistics VARBYTE(16383) FORMAT 'X(255)',". This is being used in my project. But in V16 I could not see same column in that table.

 

ColumnStatsv and IndexStatsV both used TVFIELDS and INDEXES tables to genarate FieldStatistics  and IndexStatistics column in V15.

 

Can you help me with Workaround for V16.

Junior Contributor

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Stats are no longer stored in dbc.TVFields and dbc.Indexes since TD14, you can't have used them in TD15.

 

In TD14+ there's dbc.StatsV and all other views are based on it, why n't can't you use those?

Enthusiast

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Hi Dnoeth,

 

Thanks for you reply. Actually we were using FieldStatistics (from  TVFIELDS table) and IndexStatistics ( from INDEXES) table to create a STAT.

COALESCE(i.IndexStats,c.FieldStatistics) (VARBYTE(128)) AS STATS

 

Then this STAT is getting used to get values for different columns like

 

SUBSTR(STATS, 1, 4) AS CollectDate_,
   
         SUBSTR(STATS, 5, 4) AS CollectTime_,
      
         HASHBUCKET ('00'xb || SUBSTR(STATS, 9, 1) (BYTE(4))) / TD12 AS StatsVersion,
   
         CASE
           WHEN HASHBUCKET ('00'xb || SUBSTR(STATS, 11, 1) (BYTE(4))) / TD12 = 1
           THEN HASHBUCKET ('00'xb || SUBSTR(STATS, 12, 1) (BYTE(4))) / TD12
         END AS SampleSize,
   
/*** Differences between 32- and 64-bit start here ---> ***/
         SUBSTR(STATS, 13 + 4, 8) AS NumNulls_,

         SUBSTR(STATS, 21 + 4, 2) AS NumIntervals_,

         CASE
           WHEN SUBSTR(STATS, 9, 1) >= '03'xb THEN SUBSTR(STATS, 25 + 8, 8)
         END AS NumAllNulls_,

         CASE
           WHEN SUBSTR(STATS, 9, 1) >= '03'xb THEN SUBSTR(STATS, 33 + 8, 8)
         END AS AvgAmpRPV_,

         CASE
           WHEN SUBSTR(STATS, 9, 1) > '03'xb THEN SUBSTR(STATS, 41 + 8, 8)
         END AS OneAMPSampleEst_,
    
         CASE
           WHEN SUBSTR(STATS, 9, 1) > '03'xb THEN SUBSTR(STATS, 49 + 8, 8)
         END AS AllAMPSampleEst_,
    
         CASE
           WHEN SUBSTR(STATS, 9, 1) >= '03'xb THEN SUBSTR(STATS, 57 + 8, 2)
         END AS NumAMPs_,

 

Can you please help me with workaorund for this as we could able to genarate these columns in TD15. But after upgrade we are unable to use this as the column in not present.

Senior Apprentice

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Hi,

You'll have to change your code.

 

As @dnoeth said try using the DBC.StatsV view. That probably contais most of what you want without having to substring etc.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Enthusiast

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Yes I have seen this. But in V16 most of those column are not present. Found few of those in DBC.StatsTbl.

 

Like few columns  AvgAmpRPV and OneAMPSampleEst still not able to found

 

Thanks @dnoeth and @DaveWellman for your support. If you find anything related to these please help me out for TD 16.

 

Highlighted
Junior Contributor

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Did you read my link?

Enthusiast

Re: Not able to get FIELDSTATISTICS COLUMN in TVFIELD table in TD 16

Yes @dnoeth I have read this.

AvgAmpRPV

AvgAmpRPV

AvgAmpRPV

StatsId <> 0

Overall average of the average rows per value from each AMP, only for NUSIs, otherwise zer

 

Please correct me I am wrong, but I am not able to find this column in Newer version.