dynamic index collect stats statement

Database
Enthusiast

dynamic index collect stats statement

Hi all,

I would like to execute some dynamic sql that goes out to a table and pulls back the columns of a primary index and plugs them into a collect statistic statement.  Something like:

sel 'collect stats on ' || trim(databasename) || '.' || trim(tablename) || ' index ' || <primary index fields> || ';'

from dbc.<table or view>;

I have an automated stats collection process that collects on different columns based on pre-determined needs (i.e. current_ind).  I haven't been able to find, within TD14, a table or view that lists the primary and secondary index columns on one return row.  I usually run this when a new table in DEV is created to get a minimum amount of statistics coverage from the start.

If anyone has a script that accomplishes this I would be very thankful if they would want to share.

Joe

8 REPLIES
Enthusiast

Re: dynamic index collect stats statement

I was able to utilize a 3th party SP called Run_Index_CS_SP to collect stats on primary and secondary indexes.  Still curious to see if anyone has been able to generate dynamic sql similar to the above in order to obtain and collect stats on table indexes.

Joe

Enthusiast

Re: dynamic index collect stats statement

Hi,

Is this what you are looking for:

show table dbc.querystatstbl;----this has pi and si

select tdstats.udfconcat(trim(columnname)) from dbc.Indicesv where 

databasename='dbc' and tablename='querystatstbl'

Please let me know.

Senior Apprentice

Re: dynamic index collect stats statement

Hi Joe,

as Raja mentioned, there's an undocumented udfconcat function in the TD14 tdstats database, you just have to add a Group By as it's an aggregate UDF.

My StatsInfo view returns the Collect Stats statement:

New StatsInfo query for TD14

Enthusiast

Re: dynamic index collect stats statement

Thanks Guys!

I tried udfconcat but I believe it pulls back all the index columns (primary and secondary) on the same line.  Do you know of any way to only pull the UPI or USI columns?  I will look, but was wondering if there were an index # filter or some other filter to id as either the primary or secondary index(s).  For example, the table in the sql was created with a single column UPI and multi-column USI:

select tdstats.udfconcat(trim(columnname)) from dbc.Indicesv where

databasename='base_t' and tablename='account_group_tmp'

The output:

ACCOUNT_GROUP_KEY,ACQ_SITE_ABBR,ACQ_SRC_SYS_ABBR,SRC_KTOKK

     UPI = ACCOUNT_GROUP_KEY

     USI = ACQ_SITE_ABBR,ACQ_SRC_SYS_ABBR,SRC_KTOKK

This would work perfect if I could isolate and collect on the UPI and USI seperately within a dynamic sql statement whose result would look like:

     collect stats on BASE_T.ACCOUNT_GROUP_TMP column  ACCOUNT_GROUP_KEY;  UPI statement

     collect stats on BASE_T.ACCOUNT_GROUP_TMP column (ACQ_SITE_ABBR,ACQ_SRC_SYS_ABBR,SRC_KTOKK);  USI statement

Dieter - does your TD14 StatsInfo pull back the index columns or only the index columns that currently have stats collected?  I believe that if I create a new table, then run StatsInfo, index columns would not be returned because stats had not yet been collected.  Is that true?

What I'm really looking for is something that I can plug into dynamic sql that will create the stats statements for the index columns before the table has had any stats collected.

Thanks for taking the time to respond to my questions!

Joe

Enthusiast

Re: dynamic index collect stats statement

Found it!.... IndexType!  I was pretty close to getting this to work.  I tried running the following:

sel 'collect stats on ' || trim(databasename) || '.' || trim(tablename) || ' column ( ' || tdstats.udfconcat(trim(columnname)) || ');'

from dbc.Indicesv where databasename='base_t' and tablename='account_group_tmp'

and indextype='S';

It throws the following error:

     select non-aggregate values must be part of the associated group. 

Must be the udf call inside the dynamic sql statement...I appreciate your suggestions!

Senior Apprentice

Re: dynamic index collect stats statement

Hi Joe, 

you need to add a Group By, e.g.

sel 'collect stats on ' || databasename || '.' || tablename 
|| ' column ( ' || tdstats.udfconcat(columnname) || ');'
from dbc.Indicesv
group by DatabaseName, TableName, IndexNumber

Added IndexNumber to the group by to get multiple indexes.

where IndexNumber = 1 -> all PIs

where IndexType in ('K','P','Q','S','U','V') -> all indexes, PI + SI

where IndexType in ('K','S','U','V') and IndexNumber <> 1 -> all SIs

My StatsInfo view doesn't return missing stats on indexes anymore in TD14 (the older version did), because that's to much overhead and it's easy to write in TD14.

Enthusiast

Re: dynamic index collect stats statement

Thanks Dieter... works very nicely now!

Joe

Enthusiast

Re: dynamic index collect stats statement

Hi guys,

Sorry for posting on an old thread. I'm looking into usage of udfconcat. Is there any way to control the order of the values that this function concatenates?

-Suhail