All Forums Database
teradatauser2 236 posts Joined 04/12
01 Aug 2012
Query to generate collect stats statement automatically

Hi,

say i ahve a tablename and databasename. I have to collect stats on the primay and secondaty indexes of that table(single or multi column).

Is there a query with someone which can help me create that the collect stats statement. The number of tables runs in hundreds, therefore need to automate it, manually its a diffucult task.

Need t it urgently, any help would be appreciated.

 

 

mjj 23 posts Joined 03/10
01 Aug 2012

Hi,

Below statement will generate the collect stats statements for a given table in given database. You can create a macro for this and run it for all tables in your DB and save the collect stats statements in file which can be executed using bteq.

 

select distinct 'collect stats on '|| trim(databasename) || '.' || trim(tablename) ||' on index( ' || trim(columnname) || ');'

from dbc.indices

where trim(databasename) = 'db1'

and trim(tablename)= 'test1'

and Indextype = 'P'

CarlosAL 512 posts Joined 04/08
01 Aug 2012

Hi:

I use this query:

http://carlosal.wordpress.com/2012/08/02/generar-collect-statistics-para-los-indices-primarios-secundarios-y-primarios-particionados/

HTH.

Cheers.

Carlos.

ulrich 816 posts Joined 09/09
02 Aug 2012

Check also dieters blog post

http://developer.teradata.com/blog/dnoeth/2011/12/how-to-decode-the-binary-statistics-stored-in-dbc-tables

the code can be downloaded in the attachement section.

The statsinfo view contains also collect stats statements

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.