All Forums Database
goldminer 118 posts Joined 05/09
10 Feb 2014
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.

goldminer 118 posts Joined 05/09
10 Feb 2014

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.

Raja_KT 1246 posts Joined 07/09
10 Feb 2014

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.



Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
10 Feb 2014

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


goldminer 118 posts Joined 05/09
11 Feb 2014

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:
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!

goldminer 118 posts Joined 05/09
11 Feb 2014

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!

dnoeth 4628 posts Joined 11/04
14 Feb 2014

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.


goldminer 118 posts Joined 05/09
15 Feb 2014

Thanks Dieter... works very nicely now!

suhailmemon84 47 posts Joined 09/10
18 May 2016

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?

You must sign in to leave a comment.