Build a query that extracts the COLLECT STATS sql command for a certain DB

Database
Enthusiast

Build a query that extracts the COLLECT STATS sql command for a certain DB

I need a query that extracts the already existing collect stats sql commands on a DB because we will upgrade it, and after the upgrade we need to run the collect stats for each column and for every table right after the upgrade.

 

There are columns that are collected with a percentage...and some collect stats are on 2 or more columns..

 

Is there an already built query for that?

 

Thanks in advance.

3 REPLIES
Teradata Employee

Re: Build a query that extracts the COLLECT STATS sql command for a certain DB

If you really want to recollect everything, just make a list of tables and make a set of:

COLLECT ON table name;

When you collect on table without any other options it recollects all existing statistics.
Enthusiast

Re: Build a query that extracts the COLLECT STATS sql command for a certain DB

Does the upgrade drop the current stats? I dont think so...but just to confirm..

 

I extracted the information form columnstatsv and MultiColumnStatsV and imported them into excel and built the queries i needed...

 

Tags (1)
Teradata Employee

Re: Build a query that extracts the COLLECT STATS sql command for a certain DB

An upgrade (software or hardware) to a teradata system has no effect on existing statistics.

While not strictly necessary, it is generally advised to recollect stats after an upgrade to ensure that stale stats do not cause poor plans and to take advantage of new features in the stats mechanisms.

The collect on table method will be fully capable of performing that recollection. I forgot to mention to include join indexes in your list of objects on which to recollect.